Email : How to add read receipt in VBA.

How to add email read receipt in VBA.

In a previous post, I explained how to send emails in excel using VBA (send an email with attachments via Excel), but it was an illustration of the core stuff required to send an email via excel, update the information required to control the whole process that why I wanted to make minimal the solution and make it usable for everyone.

Obviously, some readers can’t live on bread alone and have asked how to achieve extras features

Many readers asked how to add the read receipt.

You have to set this property :

.ReadReceiptRequested = True

Where to add it ? in the code you have already in place so that the code will change in this way

Add email Receipt Confirm VBA

Add email Receipt Confirm VBA

There are a lot of other property that you can set and in this Microsoft’s page, you can find the most important and I suppose relevant to you ( a very useful property is “DeferredDeliveryTime” that allows setting the delivery time so you can optimize the

quantity of the emails send according to your email provider limits or the customers local times.).

Once learning how to set one you can easily set the properties you need.

How to send email from Excel

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

excel sheet used to send email

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.

In case you want to add more data, add the columns always after the last provided in the

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.


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.


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 “)  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.


Vba macro used to send email via excel

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)
  • .Send


Here you can Download the excel file (it’s an xlsm ) : Email_send_blog 2018  servermx.offers servermx.features