Example of some Excel VBA code to send email.  The primary example works from a selection of email addresses in a column.  Additional examples show using a HYPERLINK Worksheet Function, and including an email link in HTML all examples including more than just the email address.

Email from Excel and from HTML

Location: http://www.mvps.org/dmcritchie/excel/email.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Intoductory Notes   (#intro)

Your hyperlink to send email can contain multiple addressee, a subject, and the text for an email.  In these examples the email will be started and you can modify the email before sending or cancel out of sending altogether.

Email from Excel VBA coding   (#vba)

Start an Email for recipients found in a selection of email addresses:

In this example we have chosen cells A35, A37, and A38 by using the Ctrl key, to send email to Aunt Harriet, Granny, and Dad.

Since you will be selecting from the cells it would be advisble that you not have them as hyperlinks.  You can prefix them with a single quote so they are text or you can eliminate they hyperlink later using the DelHyperlinks (delete hyperlinks) macro.

After making your selection, run the mailto_Selection macro.  If you are using Outlook Express, the names will be included as well based on the email addresses.  But the last example (A38:) shows that you can include

 A
35  AuntHarriet@example.com
36 Bubba@example.com
37  Dad (work)<Dad@example.com>
38  Granny@example.com
39  UncleCharlie@example.com
both.  The message will not be sent right away, it will be ready for you to add your message and then press the send button.

There is a lot of flexibility for instance you could put multiple names into a cell all you need to do is separate each email address with a semicolon (US list separator char) like you would do on the email, and you can add a space after the semicolon to make it easier to read.
  bubba@example.com; John@example.com

Sub mailto_Selection()
    Dim Email As String, Subj As String, cell As Range
    Dim response As Variant
    Dim msg As String, url As String
    Email = ""    'create list below
    Subj = "Family Newsletter"
    msg = "Dear Family,"
'--     Create the URL

     For Each cell In Selection
         Email = Email & cell.Text & "; "
     Next cell

    url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
      & Replace(msg, Chr(10), "/" & vbCrLf & "\")
    MsgBox url
    url = Left(url, 2025) 'was successful with 2025   , not with 2045
'--     Execute the URL (start the email client)
     ShellExecute 0&, vbNullString, url, vbNullString, vbNullString, vbNormalFocus
 End Sub
The above code can also be found at code/email.txt.

I prefer being able to review before sending, but if you have added all of the content within your macro and want to send automatically then replace the ShellExecute line (last line of code) with the following: 

    ActiveWorkbook.FollowHyperlink (url)
    Application.Wait (Now + TimeValue("0:00:03"))
    Application.SendKeys "%s"
You can include more than just an email address in a HYPERLINK Worksheet Function.

=HYPERLINK("mailto:someone@example.com?subject=Comment&body=comments concerning...","someone@example.com")

Another Plain Text Worksheet Example (#ws-example)

 A
 1  Greetings from the Cellar
 2  B&B "quoted text" 1-2-3
 second line
 third line $34.00 Room #1 at 80%.
 3  B&B%20"quoted%20text"%201-2-3%0D%0Asecond%20line%0D%0Athird%20line%20%20$34.00%20Room%20#1%20at%2080%.
 4  B%26B%20%22quoted%20text%22%201-2-3%0D%0Asecond%20line%0D%0Athird%20line%20%20$34.00%20Room%20#1%20at%2080%.
 5 
 6 George Brown
 7  GeorgeB@example.com
 8  send mail
 9 
10  A3: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"<","%3C"),">","%3E")," ","%20"),CHAR(10),"%0D%0A")
11  A4: =SUBSTITUTE(SUBSTITUTE($A$3,"""","%22"),"&","%26")
12  A8: =HYPERLINK("mailto:" & A6 & "<" & A7 & ">?subject=" & SUBSTITUTE($A$1," ","%20") & "&body=" & $A$4, "send mail")

Specification Limits (#specifications)

Watch out for the following “Excel specifications and limits” in worksheet functions, which you can check out in HELP for yourself.

Email in HTML   (#html)

*
You are not limited to just including an Email address in your HTML, you can actually include multiple recipients, include the subject, and start off the body of the email, so they know how to address you. 

Within a reference, you must substitute certain characters: 
%25  for a percent sign (%)   (should have been included in the example above).
%20  for a space
%22  for double-quote (suggest you avoid)
%3C  for less than, and %3E for greater than, i.e. <someone@example.com>
%26  or &amp;  for ampersand (&)
%0D%0A  for CRLF i.e. CHAR(13) followed by CHAR(10)

Example:  Send your comments concerning this web page   to: David send email comments

This is strictly an example, if you really want to send me email use the “send email comments” at the very bottom of the page.  The example.com domain is strictly for documentation purposes.

<!-- noindex -->
Send your comments concerning this web page to: David 
<I><A HREF="mailto:someone%20at%20Example.com%3CSomeone@example.com%3E?subject=Comments
%20on%20example.htm%20&amp;body=comments%20concerning%20your%20webpage%0D%0A%20at
%20http://www.example.com/example.htm%20%0D%0A%0D%0AHi%20David,%0D%0A">send email comments</a></i>    
<!-- end noindex -->
If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on August 01, 2005. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2008,  F. David McRitchie,  All Rights Reserved