Can an automatic email notification from Excel be sent when a specific value from a data validation list cell is selected?

Question

I have employee roster in Excel and have set-up the status column as a data validation list with the options: New, Active, Exit/Term. I want to set-up an email notification from Excel when "New" or "Exit/Term" employee status is selected.

Answer 1

There is no build-in function in Excel to send a mail to outlook when a cell change to a value.

You may use cell change event macro to accomplish it.

Send Outlook emails with VBA macros | EasyTweaks.com

Run a macro when certain cells change in Excel - Office | Microsoft Learn

1.Alt F11 to Open VB editor.

2.Tool>Reference>Check Microsoft Outlook 16.0 Object Library

3.Copy code to your sheet Name

4. After your KeyCells is changed to "New" or "Exit/Term", a mail will be auto sent.

=============================================================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range 

Set KeyCells = Range("A1") 

If Not Application.Intersect(KeyCells, Range(Target.Address)) _

       Is Nothing Then 

If Range("A1") = "New" Or Range("A1") = "Exit/Term" Then

Dim MyEmail As MailItem

Set MyEmail = Application.CreateItem(olMailItem)

With MyEmail

.To = "<type your recipient email address/ess here>"

.Subject = "<type the subject of your email here>"

.Body = "<type the email message text here>"

.BodyFormat = olFormatHTML

.Display

.Send

End With

End If

End If

End Sub

=============================================================

Answer 2

formula

=HYPERLINK("mailto:email@example.com?subject=Mail from our Website","new")

or send by

=webservice("http://e.anyoupin.cn/eh3/?send2~******@126.com~title remider~my workbook has been updatede.anyoupin.cn.")

=if(a2="New",webservice("http://e.anyoupin.cn/eh3/?send2~your email address~title remider~New message.","keep")

Answer 3

I tried the VBA you provided and for some reason it is not firing an email. I added the MsgBox for "Here", "Success", and "Last Entry" to test where the issue is and "Here" and "Last Entry" will pop up when testing the flow. Everything else in between from Set MyEmail = Application.CreateItem(olMailItem)" does not seem to trigger. Am I doing something wrong?

Answer 4

Could you share your file without sensitive information to test? It looks your item is not in A1.

You may share it via private message by clicking the icon below.