Automatically Send Email from Excel Based on Date — Full Guide for Beginners and Professionals

Have you ever wished that Excel could automatically send an email when a particular date occurs, say to remind the client of his/her payment, to pursue a lead, or to remind you of their next scheduled due date? The answer to that is yes, one can automatically dispatch emails out of Excel and based upon a date using a combination of VBA (Visual Base attacks Applications) scripting, Outlook automated control, and Excel formulas. It is possible to save hours of manual work, and your communications will never be late. We are going to do it step-by-step in this article, even without knowing how to write code.

Why Automate Emails from Excel?

Emailing may be cumbersome when you have many friends or business deals to make or get. This can be automated in Excel to:

• Avoid spending time and absolutely book bulk emails.
• Eliminate human errors, i.e., missed following or date not entered.
• Enhance workflow performance in HR, finance, or sales.
• The office demonstrated integration with either Outlook or Gmail to enable a smooth communication process.

Indicatively, when you have a spreadsheet where you have some due dates of clients’ payments, you can automatically set a reminder email to the clients between one and two days before the due date.

Prerequisites Before You Start

Before designing the automation, ensure that you will have:

1. Microsoft Excel (end desirably 2016 or beyond).
2. Microsoft Outlook (when emails are used to send them using Outlook).
3. An updated Excel list of at least:
   • Recipient Email Address
   • Subject
   • Message Body
   • Date to Send Email

An example of how your Excel table can appear is presented below:

How to Automatically Send Email from Excel Based on Date? Step-by-Step Guide

Step 1: Enable the Developer Tab in Excel

To use the Developer tab in Excel, (unless this has been enabled already), you can do this:

1. Click File → Options → Customize Ribbon.
2. Check the box labeled Developer.
3. Click OK.

This will enable you to open the Visual Basic Editor in order to case your automation code.

Step 2: Write the VBA Script to Send Emails Automatically

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) window.
2. Go to Insert → Module.
3. Paste the following VBA code:

   vba   Copy code
   
   Sub Send_Email_Based_On_Date()
   
   Dim OutlookApp As Object
   
   Dim OutlookMail As Object
   
   Dim ws As Worksheet
   
   Dim i As Integer
   
   Dim sendDate As Date
   
   Dim todayDate As Date
   
   Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to your sheet name
   
   todayDate = Date
   
   Set OutlookApp = CreateObject("Outlook.Application")
   
   For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
   sendDate = ws.Cells(i, 5).Value
   
   If sendDate <= todayDate And ws.Cells(i, 6).Value <> "Sent" Then
   
   Set OutlookMail = OutlookApp.CreateItem(0)
   
   With OutlookMail
   
   .To = ws.Cells(i, 2).Value
   
   .Subject = ws.Cells(i, 3).Value
   
   .Body = ws.Cells(i, 4).Value
   
   .Send
   
   End With
   
   ws.Cells(i, 6).Value = "Sent"
   
   End If
   
   Next i
   
   MsgBox "Emails have been sent successfully!", vbInformation
   
   End Sub

Explanation of Code:

• The script takes about the rows in your Excel sheet.
• It verifies the date of the column, which is the Send Date, as to whether it is older than or the current date.
• In case the email has not been dispatched, it builds an email and sends it through Outlook.
• After sending, it updates the Status column to “Sent”.

Step 3: Run the Macro Automatically on Workbook Open

If you want the emails to send automatically when you open the workbook, you can use this additional code:

1. In the VBA Editor, double-click ThisWorkbook.
2. Add this code:

   vba   Copy code
   
   Private Sub Workbook_Open()
   
   Call Send_Email_Based_On_Date
   
   End Sub

This is because now whenever working in your Excel file, it will automatically set the date and send the emails.

Step 4: Schedule the Excel File to Open Automatically (Optional)

Task Scheduler in Windows defines your Excel file to open at a particular time every day, so that your mail will automatically send.

Steps:

1. Open Task Scheduler on your PC.
2. Click Create Task → Set the trigger (for example, daily at 9 AM).
3. Under Action, choose Start a program → Browse and select Excel.
4. Add your workbook’s file path as an argument.

Now your Excel automation will run daily without your manual input.

Step 5: Add Conditional Formatting or Alerts

And to keep track of your progress, you may make use of the Excel Conditional Formatting to highlight the so-called row-rows where emails were dispatched or are about to be dispatched very soon.

For example:

• Select Cells in the Send Date Column that are within the last 2 days.
• Use a rule like:

  bash   Copy code
  
  =AND($E2-TODAY()<=2, $F2<>"Sent")

Then choose a color (like orange) for visual alerts.

Step 6: Using Gmail Instead of Outlook (Alternative Option)

In a different case, when you are not using Outlook, it is possible to send an email via Gmail using a foreign library called CDO. (Collaboration Data Objects). Here’s a simplified version:

vba   Copy code
Sub SendMailUsingGmail()
Dim Mail As Object
Dim Config As Object
Dim ws As Worksheet
Dim i As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")

For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If ws.Cells(i, 5).Value = Date Then
Set Mail = CreateObject("CDO.Message")
Set Config = CreateObject("CDO.Configuration")

Config.Load -1
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
Config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"
Config.Fields.Update

With Mail
Set .Configuration = Config
.To = ws.Cells(i, 2).Value
.Subject = ws.Cells(i, 3).Value
.TextBody = ws.Cells(i, 4).Value
.Send
End With
End If
Next i
MsgBox "Gmail Emails Sent!"
End Sub

Note: To work safely this might require you to set preparations on your Gmail account so that it uses passages on apps.

Step 7: Troubleshooting Common Issues

1. Outlook Security Warning
   You may receive the prompt regarding letting access. One can prevent it by using a digital signature or trusted macro settings.
2. Macros Disabled
   Ensure macros are enabled in Excel:
   Go to File → Options → Trust Center → Macro Settings → Enable all macros (for internal use only).
3. Incorrect Date Format
   Be sure that your column of Send Date is in a good date format (e.g., DD-MM-YYYY).

Benefits of Automating Emails with Excel

• Economical: There is no need to have third-party email automation tools.
• Customizable: You can make messages and triggers customizable.
• Scalable: Operates with hundreds of contacts.
• Speed: Ideal when it comes to frequent reminders or report emails.

Final Thoughts

Send out mail automatically based on date, as an Excel workbook is a tremendously strong automation tool for anyone who deals with reminders, billing, HR email, or project updates. Excel may be transformed into a kind of dynamic communication tool, which will keep your operations promptly moving on, with the help of a few lines of VBA code.<|human|>Excel can become the type of dynamic communication that will keep your operations in the flow, even when you are not available anymore.

You have incorporated Excel as a major part of your daily routine, and so employing this automation should be an attempt – choose one of those time-saving gimmicks to overhaul your daily routine.