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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Got a single worksheet that you want to e-mail to someone, but don't want them to see the rest of the worksheets in the ...Discover More
Need a quick link within a document to some external data? You can paste information so that Excel treats it just like a ...Discover More
Excel allows you to easily add hyperlinks to a worksheet. Click on it, and the target of the link is opened in a browser ...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.