Emailing from Excel Using the Hyperlink Function

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!

58 thoughts on “Emailing from Excel Using the Hyperlink Function

  1. Aaron

    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

    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. Beto

    Hello,

    What if i want to put a friendly name in a vlookup email with a email subject.

    My formula is
    =HYPERLINK(“mailto:”&VLOOKUP($A$1,Database!$B:$R,14,0))&”?subject=Permission Request”, “Friendly name” doest not work.

    Could you please kindly give assistance?

    Thank you.

  4. PRHartfiel

    I have a list of names in drop down to be selected; when the name is selected, I want it to send an email – thus the email needs to issue to whichever name is selected. I am not familiar enough with Excel to be able to figure out how to say if Ms. Smith is selected, send the email to Ms. Smith – help!

  5. Jeff

    I need help putting multiple emails in BCC and no email address in To: or CC:

    can anyone help? Thanks

  6. Rajesh

    =HYPERLINK(“mailto:”&H9&”,?CC=”&H10&”,&Subject=”&H11&”,&body=”&LEFT(H9,6)&”&body=”&H13&”%0A “&H14&” %0ABeginner”,”send email”)

  7. Sarah

    Hi there :)

    I need to take the “=hyperlink(“mailto:” from the cell and add it to an email generated through VBA. When i tried adding it making direct reference to the cell, the link name will appear but the hyperlink ability (i.e. mailto) is not.

    Any idea how to mkae this work?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>