How to Automatically Send Email from Excel Based on Date

In the dataset, we have some email addresses (all of them are dummy addresses except one). We’re using a real address to demonstrate how the methods work.

How to Automatically Send Email from Excel Based on Date: 3 Ways

Method 1 – Using Excel VBA to Automatically Send an Email Based on a Fixed Date

This VBA macro emails if the date in a selected range matches today’s date. It prompts the user to select a range of cells and checks each cell. If a cell’s value matches the current date, it asks the user for the email subject, sender, recipient, CC, BCC, and message body. It then uses Outlook to send the email with the provided details.

Steps:

Go to the Developer tab and select Visual Basic. The VBA window will open.

Select Insert and pick Module.
Copy 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

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.
We set Range_Select to an InputBox where it can select a range of cells via a Message Box.
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.
The Outlook Application Object is defined by Email_Obj. We use Single_Mail to create an item for the Email_Obj.
We define Subject, Email_To, Cc, Bcc, Email_Text as the necessary parts of an Email through a With Statement. We also put the .Send command in this statement.

Go back to your sheet and run the Macro named SendEmail01.

You will see a Message Box pop up telling you to select the date range.
Select the range from the table and click OK.

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. Follow the instructions below.
Put your sender Email address.

Type in the Email address for the recipient.

Insert the CC address.

Put the BCC recipient in the Message Box if you want.

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.

Here is the Carbon Copy of the Email.

Thus you can automatically send an Email from Excel based on the date.

Method 2 – Sending Emails Automatically from Excel Based on Future Dates

In this section, we will send Emails based on future dates. Suppose you want to send someone an Email prior to 3 days or 7 days.

Steps:

Open a VBA Module (see Method 1 if necessary).
Copy 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 ignore the command.
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.

Go back to your sheet and run the Macro named SendEmail02.

You will see a message box pop up telling you to select the date range.
Select the range and click OK.

Another message box will pop up to select the Email address range.
Select the range and click OK.

Select the range of text messages in the message box and click OK.

You will see the email drafts for May 21 to May 24. We sent the corresponding email to the real address to show you the example. You can put a CC recipient if you want.

Let’s check the Email inbox. These can be sent to Spam box, too, so check everywhere.

Method 3 – Automatically Sending Emails to a Single Address on a Date

Steps:

Open a VBA Module.
Copy 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

We declared some necessary variables: rng and Date_Range as Range.
We defined the range for Date_Range.
We used 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.
We set the text value for Subject, Send_From, Send_To, Cc and Bcc.
We created an Outlook Application Object which we defined by Email_Obj. We’ll use Single_Mail to create an item for Email_Obj.
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.

Go back to your sheet and run the Macro named SendEmail03.

Follow the on-screen instructions.
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.

Here is the Carbon Copy of the Email.

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

You need to open and log into your Outlook account to run the VBA codes in this article.

Download Practice Workbook

Automatically Send Email.xlsm

<< Go Back To Send Email from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Public Sub SendEmailWhenDue() Dim Date_Range, Mail_Cc, Mail_Subject 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 Set Date_Range = Application.InputBox("Please choose the date range:", "Insert Date Range", Type:=8) If Date_Range Is Nothing Then Exit Sub Set Mail_Recipient = Application.InputBox("Please select the range of Email addresses:", "Insert Mail Recipeint", Type:=8) If Mail_Recipient Is Nothing Then Exit Sub Set Mail_Cc = Application.InputBox("Please choose the CC range:", "Insert CC Range", Type:=8) Set Mail_Subject = Application.InputBox("Please choose the Subject range:", "Insert Date Range", Type:=8) Last_Row = Date_Range.Rows.Count Set Date_Range = Date_Range(1) Set Mail_Recipient = Mail_Recipient(1) Set Mail_Cc = Mail_Cc(1) Set Mail_Subject = Mail_Subject(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 'Condition for sending mail. If CDate(Date_Range_Value) <= Date Then Send_Value = Mail_Recipient.Offset(i - 1).Value Cc = Mail_Cc.Offset(i - 1).Value Subject = Mail_Subject.Offset(i - 1).Value Email_Body = " Hi, <br> Please check the reminder." 'Compose your Body Here Set Mail_Item = Outlook_App_Create.CreateItem(0) With Mail_Item .Subject = Subject .Cc = Cc .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

Private Sub Workbook_Open() 'variable declaration Dim myOutlookApp As Object Dim myOutlookMail As Object Dim i As Long Dim Receiver As String Dim EmailBody As String Dim WS As Worksheet 'create outlook application object Set myOutlookApp = CreateObject("Outlook.Application") 'set worksheet Set WS = ActiveSheet 'loop through rows 'starting from the second row assuming row 1 is the Header For i = 2 To WS.Range("C" & Rows.Count).End(xlUp).Row 'check if the task is assigned today If WS.Range("C" & i) = Date Then 'check status If WS.Range("E" & i) = "In progress" Or WS.Range("E" & i) = "Outstanding" Then 'check if the email address is same as the previous one If WS.Range("G" & i) = Receiver Then 'merge email bodies for same email addresses EmailBody = EmailBody & vbCrLf & WS.Range("A" & i) Else 'if the email address is different, send the previous email 'create a new email If Receiver <> "" Then 'create a new mail item Set myOutlookMail = myOutlookApp.CreateItem(0) 'compose the email With myOutlookMail 'set the receiver email address .To = Receiver 'set the subject of the email .Subject = "Task Summary" 'set the body of the email .body = EmailBody 'send email .Send End With 'release the mail object Set myOutlookMail = Nothing End If 'set the receiver and body for the new email Receiver = WS.Range("G" & i) EmailBody = WS.Range("A" & i) End If End If End If Next i 'send the last email (if exists) If Receiver <> "" Then Set myOutlookMail = myOutlookApp.CreateItem(0) With myOutlookMail .To = Receiver .Subject = "Task Summary" .body = EmailBody .Send End With Set myOutlookMail = Nothing End If 'release the Outlook application object Set myOutlookApp = Nothing 'display a confirmation message MsgBox "The emails have been sent successfully from your outlook" End Sub

Private Sub Workbook_Open() 'variable declaration Dim myOutlookApp As Object Dim myOutlookMail As Object Dim i As Long Dim Receiver As String Dim EmailBody As String Dim WS As Worksheet 'create outlook application object Set myOutlookApp = CreateObject("Outlook.Application") 'set worksheet Set WS = ActiveSheet 'loop through rows 'starting from the second row assuming row 1 is the Header For i = 2 To WS.Range("C" & Rows.Count).End(xlUp).Row 'check if the task is assigned today If WS.Range("C" & i) = Date Then 'check status If WS.Range("E" & i) = "In progress" Or WS.Range("E" & i) = "Outstanding" Then 'check if the email address is same as the previous one If WS.Range("G" & i) = Receiver Then 'merge email bodies for same email addresses EmailBody = EmailBody & vbCrLf & WS.Range("A" & i) Else 'if the email address is different, send the previous email 'create a new email If Receiver <> "" Then 'create a new mail item Set myOutlookMail = myOutlookApp.CreateItem(0) 'compose the email With myOutlookMail 'set the receiver email address .To = Receiver 'set the subject of the email .Subject = "Task Summary" 'set the body of the email .body = EmailBody 'send email .Send End With 'release the mail object Set myOutlookMail = Nothing End If 'set the receiver and body for the new email Receiver = WS.Range("G" & i) If WS.Range("B" & i) <> "" Then EmailBody = WS.Range("A" & i) & ". You need to finish the work " & WS.Range("B" & i) Else EmailBody = WS.Range("A" & i) End If End If End If End If Next i 'send the last email (if exists) If Receiver <> "" Then Set myOutlookMail = myOutlookApp.CreateItem(0) With myOutlookMail .To = Receiver .Subject = "Task Summary" .body = EmailBody .Send End With Set myOutlookMail = Nothing End If 'release the Outlook application object Set myOutlookApp = Nothing 'display a confirmation message MsgBox "The emails have been sent successfully from your outlook" End Sub

Sub Send_Email_with_Attachment() Dim MyOutlook As Object Set MyOutlook = CreateObject("Outlook.Application") Dim MyMail As Object Set MyMail = MyOutlook.CreateItem(olMailItem) MyMail.To = "[email protected]" MyMail.CC = "[email protected]" MyMail.BCC = "[email protected]" MyMail.Subject = "Sending Email with VBA." MyMail.Body = "This is a Sample Mail." Attached_File = "F:\ExcelDemy\Attachment.xlsx" MyMail.Attachments.Add Attached_File MyMail.Send End Sub

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 - 4 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

Sub SendNotificationEmails() Dim outlookApp As Object Dim outlookMail As Object Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("DATA") lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).row For i = 5 To lastRow If ws.Cells(i, "C").Value - Date = 7 Then Set outlookApp = CreateObject("Outlook.Application") Set outlookMail = outlookApp.CreateItem(0) outlookMail.To = ws.Cells(i, "E").Value outlookMail.Subject = "Reminder: " & ws.Cells(i, "D").Value outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _ ws.Cells(i, "E").Value & "," & vbCrLf & _ "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _ "Deadline: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _ "Mail Content: " & ws.Cells(i, "D").Value & vbCrLf & _ "Please take necessary action." & vbCrLf & vbCrLf & _ "Regards," & vbCrLf & _ "Isaac Chavez" outlookMail.Display On Error Resume Next outlookMail.Send Set outlookMail = Nothing Set outlookApp = Nothing End If Next i End Sub

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 Dim DateMatched As Boolean DateMatched = False On Error Resume Next ' Get the selected range of cells 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 ' Loop through each cell in the selected range For Each Date_Range In Range_Select ' Check if the cell's value matches today's date If Date_Range.Value = Date Then DateMatched = True Exit For End If Next ' If no dates matched today's date, exit the sub If Not DateMatched Then MsgBox "No dates in the selected range match today's date." Exit Sub End If ' Prompt the user for email details 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) ' Create and send the email using Outlook 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 Sub

Sub SendEmailsBasedOnDates() Dim ws As Worksheet Dim i As Long, LastRow As Long Dim EmailDate As Date Dim OutApp As Object, OutMail As Object Set ws = ThisWorkbook.Sheets("Sheet1") LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow EmailDate = ws.Cells(i, 2).Value If EmailDate = Date Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ws.Cells(i, 1).Value .Subject = "Reminder" .Body = "This is your reminder email." .Send End With End If Next i Set OutMail = Nothing Set OutApp = Nothing End Sub

32 Comments

Rachel Jun 20, 2022 at 1:43 PM

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.
- Reply
  Nahian Jun 29, 2022 at 4:30 PM
  
  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
  - Reply
    Tanya Aug 30, 2022 at 8:23 AM
    
    I have the same issue. Is it possible to send emails without running the code
    - Nahian Sep 1, 2022 at 3:18 PM
      
      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
Liss Jul 13, 2022 at 1:58 PM

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
- Reply
  Nahian Jul 15, 2022 at 3:48 AM
  
  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 Explicit Private 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
pranav Jul 27, 2022 at 2:36 PM

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?
- Reply
  Nahian Aug 14, 2022 at 11:40 AM
  
  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.
Barry Sep 7, 2022 at 11:52 PM

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.
- Reply
  Nahian Oct 30, 2022 at 10:02 AM
  
  Hey Barry, thank you for reaching out. If you provide your workbook, that would be easy for me to understand your problem.
Sharon Oct 27, 2022 at 9:41 PM

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 ?
- Reply
  Nahian Oct 30, 2022 at 11:08 AM
  
  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.

Jignesh Mar 23, 2023 at 11:17 AM

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

Aniruddah Alam Mar 27, 2023 at 2:33 PM

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

VSP Jul 4, 2023 at 7:02 PM

I was wondering if you could help me with the code for below scenario.

I have excel data where I need to send emails on a specific date within the excel (dates available in Column C) to the corresponding email address in Column G with Mail body details in Column A (tasks that are due).

However, if there are multiple tasks due on the same day for the same email address, only one email should be sent with consolidated details of all tasks due under Column A.
Further the email should go only if the status of the task is ‘In progress’ or ‘Outstanding’ not for ‘Completed status’. Status available in column E.

Also, like the others I was wondering if there is a way to send automated emails on the due date without running the code.

Md. Abu Sina Ibne Albaruni Jul 9, 2023 at 3:47 PM

Dear VSP

Thank you very much for your question. I appreciate your interest in our article. I will show you the step-by-step process to create a macro according to your preferences. Also, you will be able to send automated emails on the due date without running the code.

● First, create the workbook. I am creating a sample workbook that has Message, Date, Status of work, and Email addresses in specific positions that you have mentioned.

Create Dataset
● Select your entire dataset.
● Go to the Insert tab >> Table.
● Check the My table has headers option.
● Press OK.

Create Table
● Sort your data according to your Email address.

Sort Email Addresses
● Now go to the Developer tab >> Visual Basic.

Open Visual Basic
● Go to ThisWorkbook and paste the following code into the VBA Macro Editor.
● Save the file as Excel Macro-Enabled Workbook (.xlsm) file.

VBA Code

● Now, to automatically send emails, type Run in your Start Menu and open the Run dialog box.
● Paste the following path there and press OK.

%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

Run Dialog Box
● Place your Excel file in the Startup folder.

Put Excel File in Startup Folder
Now whenever you open your PC, the VBA code of your Excel file will be automatically executed. Emails will be sent to the given email addresses according to the criteria you mentioned.

Notes:
● Stay logged in to your outlook application. We are using the OUTLOOK Microsoft 365 Office application.
● The provided code sends an email for the current date. You can customize it for any date.
● Make sure that your active worksheet contains the sorted table and save the file accordingly.

You can download the sample Excel file from here.
Send Emails.xlsm

If you have any more questions, please let us know in the comment sections.

Regards,
Md. Abu Sina Ibne Albaruni
Team ExcelDemy

TAN LI WEN Nov 15, 2023 at 4:39 PM

Hi , your sharing is helpful for me! appreciate it!
May I know how can I select more than 1 column in the email body ?

Meraz Al Nahian Nov 16, 2023 at 4:44 PM

Hi, Tan Li Wen, thanks for reaching out. Here, I developed a solution to your problem. I made a second column for email body in column B as shown in the picture.

Here is the modified code. Use it following the steps described in the previous comment.

Here, I made changes in the marked portion of the following image. If there is a second message in the column B, it will be added to the main Email body as You need finish the work within ‘x’ days. If there’s no such thing in column B, only the part of the Email body in column A will be sent.

Hope this helps. If you have any more questions, please let us know in the comment sections.

Regards,
Md. Meraz Al Nahian
Team ExcelDemy

Matthew Brennan Oct 11, 2023 at 4:30 PM

Hi, is there a way to add attachments to the email?

Meraz Al Nahian Oct 12, 2023 at 11:02 AM

Hi, thanks for reaching out. Yes, there is a way to send emails with attachments using VBA. Here is the code for you.

You just have to change the file location according to your perception and use some valid email addresses. Also, keep logged into your outlook account.

Hope Brown Oct 26, 2023 at 6:54 AM

I tried using the VBA for 3. However, it never actually opens an e-mail for me to send. I am unsure what I am doing wrong. I am most definitely an amateur, I am just trying to get an e-mail to send to one specific e-mail address when the deadline date is past by four days. I would also like to be able to CC another individual on this e-mail. Both e-mails are outlook e-mails. Any ideas?
- Reply
  Meraz Al Nahian Oct 26, 2023 at 1:07 PM
  Hi Mr. Brown, thanks for reaching out. Regarding the solution of your problem, you just have to modify the code a bit. You can use the code of the 3rd method, but I don’t know why it takes 15 minutes to send the email to the receiver.
  So, I used the code of the first method. I’m giving it here again with modification. See the image below to see where the modification is made.
```
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 - 4 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
```
  You wanted to send the Email if the deadline is past 4 days by the current date. So I subtracted 4 in the code.
  
  Remember, your desired date has to be present in the date list. I’m going to give you an example here. In the date list, the date 10/22/2023 is present. It is past 4 days by today’s date which is 10/26/2023.
  
  If the date is not present in the list, the code will work but it won’t send any email. If you have any confusion, follow method 1 to operate the macro. You can insert necessary Email addresses and the message easily. Hope this helps you. Thank you.

isaac chavez Apr 2, 2024 at 9:00 PM

Good day, I hope this message finds you well. I want to send an email notification from excel to outlook when the deadline is seven days out, in addition to this, I have tried several codes, but it seems it is not possible to complete this task automatically through VBA. how can I make this task automatic through VBA. I do not have access to automate.

Lutfor Rahman Shimanto Apr 4, 2024 at 12:40 PM

Dear Isaac Chavez

Thanks for visiting our blog. As requested, I have developed an Excel VBA Sub-procedure that will send an Email notification if the Deadline is 7 days ahead.

SOLUTION Overview:

Note: If the Microsoft Outlook dialog box appears, click Allow to send an email.

Excel VBA Sub-procedure:

I hope the sub-procedure will fulfil your requirements. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOK

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

Alvin Jul 25, 2024 at 7:44 PM

Hi,
I was trying to follow 1 option to create sending automatic emailing. I create worksheet, open VB and copy your programme, gone back to spreadsheet and open macro entered the date range but after that dosent open another pop-up for entering other datas.

Please explain WHY is doesn’t run.

Tks
ALVIN

Shamima Sultana Jul 28, 2024 at 11:15 AM

Hello Alvin,

This VBA macro sends an email if the date in a selected range matches Today’s date. It prompts the user to select a range of cells and checks each cell in the range. If a cell’s value matches the current date, it asks the user for the email subject, sender, recipient, CC, BCC, and message body. It then uses Outlook to send the email with the provided details.
In your date range you must include the date which will match Today’s date.
To understand the logic I added a debugging option here:

Regards
ExcelDemy

Liliana Oct 16, 2024 at 6:24 AM

Hi There,

Thank you for your help with excel! It has greatly aided in my use of macros.

I have a list of email addresses (column A) and a list of dates (column B). I need one email to be sent to each email address all on different dates. The dates can vary quite largely (e.g. some may be in 2 months and some in 10 months).

What is the best VBA code to use for this? I find your option 1 only sends emails run on that day. Option 2 is sending emails now for future dates and option 3 only allows one addressee.
- Reply
  Shamima Sultana Oct 16, 2024 at 11:31 AM
  Hello Liliana,
  
  You are most welcome. You can adapt Method 1 from this article on sending emails based on dates. Here’s an updated VBA code snippet that checks if the date in column B matches today’s date before sending the email.
```
Sub SendEmailsBasedOnDates()
    Dim ws As Worksheet
    Dim i As Long, LastRow As Long
    Dim EmailDate As Date
    Dim OutApp As Object, OutMail As Object

    Set ws = ThisWorkbook.Sheets("Sheet1")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        EmailDate = ws.Cells(i, 2).Value
        If EmailDate = Date Then
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)            
            With OutMail
                .To = ws.Cells(i, 1).Value
                .Subject = "Reminder"
                .Body = "This is your reminder email."
                .Send
            End With
        End If
    Next i
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
```
  This code will check the date in column B and send the email if the date matches today’s date.
  
  Regards
  ExcelDemy
Lili Oct 16, 2024 at 6:48 AM

Hi there,

Your articles have helped me greatly in my everyday use of excel. Thank you for this 🙂

I have a scenario that I am in need of some help with please. I have a list of email addresses (column A) and a list of dates (column B). I need one email to be sent to each email address specified in column A on the dates specified in column B.

Each date varies quite significantly. The email for one person may need to be sent on 1/1/25, where as another on 12/5/25 and another on 1/12/25 etc.

To clarify there is only one email body that I need each individual email to receive. each on a different specified date.

Thank you for your help.
- Reply
  Shamima Sultana Oct 16, 2024 at 11:32 AM
  Hello Lili,
  
  You are most welcome. You can adapt Method 1 from this article on sending emails based on dates. Here’s an updated VBA code snippet that checks if the date in column B matches today’s date before sending the email.
```
Sub SendEmailsBasedOnDates()
    Dim ws As Worksheet
    Dim i As Long, LastRow As Long
    Dim EmailDate As Date
    Dim OutApp As Object, OutMail As Object

    Set ws = ThisWorkbook.Sheets("Sheet1")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        EmailDate = ws.Cells(i, 2).Value
        If EmailDate = Date Then
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)            
            With OutMail
                .To = ws.Cells(i, 1).Value
                .Subject = "Reminder"
                .Body = "This is your reminder email."
                .Send
            End With
        End If
    Next i
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
```
  This code will check the date in column B and send the email if the date matches today’s date.
  
  Regards
  ExcelDemy
Donna Dec 4, 2024 at 4:58 AM

Hello Meraz,
I copied the 1st example code and set to run, but outlook did not completely open the first time and will not open now. Suggestions?
Thank you for any suggestions you may have.
- Reply
  Shamima Sultana Dec 4, 2024 at 11:22 AM
  
  Hello Donna,
  
  It seems like Outlook may not have fully initialized when the macro was first run. Here are a few steps to troubleshoot:
  Restart Your System: This clears any lingering background processes.
  Check Task Manager: Ensure no Outlook process is running. End any that are and retry.
  Verify Macro Settings: Make sure macros are enabled under Trust Center >> Macro Settings.
  Run Excel as Administrator: Sometimes permissions can block full integration with Outlook.
  
  Let me know if these steps resolve your issue!
  
  Regards
  ExcelDemy

How to Automatically Send Email from Excel Based on Date

How to Automatically Send Email from Excel Based on Date: 3 Ways

Method 1 – Using Excel VBA to Automatically Send an Email Based on a Fixed Date

Method 2 – Sending Emails Automatically from Excel Based on Future Dates

Method 3 – Automatically Sending Emails to a Single Address on a Date

Practice Section

Things to Remember

Related Articles

Leave a reply Cancel reply