Massive sending of Emails with Excel
It may happen that you have to send your customers or suppliers some e-mails with customized text depending on the recipient, or with a different attachment for each recipient.
Think about sending declarations of intent, or declarations of origin, or sending personalized invitations for participation in an event, or certificates of participation, greeting cards, and much more!
If you don’t have CRM software that allows you to automate all of this, don’t worry! you can do it with Excel!
In quesIn this article I’ll explain how to do it.
The shopping list
You just need three things:
- Microsoft Office (Excel or Access) with VBA enable.
- blat, a very powerful command line program capable of sending emails with the STMP protocol
- a mail server for maximum sending, such as free SMTP2GO. Alternatively, if you don’t have a lot of emails, you can also use your corporate mail server.
The basic idea
Microsoft Office is a truly wonderful package that a company cannot miss. The strength of this suite of tools (in particular Excel and Access) is the ability to write VBA (Visual Basic for Application) code and create incredible automations with it not only for data processing within them, but also for processing. integration with the Operating System and other external tools.
Personally, I have done everything with it: from systems for factory data acquisition, to interfacing with PLCs, to controls for electronic scales, to systems for controlling industrial furnaces, marking systems, presses and much more! VBA lends itself to many uses, so why not also use it to send e-mails?
Starting from now I will only talk about Excel because it is more known and easier to use, but it can also be extended to Access.
The idea is to then use a VBA macro in Excel to send the e-mails.
However, Excel alone cannot send e-mails but it can call an external command that can do it for it. this command will be blat, a program capable of sending commands for sending electronic mail to a mail server.
But let’s proceed step by step.
First, however, I want to remind you that sending emails without the prior consent of the interested parties can lead to various “privacy” disputes, making us the possible target of two different types of actions: one of an administrative nature (to be proposed before the Privacy Guarantor), and the other of a judicial nature, in order to obtain any compensation for damages. I therefore recommend that you use what I am about to explain only to send to your customers / suppliers and not to acquire new customers.
Creating a free account on SMTP2GO
This step is optional but highly recommended by me. Sending many “massive” emails using your company mail server in fact exposes you to the risk of losing the good reputation of your server, and ending it up in some “black list” by marking it as the server that sends SPAM, with the consequent blocking of the possibility of delivering normal messages. Mail servers do not like receiving SPAM, and are “trained” to try to recognize abuse, and to defend themselves.
For this reason we will use a specially created server, behind which there is someone who takes care to keep the reputation “clean”. A second reason for doing this is that doing so will lighten the workload on our server.
Visit www.smtp2go.com and sign up for a FREE plan. The service is free with a limit of 1000 emails per month.
If you need more monthly mailings here www.smtp2go.com/pricing/ you can find the various plans and their prices: from 10k, 40k, 100k, 250k, 500k, 1Milion and over 3 Million emails per month.
Creating a “sender” user
First we tell SMTP2GO what will be the e-mail address that will appear as the sender of the e-mails.
To do this we need to create an SMTP user.
After registering, go to Settings –> SMTP Users
Now write down the data you see under “Connecting via SMTP”, we will need them later:
Connecting via SMTP SMTP Server: mail.smtp2go.com SMTP Port: 2525 Alternative ports: 8025, 587, 80 or 25. TLS is available on the same ports. SSL is available on ports 465, 8465 and 443.
Create the first user for sending, click on the button Add SMTP User
and fill in Username with the email address of your company with which you want the mitten to appear
Choose an arbitrary password; it doesn’t have to be the real one of your sender mailbox. write it down.
Click on SAVE
Authorize SMTP2GO to send on behalf of your domain
ATTENTION: If you prefer to use your company server instead of SMTP2GO, this step is not necessary. Otherwise if you use SMTP2GO it is very likely that the emails will not be accepted by the recipient servers.
To make recipients’ mail servers willingly accept our emails, we need to authorize SMTP2GO to send emails on behalf of our domain. This is done in four steps:
1. Add the domain we want to ship to
Go to Settings–>Sender Domains
type in the domain of your company and click on“Add Domain“
2. Publish some “CNAME” type records in our DNS
Now click on the domain you just entered, a window will appear with the list of CNAME records
3. Enter the CNAME records shown
Now go to the DNS management of your domain and enter all the CNAME records indicated in the previous screen, with the relative values. Remove the configuration and wait at least 10 minutes. Note: It may take several hours for DNS to propagate globally.
4. Check the DNS records
Now go back to the SMTP2GO “Configure your DNS” page and click on the “Verify” button. SMTP2GO will keep reading those records in the DNS effectively validating the configuration.
When the DNS has propagated and if the records have been entered correctly, green ticks appear indicating that the configuration is OK! SMTP2GO configuration is finished.
Installation and configuration of “blat”
Download blat Full Version from this link
Inside the downloaded compressed archive you will find a first folder and then a second one called “full” inside. Unzip the “blat.exe” and “blat.dll” files in the C: \ Windows \ System32 folder. By doing so, we guarantee blat the possibility of being able to be called from any local path.
Here is the hardest part. you must instruct BLAT to send the emails by telling him:
- with which email we send
- what is the server for sending
- with which codes (username and password) to connect to the server for sending
Open a Command Prompt like this: press the Win + R keys and in the window that appears type “cmd”. then press ENTER
The Windows Command Prompt window opens. first, type: blat and press ENTER.
and something like this appears below it means blat is callable correctly.
Configure blat with the following command:
blat -install -f <sender-email> -server <email-server> -u <login> -pw <password>
which will actually become:
blat -install -f MYEMAIL@MYDOMAIN.COM -server mail.smtp2go.com -u USERNAME-pw PASSWORD
sent a test email to verify that everything is ok:
blat -s "My Subject" -body "my body" -to firstname.lastname@example.org -html
obviously replace email@example.com with your email!
the last “-html” parameter forces the content to HTML otherwise it will come in plain text only.
if everything went as it should, you will receive an email like this
Send emails with Excel
I have prepared a ready-made Excel sheet for you but that you can customize at will and that manages both needs:
- being able to customize the text of the email based on the recipient (inserting the values in different columns)
- to be able to send a different attachment for each recipient (by specifying the attachment name in an Excel column)
Download the Zip archive I have prepared for you from this link: https://github.com/alessandroscola/mass-sending-of-e-mails-with-excel
Unpack it in your PC’s “Documents” folder.
You will find inside:
- the Excel file with the macro
- 3 sample PDF attachments: 001.pdf, 002.pdf, 003.pdf
Open the Excel sheet, it looks like this:
Compila l’OGGETTO della mail e il TESTO. Per il testo ti ricordo che per inserisce gli “a capo” devi premere ALT+INVIO.
Fill in the SUBJECT of the email and the BODY. For the text I remind you that to insert new lines you have to press ALT + ENTER.
Then from line 6 fill in the following fields.
In column A write the COMPANY NAME of the recipient. In column B write the MAIL.
In columns C and D write the name of the file separating NAME and EXTENSION. This allows you to focus only on the name. For example, the name of the attachment could coincide with the CUSTOMER CODE / SUPPLIER CODE (perhaps extrapolated from your management system) while the extension can always be the same (for example “.pdf” or “.docx” or other …).
It will then be your responsibility to save in the same folder as the excel file, all the attachments to be sent, with the correct names (eg 001.pdf 002.pdf 003.pdf) following the customer / supplier code.
In column E I have entered a hypothetical “discount code” that will be inserted in the TEXT of the email in place of the% E% tag.
To send everything, click on the “Send” button
The macro is executed and for each row starting from row 6, the text will be customized with the values of columns A and E.
Then the email will start with “Dear “<YOUR REAL COMPANY NAME indicated in column A>”
Similarly, the discount code will be inserted instead of% E%.
Furthermore, for each indicated attachment, its real existence will be verified in the folder where the Excel file is present. If the file exists then it will be attached, otherwise the email will be sent without the attachment.
In column F “ATTACHMENT CHECK” will be written “Ok” if the attachment has been found, or the message: “ATTENTION: ATTACHMENT NOT FOUND!” if not.
Customize the VBA code
The macro is simple to understand. You can customize it at will.
Option Explicit Public conn, rs As Object ' Code by Alessandro Scola www.alessandroscola.com ' ' Rimbember for INSTALL BLAT: ' blat -install -f <sender-email> -server <email-server> -u <login> -pw <password> ' ' To SEND with BLAT: ' blat -Subject "email Subject" -body "email body" -to firstname.lastname@example.org -html ' ' alternatively with the email body in a external file "email_body.txt", the command is: ' blat -s "c:\path\to\email_body.txt" -Subject "email Subject" -to email@example.com -attach "c:\path\to\attachment.pdf" -html Sub send_emails() Dim row As Long Dim RetVal As Variant Dim email As String Dim command As String Dim obj_fso As Object Dim fileName As String Dim subject As String Dim body As String RetVal = MsgBox("Are you sure you want to send all emails ?", vbQuestion + vbYesNo + vbDefaultButton2) If (RetVal <> vbYes) Then Exit Sub End If subject = Trim(Cells(2, 2).Value) subject = Replace(subject, Chr(34), "\" & Chr(34)) ' replaces any " character with a \" not to break the command string row = 6 email = Trim(Range("B" & row).Value) While (email <> "") body = Trim(Cells(4, 2).Value) ' the initial body text body = Replace(body, "%A%", Trim(Cells(row, 1))) ' replaces any %A% with the content of A column body = Replace(body, "%E%", Trim(Cells(row, 5))) ' replaces any %E%with the content of E column body = Replace(body, vbLf, "<br>") ' replaces any "new line" with the "<BR>" TAG. "<BR>" TAG work as "new line " in HTML e-mails body = Replace(body, Chr(34), "\" & Chr(34)) ' replaces any " character with \" to not to break the command string command = "blat.exe -Subject " & Chr(34) & subject & Chr(34) & " -body " & Chr(34) & body & Chr(34) & " -to " & email & " -html" fileName = ThisWorkbook.Path & "\" & Trim(Range("C" & row).Value) & Trim(Range("D" & row).Value) If (fileExists(fileName)) Then 'Se il file allegato esiste aggiunge al command "BLAT" la parte di codice per allegarlo command = command & " -attach " & Chr(34) & fileName & Chr(34) Cells(row, 6).Value = "OK" Else 'Altrimenti scrive alla colonna 6 un avviso ! Cells(row, 6).Value = "ATTENTION: ATTACHMENT NOT FOUND!" End If RetVal = Shell(command, vbMinimizedFocus) Application.Wait (Now + TimeValue("0:00:1")) ' Pause for 1 second row = row + 1 email = Trim(Range("B" & row).Value) fileName = ThisWorkbook.Path & "\" & Trim(Range("C" & row).Value) & Trim(Range("D" & row).Value) Wend row = row - 1 MsgBox "Program ended at row: " & row, vbInformation End Sub ' Check if a file exists Function fileExists(fileName As String) As Boolean Dim obj_fso As Object Set obj_fso = CreateObject("Scripting.FileSystemObject") fileExists = obj_fso.fileExists(fileName) End Function
Automate everything and possible uses
If you are familiar with VBA you can take advantage of this system to automate the sending of some emails at specific intervals.
For example, it happened in the past to create a VBA program in Access that every month, at the beginning of the month, exported the time stamps of the attendance detector, and sent them via email in Zip format to the temporary administration agency. This is an example of how this solution can take away hours of work every month!
It is therefore sometimes worthwhile to spend some time on it, to be able to recover it later.
In those situations where you need to repeat a submission, it comes naturally to automate it. The solution described here is within everyone’s reach (Who doesn’t have Excel or Access in the company?).
If you chew on VBA you will be autonomous, even in customizing the solution, otherwise you will need a little help from your IT department.
Help me support this blog
If you want you can help me concretely to support this blog. You can do it with a free donation, by credit card or paypal. I will be grateful to you.