Emailing from Excel Using the Hyperlink Function

December 9th, 2010 by datapig Leave a reply »

Did you know that you can use the HYPERLINK function to create emails from Excel?

It's true. Today, I'll show you that the HYPERLINK function is not just for getting to your E-Harmony account.

.

Excel's HYPERLINK function has only two arguments: the URL you are trying to hit, and the friendly name. So to get to the E-Harmony site, you could use something like:

=HYPERLINK("http://www.eharmony.com", "Woman Shopping")


This will create a hot link in your spreadsheet that, when clicked, will take you to the prescribed URL.

.

Well, the neat thing is that the HYPERLINK function also works with 'mailto:' URL constructs. This means you can create a hot link that sends a basic email when clicked.

=HYPERLINK("mailto:mike@someaddress.com?subject=Email To DataPig", "Send Email")

.

If you want to send the email to multiple recipients, you can include other addresses separating each with a comma. Like this:

=HYPERLINK("mailto:mike@someaddress.com, MrsPig@someaddress.com?subject=Email To DataPig", "Send Email")

.

What's that? You want to use CC and BCC lines? I got you covered. Simply add the cc and bcc parameters:

=HYPERLINK("mailto:mike@someaddress.com, MrsPig@someaddress.com?cc=Thing1@someaddress&bcc=Thing2@someaddress&subject=Email To DataPig", "Send Email")

Notice that you will need to use the ampersand (&) to separate the parameters

.

OK, let's get fancier and add a body.

=HYPERLINK("mailto:mike@someaddress.com, MrsPig@someaddress.com?cc=Thing1@someaddress&bcc=Thing2@someaddress&subject=Email To DataPig&body=I ate too much salmon.", "Send Email")

.

If you need to add a multi-lined body (meaning the text of your body is on multiple lines), you can use the carriage return encoding (%0A).

=HYPERLINK("mailto:mike@someaddress.com, MrsPig@someaddress.com?cc=Thing1@someaddress&bcc=Thing2@someaddress&subject=Email To DataPig&body=I ate too much salmon.%0ANow I have salmonella ", "Send Email")

.

With the finished HYPERLINK, this email is generated. As you can see, with one click, the user gets an email with the multiple TO recipients, CC and BCC recipients, a Subject and a multi-lined Body. Pretty snazzy!

.

.

And as always, you can dynamically build your 'mailto' URL address in with formulas. It's simply a matter of concatenating the appropriate cells to create the correct syntax for the URL.

.

.

There you have it! Happy emailing!

Advertisement

55 comments

  1. Aaron says:

    I have a predefined email signature in Outlook when I use this formula however, it wipes the email signature. Do you (or anyone) know how to keep the email signature when hyper linking directly from Excel? also can i have the font change to my default of 11 Calibri instead of 12

  2. Nick Anderson says:

    I have a document that I send out weekly. I would like to use the formula above to send out an email that populates the information as shown in the last example. However, the body of the email is a to long to make it work. Any suggestions?

  3. Joe Bowers says:

    Is there a way to attach the active excel worksheet onto the email that is generated when clicking the hyperlink?

Leave a Reply