How to Automatically Send Email Based on Date from Excel

Sending emails automatically based on specific dates in Excel can simplify reminders, notifications, or alerts without manual effort. This is especially useful for birthdays, deadlines, renewals, or appointments.

In this article, we will learn how to trigger email sending from Excel when a date condition is met, using VBA macros to automate the process.

Steps to Automatically Send Email Based on Date from Excel

You can set up Excel to automatically send emails when a specific date is reached in your spreadsheet. Follow the steps below to configure the process and trigger emails based on your chosen dates.

Step 1: Write VBA to Automatically Send Emails on a Specific Date

VBA macros can scan your Excel sheet daily, check if any date matches today’s date, and send personalized emails through Outlook automatically. This is the most direct and customizable way to automate date-based emailing.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor in Excel.
➤ Insert a new module and paste a VBA macro like this:

Sub SendEmailOnDate()
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long
    Dim MailTo As String, MailSubject As String, MailBody As String
    Dim TargetDate As Date
    Dim olApp As Object, olMail As Object
    Dim today As Date
    Dim emailCount As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change if needed
    today = Date
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    emailCount = 0
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    If olApp Is Nothing Then
        MsgBox "Outlook is not available. Please open Outlook and try again.", vbCritical
        Exit Sub
    End If
    For i = 2 To lastRow
        If IsDate(ws.Cells(i, "A").Value) Then
            TargetDate = ws.Cells(i, "A").Value
            If TargetDate = today Then
                MailTo = ws.Cells(i, "B").Value
                MailSubject = "Reminder: " & ws.Cells(i, "C").Value
                MailBody = "This is a reminder for: " & ws.Cells(i, "C").Value & vbCrLf & _
                           "Details: " & ws.Cells(i, "D").Value
                Set olMail = olApp.CreateItem(0)
                With olMail
                    .To = MailTo
                    .Subject = MailSubject
                    .Body = MailBody
                    .Send
                End With
                Set olMail = Nothing
                emailCount = emailCount + 1
            End If
        End If
    Next i
    If emailCount > 0 Then
        MsgBox emailCount & " email(s) prepared. Please review and send them from Outlook.", vbInformation
    Else
        MsgBox "No emails matched today's date (" & Format(today, "dd-mmm-yyyy") & ").", vbInformation
    End If
End Sub

➤ Adjust column references as per your worksheet.
➤ Run the macro manually or schedule it with Windows Task Scheduler to open the workbook and execute daily.

This method requires Outlook installed and trusted macros enabled.

Step 2: Apply Workbook Open Event to Trigger Emails Automatically

You can automate email sending each time you open the workbook by embedding the macro in the Workbook Open event. This is helpful if you want emails to send whenever the file is accessed.

Steps:

➤ Open VBA editor (Alt + F11).
➤ Double-click ThisWorkbook in the Project Explorer.
➤ Paste this code inside:

Private Sub Workbook_Open()
    Call SendEmailOnDate
End Sub

➤ Make sure the SendEmailOnDate macro from Method 1 is in a standard module.
➤ Save the workbook as a macro-enabled file (*.xlsm) by hitting Save on the pop-up when you go to File tab >> Save as usual.

➤ Each time you open the workbook, emails for the current date will be sent automatically.

This method depends on manual opening but automates sending without extra clicks.

Frequently Asked Questions

Can I send emails automatically without Outlook installed?

Yes, using cloud tools like Power Automate you can send emails without Outlook, but VBA macros require Outlook on the desktop.

How do I schedule VBA macros to run daily automatically?

You can use Windows Task Scheduler to open your Excel workbook daily, triggering macros set in the Workbook Open event.

Can Excel send emails based on time as well as date?

Excel VBA can be programmed to check times, but scheduling by time usually needs external tools or continuous background processes.

What security settings affect automatic email sending in Excel?

Macro security settings must allow trusted macros. Outlook’s programmatic access security may prompt permission to send emails via VBA.

Wrapping Up

In this tutorial, we learned how to automatically send emails from Excel based on specific dates using VBA and Workbook Open events. Each method helps simplify communication and reduce the risk of missed deadlines, especially for recurring reminders or scheduled alerts. Feel free to download the practice file and share your feedback.