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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Is your worksheet information destined for a Web page? Here's how you can specify the fonts that should be used when Excel ...Discover More
Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this tip ...Discover More
Hyperlinks can be a great timesaver and very convenient. Unless, of course, if they don't work as you expect. This tip ...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.