Send an Email when a Cell Contains the Desired Text

Hi all, TIA for any help.

I have a cell that contains a drop down with two options; Accept or Reject. When Accept is selected, I would like an Outlook email window to pop up ready to send, and the same for Reject. The emails will only contain different text bodies.

Any help is appreciated.

You've not gone into any detail so I've set the Drop Down box as A1

Can you work with this?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    If Range("A1") = "Accept" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                "Accepted!" & vbNewLine
        strSubject = "Accepted!"
    End If
    
    If Range("A1") = "Reject" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                "Rejected!" & vbNewLine
        strSubject = "Rejected!"
    End If

    If Range("A1") <> "Accept" And Range("A1") <> "Reject" Then Exit Sub

    On Error Resume Next
    With OutMail
        .To = "some.email@address.com"
        .CC = ""
        .BCC = ""
        .Subject = strSubject
        .Body = strbody
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End If

End Sub

Thank you for your help.

The cell that contains the dropdown is G4. However, the table will often add new rows, each of which will have the Accept/Reject drop down cell (G5, G6, G7 etc. etc.). So can the code recognise this or will something need to be changed?

Try;

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    If Cells(Target.Row, Target.Column) = "Accept" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                "Accepted!" & vbNewLine
        strSubject = "Accepted!"
    End If
    
    If Cells(Target.Row, Target.Column) = "Reject" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                "Rejected!" & vbNewLine
        strSubject = "Rejected!"
    End If

    If Cells(Target.Row, Target.Column) <> "Accept" And Cells(Target.Row, Target.Column) <> "Reject" Then Exit Sub

    On Error Resume Next
    With OutMail
        .To = "some.email@address.com"
        .CC = ""
        .BCC = ""
        .Subject = strSubject
        .Body = strbody
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing


End If

End Sub

This is perfect! Thank you very much for your help.

Am I correct in saying that I can use the Target.Offset(Y, X) to pick out values from the row to then use in the email body?

No worries at all, glad to help.

Yes you can do that,

If Target.Column = 7 Then

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    If Cells(Target.Row, Target.Column) = "Accept" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                Target.Offset(0, 1).Value & vbNewLine
        strSubject = "Accepted!"
    End If

Or I'd do it like this if I'm honest to keep it more "readable";

If Target.Column = 7 Then

    ARRow = Target.Row

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    If Cells(Target.Row, Target.Column) = "Accept" Then
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                Cells(ARRow, "H").Value & vbNewLine
        strSubject = "Accepted!"
    End If

The choice is yours, whatever you feel comfortable with like anything else

Brilliant, cheers!