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.
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.
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
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.
A 35 AuntHarriet@example.com 36 Bubba@example.com 37 Dad (work)<Dad@example.com> 38 Granny@example.com 39 UncleCharlie@example.com 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.comSub 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 SubThe 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")
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")
Watch out for the following “Excel specifications and limits” in worksheet functions, which you can check out in HELP for yourself.
- Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
- Length of formula contents (as used for HYPERLINK Worksheet Function) 1,024 characters
- Nested levels of functions: 7
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>
%26or &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&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
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2008, F. David McRitchie, All Rights Reserved