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