We examine the code required to send emails in bulk from Excel with data particular to each recipient, plus a general attachment. The workbook used in this exercise can be found here
The article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.
It is further assumed that Outlook is the email program.
Data
Copy the following text into a workbook:
| Title | Member | Subs | |
| Mr | Robert Jones | $90.00 | r.jones@acme.com |
| Miss | Mary Muffet | $75.00 | mary.m@gmail.com |
| Master | Jack Sprat | $50.00 | jacksprat@gmail.com |
Discovering after the event that an automated emailing has run berserk is embarrassing. So, test your code by initially using your own email address; enter it in column F. You need only two or 3 instances
Open the Code window, and select Tools>References> and tick Microsoft Outlook nn.n Object Library,
The Process.
The body takes up most of our code, and looks daunting without explanation. Our “storyboard” goes like this:
- Excel creates a new instance of Outlook.
- It asks for an attachment filename. If one is to be included, please save it for simplicity sake into the same directory from which the workbook opened (our workbook simply looks back along its own path for additional documents).
- Excel will cycle through the spreadsheet rows, using column B as a base……and will create a message body containing varying subscription amounts for members.
- The body will look like the image. Char(9) is used for tabbing, Char(13) for line feeds.
- Any file specified is attached
- The emails are sent.
Other Considerations
Barriers to sending multiple emails could manifest in Antivirus settings, Or your ISP could block excessive emails. If so, you might have to program a run in batches of, say, 50.
Outlook might block multiple emails itself if there is no antivirus software present. Check this via Outlook menus File -> Options -> Trust Center -> Trust Center Settings -> Programmatic Access.
If the way is clear, we can proceed. In the Code window, choose Insert -> Module
The Code(1)
Sub SendMsg(Optional AttachmentPath)
Dim strYear As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strAttach As String
Dim nCol As Integer
On Error GoTo ErrorMsgs
strYear = Format(Now(), "YYYY")
ActiveWorkbook.Sheets("Sheet1").Range("B3").Select 'Start point
If ActiveCell.Offset(0, 4) = "" Then nCol = 3 Else nCol = 4 'Establish email address column
Set objOutlook = CreateObject("Outlook.Application") ' Create the Outlook session.
strAttach = InputBox("Enter attachment name", "", ActiveWorkbook.Path & "\")
So far, we have booked our variable space, and positioned the cursor at the start point, B3.
We have also checked if the user has placed any email address in the test section, cell F4.
If Activecell.offset(0,4) = “etc”…
If so the program will send only emails to addresses found in column F until they run out. Otherwise the program will use the proper emails.
Lastly, we have asked for an attachment file name, which we’ll store in strAttach. If there is no attachment, we expect the user to press <Cancel>.
The remainder of the code follows:
The Code(2)
For i = 3 To 500 ‘Enter a suitable range. If Cells(i, 2) = "" Then Exit For ‘Escape the loop if complete Cells(i, 2).Select ‘i.e. B3 sTitle = ActiveCell sMember = ActiveCell.Offset(0, 1) nSubs = ActiveCell.Offset(0, 2) sRecip = ActiveCell.Offset(0, nCol) 'Email address '@@@@@@@@@ create body @@@@@@@@@@@@@@@@@@@@@@ Set objOutlookMsg = objOutlook.CreateItem(olMailItem) With objOutlookMsg Set objOutlookRecip = .Recipients.Add(sRecip) objOutlookRecip.Type = olTo 'Set the Subject & Body. .Subject = "Membership Fees " & strYear sBody = "The Flat Earth Society " & Chr(13) & Chr(13) sBody = sBody & "Membership Statement - " & sTitle & " " & sMember & Chr(13) sBody = sBody & "-------------------------------------------------------------" & Chr(13) & Chr(13) sBody = sBody & "Year" & Chr(9) & "Amount" & Chr(13) sBody = sBody & "------" & Space(4) & "------------" & Chr(13) sBody2 = "Please make your payment by EFT into our account at xxxx. If you have already paid, please advise accordingly " & Chr(13) & Chr(13) sBody2 = sBody2 & "We appreciate your membership and support in keeping the Earth flat." & Chr(13) & Chr(13) sBody2 = sBody2 & "Attached is a copy of the Outings Programme which is also available on our website at http://www.flatearth.org" & Chr(13) & Chr(13) sBody1 = sDesc & strYear & Chr(9) & nSubs & Chr(13) & Chr(13) sBody = sBody & sBody1 & sBody2 & Chr(13) & Chr(13) 'MsgBox sBody 'Uncomment this line to observe the layout of your message .Body = sBody '@@@@@@@@@ End body @@@@@@@@@@@@@@@@@@@@@@@ If strAttach > "" Then 'Attach a document Set objOutlookAttach = .Attachments.Add(strAttach) End If If Not objOutlookRecip.Resolve Then 'Resolve each Recipient's name. objOutlookMsg.Display End If .Send End With Next i MsgBox "Complete" Set objOutlookMsg = Nothing Set objOutlook = Nothing Set objOutlookRecip = Nothing Set objOutlookAttach = Nothing Exit Sub ErrorMsgs: If Err.Number = "287" Then MsgBox "You clicked No to the Outlook security warning. " & _ "Rerun the procedure and click Yes to access e-mail" & _ "addresses to send your message. For more information, " & _ "see the document at http://www.microsoft.com/office" & _ "/previous/outlook/downloads/security.asp. " Else MsgBox Err.Number, Err.Description Resume Next End If End Sub
Test Your Code.
Save the workbook as an xlsm, in order to establish an automatic path to possible attachments. Remember to save attachments into this same directory.
Using the Developer’s tab, insert a command button and assign it to the macro SendMsg.
Press the button to run.
This basic application is open to further customisation and improvements (and will run from database tables as well).
Disaster Recovery.
In instances where software crashes, it would be useful to have a tool that can recover Excel files where its own self-recovery routines fail. Such corruption of files is not as rare as one would expect, particularly when dealing with large and complex workbooks.
Author Introduction:
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar data and sql recovery software products. For more information visit www.datanumen.com
Leave a Reply