How to Batch Send Multiple Emails via Excel VBA

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	Email
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