VBA & Macro Problems

Send Automatic Outlook Emails from Excel When a Cell Meets a Condition

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 868 views

Question details

Send an Outlook email automatically when an Excel cell meets a specified condition.

Send Automatic Outlook Emails from Excel When a Cell Meets a Condition
Product
Microsoft Excel and Outlook
Device & OS
not provided
Scenario
A worksheet tracks the days remaining before a membership renewal and needs to email a reminder when the count falls below 15.
Observed behavior
An email is sent when the monitored cell contains a number below 15.
Before you start

Use desktop Excel and classic Outlook for Windows, with an Outlook email account configured. Identify the cell containing the days remaining and the recipient's email address; the example below uses B2.

Solution 1Recommended

Use Excel VBA to send an Outlook email when the countdown is below 15

Use worksheet events to check B2 when the sheet changes or recalculates and send a reminder through Outlook.

This example monitors one cell and sends once while the condition remains true during the current Excel session. It resets when B2 is no longer below 15, and its sent status does not persist after the workbook is reopened.

The example also treats negative numbers as below 15, so it includes overdue renewals. Excel must be open and the sheet must change or recalculate for the check to run.

1
Set up the countdown

Enter the renewal date in A2 and use =A2-TODAY() in B2, or put your existing days-remaining value in B2. Change the cell reference in the code if your countdown is elsewhere.

2
Add the worksheet code

Press Alt+F11 in desktop Excel. In the VBA Project pane, double-click the worksheet containing B2 and paste this code into its code window: Private Sub Worksheet_Change(ByVal Target As Range) CheckRenewal End Sub Private Sub Worksheet_Calculate() CheckRenewal End Sub Private Sub CheckRenewal() Static reminderSent As Boolean Dim value As Variant Dim outlookApp As Object Dim mail As Object value = Me.Range("B2").Value If IsError(value) Then reminderSent = False Exit Sub End If If Not IsNumeric(value) Or IsEmpty(value) Then reminderSent = False Exit Sub End If If CDbl(value) >= 15 Then reminderSent = False Exit Sub End If If reminderSent Then Exit Sub On Error GoTo SendError Set outlookApp = CreateObject("Outlook.Application") Set mail = outlookApp.CreateItem(0) With mail .To = "recipient@example.com" .Subject = "Membership renewal reminder" .Body = "The membership renewal is less than 15 days away." .Send End With reminderSent = True Exit Sub SendError: MsgBox "The reminder could not be sent: " & Err.Description, vbExclamation End Sub

3
Customize and test the message

Replace recipient@example.com, the subject, and the body with your details. For a test, change .Send to .Display, then enter 14 in B2 or press F9 to recalculate; inspect the draft before sending it manually. Restore .Send for automatic sending, and change B2 to 15 or more before testing the trigger again.

4
Save and enable the macro

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Reopen the trusted workbook and select Enable Content if prompted, then set B2 below 15 and verify that Outlook processes the message.

Use Excel VBA to send an Outlook email when the countdown is below 15
Check Outlook sending behavior: Outlook security settings or organizational policies may show prompts or prevent automatic sending. The message may remain in the Outbox until Outlook can send it.
Free Microsoft Office alternative

Track membership renewals in WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheets. Open compatible Excel files and use formulas to track renewal dates as you migrate your everyday work; it does not fix or replace the Excel-to-Outlook VBA automation described above.

Use spreadsheet formulas to calculate days until renewal.Open and edit compatible Microsoft Excel XLSX files.Work in a familiar spreadsheet interface that makes everyday migration easier.Use a free, lightweight office suite for common document tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Will the email be sent if the Excel workbook is closed?

No. The VBA events run only while the workbook is open and the worksheet changes or recalculates. Press F9 in Excel to request a recalculation while the workbook is open.

How do I prevent a new email on every recalculation?

The example uses a VBA Static variable to send only once while B2 remains below 15. The variable resets when the condition becomes false or the VBA session ends, so it does not prevent another email after reopening the workbook.

Can I change the threshold or monitored cell?

Yes. In the worksheet code, replace B2 with your cell reference and change 15 in the comparison If CDbl(value) >= 15 Then to your desired threshold.

Why didn't Outlook send the reminder?

Check that classic Outlook for Windows has a configured account, that the workbook's macros are enabled, and that B2 contains a number below 15. Also check Outlook's Outbox and any security prompts or organizational restrictions.