How to Create a Bulk Mailer with Excel VBA

By following this article you can build your own bulk emailing application using Excel and macros. This will help you to save your money which is currently being spent for email marketing services. You do not need expensive web-based solutions. This app can be easily handled on your desktop or laptop. You can keep your email lists private and this is the most important advantage of this app.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s prepare the GUI

As shown in the image, create a sheet and name it as “BulkEmail”. Fill rows 1 and 2 with Black color and we will use this as ribbon for our app. Create 4 buttons using shapes. This app requires 8 fields and they are To, Cc, Bcc, Subject, Message, Attachment Path, and Status.Create BulkEmail Sheet

Let’s make it functional

Copy the script into a new module and attach Subs to buttons as mentioned here

Sub Button
BulkEmail SEND
Use_Same_Subject USE SAME SUBJECT
Use_Same_Message USE SAME MESSAGE
Use_Same_Attachment USE SAME ATTACHMENTS

Let’s test it

Excel VBA CodeAdd Email IDs, Subject, Messages and attachments on the sheet and click on the Send button. The last field i.e., “Status” column should show “Sent” for each row with email IDs in the sheet.

How it works?

The macro will scan and open Outlook application if it is not already open. It will then identify the last used row in the sheet and sends email for each row with attachments.

Modify it

The script now sends unformatted text email. You can modify the macro to send HTML newsletters.

Script

Sub BulkEmail()
'ALL DECLARATIONS GOES HERE
    Dim obj1 As Object
    Dim obj2 As Object
    Dim vlr As Long
    Dim vws As Worksheet
    Dim v_subject As String
    Dim v_message As String
'SET THE SOURCE WORKSHEET IN THIS LINE
    Set vws = ThisWorkbook.Sheets("BulkEmail")
    vlr = vws.Cells(vws.Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
'CHECKING IF OUTLOOK IS OPEN IF NOT, OPEN THE OUTLOOK
    Set obj1 = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set obj1 = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    On Error Resume Next
'START SENDING EMAIL FOR EACH ROW IN THE EXCEL SHEET. AFTER SENDING THE EMAIL UPDATE THE STATUS AS SENT
    For i = 4 To vlr
        Set obj2 = obj1.CreateItem(0)
        With obj2
            .To = Sheets("BulkEmail").Range("B" & i).Value
            .cc = Sheets("BulkEmail").Range("C" & i).Value
            .bcc = Sheets("BulkEmail").Range("D" & i).Value
            .Importance = 0
            .Subject = Sheets("BulkEmail").Range("E" & i).Value
            .Body = Sheets("BulkEmail").Range("F" & i).Value
            .ReadReceiptRequested = False
'ATTACHING EACH FILE IN THE ATTACHMENT
            Dim files As Variant, file As Variant
            files = Split(Sheets("BulkEmail").Range("G" & i).Value, ";")
            For Each file In files
                .attachments.Add file
            Next
            .send
            Sheets("BulkEmail").Range("H" & i).Value = "Sent"
        End With
'SENDING MULTIPLE EMAILS WITHOUT ANY DEALY MIGHT CRASH THE OUTLOOK APPLICATION
'SO ADD A PAUSE BETWEEN EACH EMAIL
        Application.Wait (Now + TimeValue("0:00:02"))
    Next i
    Set obj1 = Nothing
    Set obj2 = Nothing
End Sub
    
Sub Use_Same_Subject()
    Dim lr As Long
    lr = Sheets("BulkEmail").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("BulkEmail").Range("E5:E" & lr).Value = Sheets("BulkEmail").Range("E4").Value
End Sub

Sub Use_Same_Message()
    Dim lr As Long
    lr = Sheets("BulkEmail").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("BulkEmail").Range("F5:F" & lr).Value = Sheets("BulkEmail").Range("F4").Value
End Sub

Sub Use_Same_Attachment()
    Dim lr As Long
    lr = Sheets("BulkEmail").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("BulkEmail").Range("G5:G" & lr).Value = Sheets("BulkEmail").Range("G4").Value
End Sub

Handle Excel file Corruption:

Sometimes you may encounter corrupted or damaged Excel files when using the bulk mailer. No worries. Just use a third-party Excel file recovery software to easily fix the problem smoothly.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx corruption and outlook recovery software products. For more information visit www.datanumen.com

Comments are closed.