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!

68 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?

  8. David King

    Excel limits you from creating a hyperlink greater then 255, it doesn’t limit you from using a hyperlink greater then 255. I got around this by creating my hyperlink in Word and then copying and pasting the link into Excel. Excel used the link fine and I was able to get around the 255 limit.

  9. Mary

    Hi. Is there a way to auto populate the current date in the subject line?

    For example:
    Subject: “Example for 01-15-2016 attached”

    Thank you!

  10. Michael

    Mary, If you haven’t already gotten an answer, you can do something along these lines…

    =”Example for “&TEXT(TODAY(),”mm-dd-yyyy”)&” attached”

  11. Michael

    Mary, If you haven’t already gotten an answer, you can do something along these lines…

    =”Example for “&TEXT(TODAY(),”mm-dd-yyyy”)&” attached”

  12. Michael

    Mary, If you haven’t already gotten an answer, you can do something along these lines…

    =”Example for “&TEXT(TODAY(),”mm-dd-yyyy”)&” attached”

  13. Mariano

    HI

    Somebody knows if there is some way to attach a file?

    I’ve tried &attached=C:\test.pdf but doesn’t work…

    Thanks
    Best regards

  14. Ed

    I am currently stuck and wonder if someone can help me figure this out. The following information is all in one workbook and at most 4 sheets. The intent is to click on a hyperlink which builds the email using information already contained in the workbook.

    Click “E” Hyperlink made in excel (main sheet).
    Email 6 people (from sheet 2).
    CC 6 other people (from sheet 3).
    Use subject made from 3 different cells (from main sheet).
    Include a body from 3 cells of text (from sheet 2).
    Insert a signature (Don’t know if this is can happen)
    Mark email Important ( Don’t know if this can happen)
    Click send (Nothing else to do but send)

    =HYPERLINK(
    “MAILTO: “&Sheet2!B2&”; “&Sheet2!B3&”; “&Sheet2!B4&”; “&Sheet2!B5&”; “&Sheet2!B6&”; “&Sheet2!B7&”;
    ?CC= “&Sheet3!B2&”; “&Sheet3!B3&”; “&Sheet3!B4&”; “&Sheet3!B5&”; “&Sheet3!B6&”; “&Sheet3!B7&”;
    &SUBJECT= “&B3&” ” – ” “&C3&” ” – ” “&D3&”
    &BODY= “&Sheet2!C2&” %0A “&Sheet2!C3&” %0A “&Sheet2!C4&”
    , “E”)

    I used the hard returns above just in this pasted formula in order to keep my eyes from crossing.

  15. Praveen Kumar

    =HYPERLINK(“mailto:123@someaddress?cc=345@someaddress&bcc=678@someaddress&subject=xxxx&body=text book “, “Send Email”)

    and how to add data file to outlook

  16. ajay

    say if i configure above hyperlink and its working. but is it possible that cell will have some value say 200 on location c2, but on clicking such cell c2 is it possible to trigger such mail incluing cell value as 200.

  17. Salam Rashid

    the email body of mine have large text and the cell return to Error how i fixed this pls?

Leave a Reply

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