Meraz Al Nahian
The article will show you the best possible ways to automatically send Email from Excel based on date. Usually, it’s easy to send an Email from Outlook or Gmail, but when you want to send Email at a certain time or date, you can use Microsoft Excel effectively.
In the dataset, we have some email addresses (all of them are dummy addresses except [email protected], which is mine. I’m using my address so that I can show you how the methods work.
Download Practice Workbook
3 Ways to Automatically Send Email from Excel Based on Date
1. Using Excel VBA to Automatically Send Email Based on a Fixed Date
We can send an Email automatically by using the Microsoft Visual Basic for Application (VBA) based on dates. You can make a list of dates when you want to send the Emails to their corresponding recipients. Let’s go through the process below for a better understanding. In this section, we will only work with the dates.
- First, go to Developer >> Visual Basic
- Then the VBA window will open. Select Insert >> Module
Type the following code in the VBA Module.
Type the following code in the VBA Module.Sub SendEmail01()
Dim Range_Select As Range
Dim Date_Range As Range
Dim Cell_Address As String
Dim Subject, Email_From, Email_To, Cc, Bcc, Email_Text As String
Dim Email_Obj, Single_Mail As Object
On Error Resume Next
Cell_Address = ActiveWindow.RangeSelection.Address
Set Range_Select = Application.InputBox("Select a range:", _
"Message Box", Cell_Address, , , , , 8)
If Range_Select Is Nothing Then Exit Sub
For Each Date_Range In Range_Select
If Date_Range.Value = Date Then
Subject = Application.InputBox("Subject: ", "Message", , , , , , 2)
Email_From = Application.InputBox("Send from: ", "Message Box", , , , , , 2)
Email_To = Application.InputBox("Send to: ", "Message Box", , , , , , 2)
If Email_To = "" Then Exit Sub
Cc = Application.InputBox("CC: ", "Message Box", , , , , , 2)
Bcc = Application.InputBox("BCC: ", "Message Box", , , , , , 2)
Email_Text = Application.InputBox("Message Body: ", "Message Box", , , , , , 2)
Set Email_Obj = CreateObject("Outlook.Application")
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.To = Email_To
.Cc = Cc
.Bcc = Bcc
.Body = Email_Text
.send
End With
End If
Next
End Sub
Code Explanation
- First, we declared some necessary variables: Range_Select and Date_Range as Range; Cell_Address, Subject, Email_From, Email_To, Cc, Bcc, Email_Text as String; Email_Obj and Single_Mail as Object.
- Then we set Range_Select to an InputBox where it can select a range of cells via a Message Box.
- After that, we use Value in a VBA IF Statement and also set Subject, Email_From and Email_To to an InputBox.
- If there is no Email address (Email_To= “”), the Sub Procedure will exit. Otherwise, we put some more variables in a new IF Statement. Cc and Bcc refer to Carbon Copy and Blind Carbon Copy of the Email that we wish to send to different people.
- Then we create an Outlook Application Object which we define by Email_Obj. Also, we use Single_Mail to create an item for Email_Obj.
- After that, we define Subject, Email_To, Cc, Bcc, Email_Text as the necessary parts of an Email by a With Statement. We also put the .Send command in this statement.
- Now, go back to your sheet and run the Macro named SendEmail01 as it is the name of your current Macro.
- After executing this operation, you will see a Message Box pop up telling you to select the date range. Select the range and click OK.
- Then, another Message Box will pop up. Type the subject and click OK.
- You will be seeing a chain of Message Boxes telling you to put the necessary information to send the Email. Just follow the images below.
- Put your Email address.
- Type the Email address where you will send your Email.
- Insert the CC recipient’s address.
- Put the BCC recipient in the Message Box if you want. I didn’t choose any.
- Type your message.
- After the final Message Box, you may see a warning box from Microsoft Outlook. Click Allow.
This operation will send the Email to the addresses that you put in the corresponding message boxes.
This is the main Email.
And below here is the Carbon Copy of the Email.
Thus you can automatically send an Email from Excel based on the date.
Read More: Excel Macro to Send Email Automatically (3 Suitable Examples)
2. Sending Email Automatically from Excel Based on Following Dates
In this section, we will send Emails based on the following or future dates. Suppose you want to send someone an Email prior to 3 days or 7 days. Let’s go through the following discussion on this matter.
Steps:
- First, go to Section 1 to see how to open a VBA Module.
- Then type the following code in the VBA Module.
Public Sub SendEmail02()
Dim Date_Range As Range
Dim Mail_Recipient As Range
Dim Email_Text As Range
Dim Outlook_App_Create As Object
Dim Mail_Item As Object
Dim Last_Row As Long
Dim VB_CR_LF, Email_Body, Date_Range_Value, Send_Value, Subject As String
Dim i As Long
On Error Resume Next
Set Date_Range = Application.InputBox("Please choose the date range:", "Message Box", , , , , , 8)
If Date_Range Is Nothing Then Exit Sub
Set Mail_Recipient = Application.InputBox("Please select the Email addresses:", "Message Box", , , , , , 8)
If Mail_Recipient Is Nothing Then Exit Sub
Set Email_Text = Application.InputBox("Select the Email Text:", "Message Box", , , , , , 8)
If Email_Text Is Nothing Then Exit Sub
Last_Row = Date_Range.Rows.Count
Set Date_Range = Date_Range(1)
Set Mail_Recipient = Mail_Recipient(1)
Set Email_Text = Email_Text(1)
Set Outlook_App_Create = CreateObject("Outlook.Application")
For i = 1 To Last_Row
Date_Range_Value = ""
Date_Range_Value = Date_Range.Offset(i - 1).Value
If Date_Range_Value <> "" Then
If CDate(Date_Range_Value) - Date <= 7 And CDate(Date_Range_Value) - Date > 0 Then
Send_Value = Mail_Recipient.Offset(i - 1).Value
Subject = Email_Text.Offset(i - 1).Value & " on " & Date_Range_Value
VB_CR_LF = "<br><br>"
Email_Body = "<HTML><BODY>"
Email_Body = Email_Body & "Dear " & Send_Value & VB_CR_LF
Email_Body = Email_Body & "Text : " & Email_Text.Offset(i - 1).Value & VB_CR_LF
Email_Body = Email_Body & "</BODY></HTML>"
Set Mail_Item = Outlook_App_Create.CreateItem(0)
With Mail_Item
.Subject = Subject
.To = Send_Value
.HTMLBody = Email_Body
.Display
End With
Set Mail_Item = Nothing
End If
End If
Next
Set Outlook_App_Create = Nothing
End Sub
Code Explanation
- First, we declared some necessary variables: Date_Range, Mail_Recipient and Email_Text as Range; Outlook_App_Create and Mail_Item as Object; Last_Row and i as Long; VB_CR_LF (Visual Basic Carriage Return Line Feed), Email_Body, Date_Range_Value, Send_Value, Subject as String.
- Then we set Date_Range to an InputBox where it can select a range of dates via a Message Box. An If Statement is used to terminate Sub Procedure if the Date_Range is Nothing.
- We also set Mail_Recipient and Email_Text to InputBox
- After that, we create an Outlook Application Object which we define by Outlook_App_Create.
- A date interval is introduced. In this code, the duration is 7. Suppose you want someone to be reminded about an event or greetings 7 days after today which is 19th May. If the Emails you want to send are within the dates between 20th to 26th May, the recipient will receive the Email. Otherwise, it will not reach.
- Then we put some commands to format the Email_Body. And also define the necessary parts of the Email by the With Statement.
- We used the .Display command here so that Outlook will open these Email drafts and we can check if any other messages need to be sent. You can use the .Send command if you don’t want to see the message drafts. But do not use these two commands simultaneously.
- Now, go back to your sheet and run the Macro named SendEmail02 as it is the name of your current Macro.
- After executing this operation, you will see a message box pop up telling you to select the date range. Select the range and click OK.
- Then, another message box will pop up and it will tell you to select the Email address range. Select the range and click OK.
- After that, select the range of text messages in the message box and click OK.
- You will see the Email drafts for 21st May to 24th May. Here, the ID [email protected] is an actual Email ID, so we sent the corresponding Email to this address to show you the example. You can put a CC recipient if you want.
- Let’s check my Emails. This can be sent to Spam box too. So check everywhere.
Thus you can automatically send email based on following dates
Read More: Send Reminder Email Automatically from an Excel Worksheet Using VBA
Similar Readings
- How to See Who Is in a Shared Excel File (With Quick Steps)
- Enable Share Workbook in Excel
- How to Share Excel File for Multiple Users
- Send Bulk Email from Outlook Using Excel (3 Ways)
- How to Apply Macro to Send Email from Excel with Attachment
3. Automatically Sending Email to a Single Address Based on a Date
We can also send an Email automatically single address based on a date. You can make a list of dates when you want to send the Emails to their corresponding recipients. Let’s go through the process below for a better understanding. In this section, we will only work with the dates.
Steps:
- First, go to Section 1 to see how to open a VBA Module.
- Type the following code in the VBA Module.
Option Explicit
Sub SendEmail03()
Dim Date_Range As Range
Dim rng As Range
Set Date_Range = Range("B5:B10")
For Each rng In Date_Range
If rng.Value = Date Then
Dim Subject, Send_From, Send_To, _
Cc, Bcc, Body As String
Dim Email_Obj, Single_Mail As Variant
Subject = "Hello there!"
Send_From = "[email protected]"
Send_To = "[email protected]"
Cc = "[email protected]"
Bcc = ""
Body = "Hope you are enjoying the article"
On Error GoTo debugs
Set Email_Obj = CreateObject("Outlook.Application")
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.To = Send_To
.Cc = Cc
.Bcc = Bcc
.Body = Body
.send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
Code Explanation
- First, we declared some necessary variables: rng and Date_Range as Range.
- Then we define the range for Date_Range.
- After that, we use Value in a VBA IF Statement and also set Subject, Send_From, Send_To, Cc, Bcc and Body to String. Also, we set Email_Obj and Single_Mail as Variant.
- Then we set the text value for Subject, Send_From, Send_To, Cc and Bcc.
- Later, we create an Outlook Application Object which we define by Email_Obj. Also, we use Single_Mail to create an item for Email_Obj.
- After that, we define Subject, Send_To, Cc, Bcc, Body as the necessary parts of an Email by a With Statement. We also put the .Send command in this statement.
- Now, go back to your sheet and run the Macro named SendEmail01 as it is the name of your current Macro.
- After that, you may see a warning box from Microsoft Outlook. Click Allow.
This operation will send the Email to the addresses that you put in the corresponding message boxes.
This is the main Email.
And below here is the Carbon Copy of the Email.
Thus you can automatically send an Email from Excel based on the date.
Read More: How to Send Email Automatically When Condition Met in Excel
Practice Section
In this section, I’m giving you the dataset that we used in this article so that you can practice on your own.
Things to Remember
Always open and log into your Outlook account to run the VBA codes in this article.
Conclusion
The bottom line is, that you will learn effective methods on how to automatically send Email from Excel based on date. If you have any better ideas or feedback, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
- How to Send Email from Excel List (2 Effective Ways)
- How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
- Macro to Send Email from Excel (5 Suitable Examples)
- Macro to Send Email from Excel with Body (3 Useful Cases)
- Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
hi for the 3rd code, where an email will automatically be sent via the date, is it required to keep rerunning the macro? because i realise that when i manually change the date on my laptop, i do not receive an auto email.
hello there, thanks for asking. I understand your problem. Although I haven’t found the exact solution to your problem yet, I can help you on sending email at a certain time in a day. The 3rd code sends email in the current date when you run it. You can add a scheduler in the code. The email will be sent at the time you set in the code. I added the code below. You can see that the time here is 16:18:00. So if you run the code before 16:18, and keep the corresponding Excel file open, the email will be sent at 16:18. You don’t need to rerun this code during this time. Hope this helps you a bit. I’ll be working on your problem too. If I get a solution, I’ll immediately add that in your reply.
Option Explicit
Private Sub Workbook_Open()
Application.OnTime TimeValue("16:18:00"), "SendEmail03"
End Sub
Sub SendEmail03()
Dim Date_Range As Range
Dim rng As Range
Set Date_Range = Range("B5:B10")
For Each rng In Date_Range
If rng.Value = Date Then
Dim Subject, Send_From, Send_To, _
Cc, Bcc, Body As String
Dim Email_Obj, Single_Mail As Variant
Subject = "Hello there!"
Send_From = "[email protected]"
Send_To = "[email protected]"
Cc = "[email protected]"
Bcc = ""
Body = "Hope you are enjoying the article"
On Error GoTo debugs
Set Email_Obj = CreateObject("Outlook.Application")
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.to = Send_To
.Cc = Cc
.Bcc = Bcc
.Body = Body
.Send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
I have the same issue. Is it possible to send emails without running the code
Hi Tanya, thanks for reaching out. I understand your problem. It may not be possible to send the Email just by running the code and wait for the day to come. But I can help you on sending email at a certain time in a day. The 3rd code sends email in the current date when you run it. You can add a scheduler in the code. The email will be sent at the time you set in the code. I added the code below. You can see that the time here is 16:18:00. So if you run the code before 16:18, and keep the corresponding Excel file open, the email will be sent at 16:18. You don’t need to rerun this code during this time. Hope this helps you a bit. I’ll be working on your problem too. If I get a solution, I’ll immediately add that in your reply.
Option Explicit
Private Sub Workbook_Open()
Application.OnTime TimeValue(“16:18:00”), “SendEmail03”
End Sub
Sub SendEmail03()
Dim Date_Range As Range
Dim rng As Range
Set Date_Range = Range(“B5:B10”)
For Each rng In Date_Range
If rng.Value = Date Then
Dim Subject, Send_From, Send_To, _
Cc, Bcc, Body As String
Dim Email_Obj, Single_Mail As Variant
Subject = “Hello there!”
Send_From = “[email protected]”
Send_To = “[email protected]”
Cc = “[email protected]”
Bcc = “”
Body = “Hope you are enjoying the article”
On Error GoTo debugs
Set Email_Obj = CreateObject(“Outlook.Application”)
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.to = Send_To
.Cc = Cc
.Bcc = Bcc
.Body = Body
.Send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> “” Then MsgBox Err.Description
End Sub
hello i was wondering how i could use this for email reminders when products are due to expire – i’d like to use one or two email addresses to alert certain staff members when it is time to re-order something. Which code would be best to use? Would these automatically send the emails on each date or would i have to run the macros on each date to get the emails sent out – many thanks in advance
hi, thanks for asking Liss. It will be better for you to use the code of the second method. You may not be able to set a reminder using VBA, but you can set a date interval using it. If you go through the explanation, you will see that I created a 7 days interval in the code (you can change it according to your convenience). If you want to remind your employee that he should finish his job within a week, you can use the dataset and VBA code of the second method. You cannot send emails on a particular date. Suppose you run this code on 15th July. You have a date range of 15th July to 25th July. The employees who have to finish their task within 16th to 22nd July will get your mail. Those who have to finish within 23rd to 25th won’t get your mail. However, you can make a schedule to send your mail in a particular time of the day. Just put the following statement at the beginning of the code of the second method. Hope this helps
Option ExplicitPrivate Sub Workbook_Open()
Application.OnTime TimeValue("hh:mm:ss"), "SendEmail02" 'put the time when you want to send the email
End Sub 'you must keep open your excel file until then after you run the code
Hello,
I have the same email address “[email protected]” in the multiple cells of the ‘Email Address’ Column. I want one single mail for “[email protected]” containing all the tasks , instead of individual mails for individual task for the same Email Address.
How do I achieve this in the above Macro?
Hi Pranav, thanks for the query. You can simply use the code of the second method of the article in this regard. Just follow the procedure after you run the code. Hope that helps you.
Hello Nahian.
I’ve just discovered your code samples for automating the sending of emails within Excel. I would just like to say thank you for providing these procedures. I am still a novice with VBA but with snippets from 03 version I have adapted version 02 to send event reminders from a ‘calendar’ list in an Excel worksheet. I have an icon on the ‘calendar’ worksheet to click-on to trigger the sending of the emails.
Eventually I hope to adapt it further as I have a gardening calendar with (currently) over 600 events across the full year. What would be ideal is to automatically open the workbook and run the email-sender as a single operation. Not sure how as yet but I understand it can be done. Anyway I am rambling on rather but thank you again.
Hey Barry, thank you for reaching out. If you provide your workbook, that would be easy for me to understand your problem.
I use Sendemail02 to sending email automatically from excel based on following dates, but the mail body i would like to mail merge with different letter, what should i do ?
The message text in the second is actually the mail body. If you want to change the Subject of the email, you can simply change it in the code. Please follow the code in the picture below. This subject will be the same for all the mails.
Dear NAHIAN,
i have started recently working with macros and got a task to send auto. mail,
I want to trigger auto mail from excel if the date is due and mail shall seek “To” “CC” and “Subject” from the excel list as below
To” Email ID CC” Email ID Subject Date
[email protected] [email protected] Only 5 Day to Go 28/03/23
[email protected] [email protected] Only 2 Day to Go 25/03/23
will pls provide the macro coding
Thank you –
Jignesh
Thank you JIGNESH for reaching out. Here is a VBA Macro code that will take “To Email Address”, “Email ID CC”, “Subject”, and “Date” ranges from the user and if the code finds any Date which is greater than the Current Date, an email will be sent to the corresponding mail address and CC address with corresponding Subject line. If you have any further queries, feel free to post on our ExcelDemy Forum.
Regards
Aniruddah