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.

Steps:

• First, go to Developer >> Visual Basic 

• Then the VBA window will open. Select Insert >> 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 Mail_Object, 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 Mail_Object = CreateObject("Outlook.Application")
Set Single_Mail = Mail_Object.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

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

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  0 Then
Send_Value = Mail_Recipient.Offset(i - 1).Value
Subject = Email_Text.Offset(i - 1).Value & " on " & Date_Range_Value
VB_CR_LF = "

"
Email_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 & ""
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

• 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

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 Mail_Object, 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 Mail_Object = CreateObject("Outlook.Application")
Set Single_Mail = Mail_Object.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

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

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.