Excel Macro Email

Among the many little-known and perhaps even-less-used hacks one could exploit via Microsoft Office, an Excel macro email can save precious hours on a weekly basis. As a bonus, understanding the nature of these handy little “cheats” provides an incidental beginner’s introduction to some very basic principles of coding.

This handy VBA code will send a personalized email directly from your Excel spreadsheet via Outlook Express – no painstaking composition required.

If you are new to VBA code and programming with Excel, check out this post to brush up on some basics before playing with this script.

Consider the following spreadsheet…

Employing the VBA code listed here will send an instantly generated Excel macro email to each of the four listed recipients detailing their respective bonuses as though the report had been hand-prepared:

Private Declare Function ShellExecute Lib “shell32.dll” _

Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, _

ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long

Sub SendEMail()

Dim Email As String, Subj As String

Dim Msg As String, URL As String

Dim r As Integer, x As Double

For r = 2 To 5 ‘data in rows 2-5

‘ Get the email address

Email = Cells(r, 2)

‘ Message subject

Subj = “TEST: Your Annual Bonus”

‘ Compose the message

Msg = “”

Msg = Msg & “Dear ” & Cells(r, 1) & “,” & vbCrLf & vbCrLf

Msg = Msg & “I am pleased to inform you that your annual bonus is ”

Msg = Msg & Cells(r, 3).Text & “.” & vbCrLf & vbCrLf

Msg = Msg & “Jonathan Rosewood” & vbCrLf ‘senders name

Msg = Msg & “President” ‘senders title

‘ Replace spaces with %20 (hex)

Subj = Application.WorksheetFunction.Substitute(Subj, ” “, “%20″)

Msg = Application.WorksheetFunction.Substitute(Msg, ” “, “%20”)

‘ Replace carriage returns with %0D%0A (hex)

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, “%0D%0A”)

‘ Create the URL

URL = “mailto:” & Email & “?subject=” & Subj & “&body=” & Msg

‘ Execute the URL (start the email client)

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

‘ Wait two seconds before sending keystrokes

Application.Wait (Now + TimeValue(“0:00:02”))

Application.SendKeys “%s”

Next r

End Sub

Impressive, no?

Outlook Express essentially takes dictation via your worksheet by picking out email addresses as it loops through rows and places the appropriate entries from the “Name” and “Bonus” column cells as the code requests. The code even automates the SendKeys demand to instantly deliver each message, which is typically limited to 255 characters.

However, one could even skirt the 255-character limit by copying the text to the Clipboard and adding this statement before the Application.SendKeys statement:

Application.SendKeys “{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v”

Try It!

Download the spreadsheet and paste the script into your VBA editor. You can tailor the code to fit your individual needs. Just keep in mind that if you add columns or change the order of how information appears in your spreadsheet, you will need to make adjustments to your code. Happy coding!