Microsoft Office Tutorials and References
In Depth Information
Tip 33: Sending Personalized E-Mail from Excel
Sending Personalized E-Mail from Excel
This tip describes a method to compose and send short customized e-mail messages from Excel,
using the HYPERLINK function.
About the HYPERLINK function
The HYPERLINK function creates a link that, when clicked, activates your default browser and
navigates to a web page. The function takes two arguments: the URL and the text that’s displayed in the
cell. For example, this formula creates a link to my website:
A URL can also contain an e-mail address. When clicked, the hyperlink opens the new message
window of your default e-mail client, with the e-mail address in the “To” field. Here’s an example:
If you don’t have a local default e-mail client and rely on a web-based e-mail service
(such as Gmail or Hotmail), this technique won’t work.
You can also include a subject line. Here’s an example of the first argument for the HYPERLINK
function that includes an e-mail subject line:
In addition, you can include a short default message:
“mailto:firstname.lastname@example.org?subject=Help me!&body=I don’t understand this.”
Things get a bit more complicated if you want to include a line break in the message body. If that’s
the case, you need to “percent encode” the line break by using this code: %0A. Here’s an example
that inserts two line breaks into the e-mail body:
“mailto:email@example.com?subject=Help me!&body=I don’t understand
Technically, all non-alphanumeric characters should be encoded, including spaces. You
can use the ENCODEURL function (introduced in Excel 2013) to encode any text string.