Wednesday, May 27, 2015

How to send emails with custom subject lines in Excel

If you type an email address into Excel and press enter, it automatically becomes a hyperlink. If you click the link, it will create a new email in your default email client with the To: line already filled in. The subject line, however, will be empty.

excel email hyperlink


So how do you automatically fill in the email subject line from Excel? One way to populate the subject line is by using the HYPERLINK formula. Follow this example:

In column A, place all of your email contacts. In column B, place all of your subject lines. Your email subject lines can all be the same or they can each be unique. In column C, we’ll place the following HYPERLINK formula:

 =HYPERLINK(CONCATENATE("mailto:",A1,"?subject="B1)) 


Now, when you click the hyperlink created by this formula it will create a new email addressed to the recipient you listed in column A, and will feature the subject line you listed in column B. If you want to add email hyperlinks with constant subjects this is the way to go.



To CC someone, add this to your formula: “&cc=”, A2 where A2 is the cell location of the recipient’s email address.

To take it a step further, we can also add text in the body of the email by expanding our Excel formula. In column C, place the text you want to appear in email’s body and add the hyperlink formula to column D:

=hyperlink(concatenate("mailto":,a2,"?subject=",B2,"&body=",C2)) 





We can take it even further by including the email recipient’s name and adding our signature. Make it look nice by adding some formatting. To add a line break to your email’s body text, use: %0D%0A. You can add this to the formula or place it in a reference cell. So, to compose a complete email in Excel, including recipient, subject line, and body text with line breaks, use the following formula:



This really is the ultimate formula for sending emails from Excel.


To add an attachment it seems like you should simply be able to add “&attachments=”,C2 but in Outlook2013 I get the error message “Cannot start Microsoft Outlook. The command line argument is not valid. Verify the switch you are using.” Anyone know how to fix this issue? In the meantime, the best bet to add an attachment may be to use a VBA macro, as demonstrated here.

 It is possible to manually manage an email list from Excel. But with all the automated tools out there today, why would you? If you have a good reason to I’d love to hear about it. Maybe it’s because someone does not want to pay for an email subscription/list building service? An email list building and sending tool like Aweber is only around $30/month (and what I use to send out my free email tips). In conclusion, you can manually manage email lists with Excel, but automated services like Aweber make it so much easier.