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

48 comments

  1. Janice D says:

    How do you disable a hyperlink? Say I type an email address into a cell. It automatically becomes a hyperlink. I know I can right click on it to get rid of that, but if I have a large list, is there an easy way to disable that functionality on an entire column?

  2. datapig says:

    Janice:

    Go to Excel Options
    Click Proofing
    Click AutoCorrect Options
    Click AutoFormat as you Type
    Uncheck ‘Internet and network paths with hyperlinks’

  3. RumRunner says:

    This is an amazing use of excel, that I wish I had thought of months ago. I guess I get to go back now and reprogram, but it will be worth it in the end. The only question I have is, is it possible to include an attachment in this hyperlink? I can create a hyperlink in a cell to a pdf, but how do you include this in the hyperlink? Any and all help would be greatly appreciated.

  4. Tracy says:

    *very* interesting. How does this work for email programs like Lotus? Does it work on Macs?

  5. datapig says:

    RumRunner: As far as I know, there is no way official way to attach files using “mailto” protocol. I tried using the ‘attachment’ parameter, but Excel never does anything with it.

    Tracy: Yes…this Hyperlink trick will work with any mail client you have.

  6. Kevin says:

    Very nice. Thanks for this. It will work with Lotus Notes (6.5)

  7. Robert says:

    Thanks for the update. It would be nice if it would do this, officially or otherwise, as I could then send documents without the extra steps.

  8. Kevin says:

    Can it do the following:

    Open E:mail
    address with subject
    add text to body and
    then self attach itself(1) to the said e:mail and
    send…

    (1) as either .xlsx or .pdf file.

    I know that you send a print job to a .pdf file

  9. Janice D says:

    Thanks Mike! That’s been bugging me for a long time and I could never find the solution.

  10. Girish says:

    Thanks! is this works in Excel2003 or earlier versions?

  11. Reido says:

    WordPress is converting plain double-quote characters to curvy ones so if you copy the formula straight into Excel it will throw an error on the first parameter. A non-developer friend of mine was wondering why it wasn’t working for him as he copied the text straight off the page, so I thought I’d point that out to anyone else having issues! I’ve fixed below, but it probably will convert the straight quotes back again! ;)

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

  12. datapig says:

    Thanks Reido!

  13. Kathleen says:

    Hi, I’m having a problem with my Excel email hyperlink and hope you can help.
    I have numerous email hyperlinks set up in excel but no matter what name/email address I click on lets say, John Doe, Mary Smith, Susan Smart it comes up the same John Doe for every one and I don’t get it because in Excel the top line will say Mary Smith or Susan Smart but the email link comes up John Doe@yahoo.com.
    What can I do to stop this.
    Thanks.

  14. Justin says:

    This works very well, thank you! Does anyone know how to include an Outlook signature in the emails created with this method? Clicking the link is bringing up an email without my default signature.
    Thanks again!

  15. Brian T. says:

    In your “dynamically build your ‘mailto’ URL address in with formulas” how do you add multiple names to send “To”… say names are in multiple cells… how do you add the multiple cell names to the formula.
    thanks…

  16. Ryan says:

    Is it possible to do this with a longer email body? After about 250 words you get a #VALUE! error.

  17. Steve Frampton says:

    I am filling a cell with an e-mail address using ‘vlookup’ but Excel does recognise this as a ‘mailto’ link, if i use ‘hyperlink’ in front of the ‘vlookup’ it will link to drive/network destinations or to the web but will drive an e-mail, any ideas on this one?

    thanks and regards,

    Steve.

  18. Michael says:

    There is a character limit to how long the body of the email can be…is there any way to reset this limit, or rather, increase it?

    Any help is appreciated,

    Michael

  19. Phil says:

    There is a limit on the number of argument chars the hyperlink function will take. If the text in the referenced cells expands the argument over 200 odd chars you get a value error. I can’t find a way round it ! Very frustrating but if this helps others from beating their heads against a brick wall !!

  20. Hi all.

    Is it possible to use a value from a cell as body? without using macro/vba

    Thanks in advance

    Kåre

  21. brent_dennise@comcast.net says:

    This is great stuff. How can I use this to attach the current workbook to the email that is created by clicking on the hyperlink. Is there a way to do it using the mailto command instead of vba

  22. precious says:

    is it possible to use excel hyperlink to send email to the customer to remind them of their payment?

  23. Great weblog right here! Also your website quite
    a bit up very fast! What host are you using? Can I get your affiliate link
    to your host? I wish my site loaded up as fast as yours lol

  24. Clodagh Rochford says:

    I have hyperlinks set up on a spreadsheet, I added a new one to my list now all the other don’t work, when i right click and go to Edit, there is no e-mail address in the box, but recently used ones are listed below, can this be fixed or will I have to add then all again ?
    Its a list of 150 codes, which should link to a different e-mail address as you click on them.

    Thanks

    Clodagh

  25. Hello, I think your site might be having browser
    compatibility issues. When I look at your blog site in Safari, it
    looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that,
    great blog!

  26. Prem Kumar BG says:

    Can anyone please let us know, whether formula generated hyperlink will be able to open?
    I try using the below formula

    =HYPERLINK(“C:\Data.txt”, “Test”)
    This is working fine, but

    =HYPERLINK(VLOOKUP(G7,sheet!A:E,2,0), “Test”)
    Where i have used the vlookup function to extract the Hyperlink link based on condition, but its popping the message “Cannot open the specified path”.

    would request to provide with the resolution.

  27. datapig says:

    Prem: I tried a similar VLOOKUP as a test and it worked for me.

    In any case try wrapping the resultant string from the vlookup in quotes:

    =HYPERLINK(“”&VLOOKUP(G7,sheet!A:E,2,0)&”", “Test”)

  28. roshan naidu says:

    how to send out emails with the subject line and the body as well as the mail(script) from excel…?
    for example: subject line :bring it on

    hi james,
    hope u doin gud n blah blah..

    wats the formulae..?

  29. Jennifer says:

    Thanks!! This has been very helpful.

    One request: Can you also add a link to the active file (without using VBA)?

    I have tried many different combinations of “file://Q:RFQ0050.xls” with no luck.

  30. Neranjan says:

    How I insert an attachment file also ??

  31. Juan Pablo says:

    Thank u very much. I am from México City and it works perfect for a special requirement than I’m doing.

    It works awesome!!!

  32. Kevin says:

    Thanks a million – worked like a charm!

  33. Amit says:

    Superb !!! Thanks a lot for sharing.

  34. Eric says:

    Hello,
    I would like to send an email with a link to a document i wrote the following code

    Private Sub CommandButton1_Click()
    ActiveWorkbook.FollowHyperlink “mailto:name@xxxxx.com?cc=name1@xxxxx.com&subject=Programmer une Planning Session&body=Préparer la Planning Session.%0Ahttps://worksites.connect.inxxxx.com/sites/XXXXXX_BS_Projects/templates/forms/by%20phase.aspx”
    End Sub

    When I launch the code the hypertext link is not totally validated, only a part of it is underligned and coloured in blue –> when you click you get “The webpage cannot be found”
    Can someone help me?

  35. You should be aware that this technique is really only string manipulation: you use whatever means (referencing cells, looking up values, etc) to create a single line of characters, which is essentially a HTML element: the ‘href’ value in a tag, to be exact.

    What happens is this:
    - you create your ‘mailto’ string, with parts like ‘subject’, ‘cc’ and ‘body’. This string has a maximum of 256 characters total.
    - Excel turns this into a hyperlink: a value in an Excel cell that can be clicked and looks (behaves) like (plain) links in your browser when you visit a web site.
    - When you click such a hyperlink, Excel treats it like any other hyperlink: it hands it over to what is called a ‘handler’ for the ‘mailto’ protocol.
    - This handler will start your default mail program and instruct it to open a blank new message and fill in the details such as subject, CC, etc.

    NB:
    1) e-mail programs create attachments by ‘translating’ them into a series of ‘normal’ characters (letters and numbers) and putting some extra information in the header of the e-mail. You never actually see this because modern e-mail programs hide this from you. This translating is not built into Excel. Also, even the smallest attachments would result in a string that is way longer than 256 characters.

    2) ‘mailto’ is not really a protocol or a program: it is a fairly ancient method introduced into HTML to be able to indicate e-mail adresses. Normally, a browser would handle this, but this functionality is now built into Windows itself.

    Hope this clarifies why some applications of this technique work and others don’t.

  36. Addendum: the maximum length seems to have increased since 1997: see this Stack Overflow question.

  37. Ellen says:

    How can I insert the automatic signature line?

  38. Andrew says:

    Hi! Firstly, thanks for the great tips – extremley helpful!

    I have a predefined email signature in Outlook, which I would like to keep.. when I use this formula however, it wipes the email signature! Do you (or anyone) know how to keep the email signature when hyperlinking directly from Excel?

    Thanks!

  39. Joe says:

    Dear DataPig:
    “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”
    Everything worked beautifully except for the body entry. When I use =body in the hyperlink =HYPERLINK(“MAILTO:”&G6&”?CC=”&G8&”;”&G9&”;”&G10&”;”&G11&”&subject=”&G12&”&body=”&G13, “Employee Resignation Email”), it returns a “#Value!” error. When body is removed the link is what I named it and the email gets created – but sans body of course. Using Outlook 2007. Any ideas of how I can get your great tip working?

  40. John says:

    Hello DataPig!!

    I did what you have mentioned above to get the message done with the hyperlink

    However I noticed that I can only add three lines and not more than that

    Could you please confirm the same or am I doing it wrong?

  41. Nicklas says:

    Is it possible to set in a link to a shared network in the body text? When I try I get the value error

  42. Juris says:

    Hey! :)
    I’m new in the whole excel world.. And i’d like a little help with a MailTo formula. In the formula i need a recipient (A1) and a subject (B1).

    looks simple, but i can’t get it rigt. the subject always remains blanc.. :(

  43. Juris says:

    Hello again. I finally got the formula i needed. If someone needs it here it is! :)

    =HYPERLINK(“mailto:” & A1 & “?subject=”& $C$1 & “”)

  44. abhishek sathe says:

    Gr8 blog. It solved my problem of sending mail to cc & bcc through mailto function right away.
    :-)

Leave a Reply