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 August 10, 2021)
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Before some features in Excel can function properly, you must have the correct permissions set for the user of the ...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
When you enter a URL or e-mail address in a worksheet, Excel usually converts it to a clickable hyperlink. This doesn't ...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.