VBA & Macro Problems

Automatically Send an Outlook Email When an Excel Cell Says Yes

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Automatically send an Outlook email when Excel cell BD2 changes to Yes.

Automatically Send an Outlook Email When an Excel Cell Says Yes
Product
Microsoft Excel and Microsoft Outlook
Device & OS
not provided
Scenario
A user wants an email to be sent when a worksheet cell is updated to Yes.
Observed behavior
A HYPERLINK mailto formula opens an email draft but does not send it automatically.
Before you start

For the VBA method, use desktop Excel and Outlook on Windows, with Outlook configured to send mail. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and choose an unused cell such as BE2 to track whether the message was sent.

Solution 1Recommended

Use a worksheet event to send an Outlook email

Use this method when someone directly edits BD2 in an open desktop Excel workbook.

The worksheet event checks BD2 and sends through Outlook when its value is Yes. BE2 records a successful send and is cleared when BD2 is changed to another value.

This event does not run when BD2 changes only because a formula recalculates. Excel macros must be enabled, and Outlook security settings may prompt for approval or restrict sending.

1
Open the worksheet code

In Excel, right-click the sheet tab containing BD2 and select View Code.

2
Add the event macro

Paste the following code into that worksheet's code window. Replace the recipient address, subject, and message before using it: Private Sub Worksheet_Change(ByVal Target As Range) Dim recipient As String Dim outlookApp As Object Dim mailItem As Object If Intersect(Target, Me.Range("BD2")) Is Nothing Then Exit Sub If IsError(Me.Range("BD2").Value) Then Exit Sub On Error GoTo SendError Application.EnableEvents = False If LCase$(Trim$(CStr(Me.Range("BD2").Value))) <> "yes" Then Me.Range("BE2").ClearContents GoTo CleanUp End If If Me.Range("BE2").Value = "Sent" Then GoTo CleanUp recipient = "recipient@example.com" If recipient = "recipient@example.com" Or InStr(1, recipient, "@") = 0 Then MsgBox "Set a valid recipient address in the macro." GoTo CleanUp End If Set outlookApp = CreateObject("Outlook.Application") Set mailItem = outlookApp.CreateItem(0) With mailItem .To = recipient .Subject = "Excel notification" .Body = "Cell BD2 has been set to Yes." .Send End With Me.Range("BE2").Value = "Sent" GoTo CleanUp SendError: MsgBox "The email could not be sent: " & Err.Description CleanUp: Application.EnableEvents = True End Sub

3
Save and test

Save the workbook as .xlsm, enable macros if prompted, and enter Yes in BD2. Check Outlook Sent Items and confirm that BE2 shows Sent.

Use a worksheet event to send an Outlook email
Control repeat messages: While BE2 contains Sent, another edit to Yes will not send a duplicate. Changing BD2 to a different value clears the flag, allowing a new message the next time it is set to Yes. The address check is basic; verify the recipient before testing.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Microsoft Office formats, making it easy to move everyday spreadsheet work. It does not fix Excel VBA or Outlook security restrictions.

Review and edit spreadsheet data in a familiar interfaceOpen and save common Microsoft Excel formats, including XLSXUse a free, lightweight suite for everyday office tasksMove existing Office documents into a familiar workspace
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel mailto hyperlink send an email automatically?

No. A mailto hyperlink normally opens a draft in the default email application. Use VBA, Power Automate, or another automation tool to send without manually clicking Send.

Will the worksheet macro run if a formula changes BD2 to Yes?

No. Worksheet_Change responds to edits, not formula recalculation. For a formula-driven value, use a carefully designed Worksheet_Calculate event or another automation approach.

How do I stop the VBA macro from sending the same notification repeatedly?

The example writes Sent to BE2 after sending and checks that flag before sending again. Change BD2 to a value other than Yes to clear the flag for a future notification.

Why does Outlook ask for permission or block the email?

Outlook security settings or organization policies can restrict programmatic sending. Check any Outlook prompt and contact your administrator if a policy prevents the macro from sending.

Does the VBA macro work when the workbook is closed?

No. The worksheet event runs in desktop Excel while the workbook is open and macros are enabled. For an online workbook, consider a Power Automate flow that checks an Excel table.