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:
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.
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
Add 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