Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatic Text in an E-mail.
by Allen Wyatt
(last updated May 20, 2017)
Laura has an e-mail address that is a hyperlink in Excel. She edited the hyperlink so that the e-mail created when the link is clicked has a subject line. She wants to be able to have the e-mail automatically contain text, as well.
There is a way to do this rather easily in Excel. To see the way to do it, put the address of the e-mail recipient in A1, the desired message subject in A2, and the desired message body text in A3. Then, in a different cell, use this formula:
=HYPERLINK("mailto:" & A1 & "?subject=" & A2 & "&body=" & A3, "Linking text")
This results in a hyperlink that users can click and have both the subject and body filled in. The downside to this is that your message body can't be terribly long. The total length permissible for any hyperlink is 255 characters, including the words such as "mailto," "subject," and "body." Thus, if the length of the address in cell A1 is 16 characters, the length of the subject in cell A2 is 25 characters, and the "overhead" ("mailto," "subject," etc.) take 22, then that means your message in cell A3 cannot be over 192 characters. (That's a relatively short message.)
You can also create the appropriate hyperlink using the Insert Hyperlink dialog box. Follow these steps:
Figure 1. The Insert Hyperlink dialog box.
You should note that this approach will work for most mail clients. If it doesn't work for yours, then try replacing the spaces in the subject and the body with the characters %20.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11785) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Automatic Text in an E-mail.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Hyperlinks can be helpful in some worksheets but bothersome in others. Here's how to get rid of any hyperlinks you don't ...Discover More
Wouldn't it be great if Excel could automatically e-mail you when a due date is reached? It can, if you are using Outlook ...Discover More
Inserting a hyperlink into a workbook that is shared with others is not possible in Excel. Here's what you can do about it.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.