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
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”))
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:
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!