How to send email from excel based on cell value
Send email from excel will be really easy if you will read this post.
This post will give you a simple illustration of how to use the attached excel sheet to send emails or, if you need it, how to use the source code inside it in other excel files. I suggest to start from the base and modified it according to your needs.
Send email from excel : MACRO
The macro provides the following functions:
- Reading data from excel
- formatting body email
- set recipients
- set up CC
- attach files
- send the email
- update sending data
Before to proceed it’s important to understand the meaning of the light blue arrow.
What does the LIGHT BLUE ARROW BELOW “SENT ON” mean?
It represents the range that the VBA macro automatically detects in order to process the email.
Your bulk email process can be handled by this short macro very useful and easy to manage (it’s easily implemented even by those who do not know much excel).
The excel sheet contains the information required by the macro refers (the code refers to the columns and not to the header) and a glance is sufficient to understand which kind of data we have available.
A delay has been inserted between sending an email to the other to avoid being blocked by the provider or, worse still, classified as spammers.
This code has been tested in Excel 10 and Excel 2016 with outlook.
It’s is easily adaptable in other contexts and soon I will also provide other versions for different scenarios.
EXCEL FILE EXAMPLE & STRUCTURE – SHORT FIELD DESCRIPTION
First of all, take in account that the macro gets the first empty cell of the column H (8) and the first empty cell of column A (1) and uses these boundaries to instance the reading loop ( for .. to ..).
The column H (8) it’s very important because it’s used to store the email’s sent date (the VBA code automatically populate this cell according to the elaborated row).
In this case, the loop while elaborate the row from 2 to 11.
EXCEL FOR SEND EMAIL – COLUMNS & CELLS CONTENT
Even if is really intuitive let me give a brief synthesis of the columns.
- Column A: Here you must to set the destination email (TO) and it’s a mandatory field.
- Column B: Here you must to set presentation name used in the opening of the email (e.g. “Dear MAX “) and it’s a mandatory field.
- Column C: It contains the email’s subject and it’s a mandatory field.
- Column D: You can specify the signature to use at the end of the email (e.g. “Cheers Servermx.com “) and it’s a mandatory field.
- In column E there is a value that can be used in the body of the email. In this example is a mandatory field, but it can update according to your needs.
- Column F: Here you have to set the carbon copy destination of the email (CC) and it’s an optional field.
- The column G It’s used to get the attachment file (Attachment) and it’s an optional field.
Anyway, the code can easily update in order to change the body, subject and so on.
VISUAL BASIC CODE
In the end the core is :
- Set OutlookApp = CreateObject(“Outlook.Application”)
- Set Mess = OutlookApp.CreateItem(olMailItem)
- With Mess
- .Subject = Subj
- .Body = Msg
- .Recipients.Add (Email)
- .CC = CC
- .Attachments.Add (Attach)