Can Excel automatically “SEND” an email via Outlook based on Data Validation in spreadsheet?

Question

I have created a communication platform, whereby it states if a suggested department needs to know about certain information.

Is it possible, that when 'Urgent action' is entered it can automatically send an email to the suggested people.

It would need a different macro per column as it would contain different email addresses.

Please see picture for visual depiction. enter image description here

Thanks for your help in advance!!!

Answer 1

You will find an answer to your question in the article
How to automatically send email based on cell value in Excel?

It contains a VBA macro for dispatching an email, which I'll reproduce below. D7 and value > 200 are the cell and condition, and the email details are in the xMailBody and xOutMail items in the code.

Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

I have not tested this macro, but it looks functional.