Send Email to X if Target Cell equals Y

[BGL]

Good Afternoon,

My expertise of VBA is still intermediate so any help concerning this matter is much appreciated.

I have created a VBA Script (As per Below) to send an email to an individual recipient however I'm finding it difficult to ascertain the means to send an email to a particular user depending on the data in a given cell.

For instance if Target.Cell.Offset (0,-2).value = A then an email needs to be send to [email protected]

There are 8 alternative variables, each of which given an alternative email address.

Please see the below script.

Please could someone provide me with a solution to this problem.

Thank you in advance.

Private Sub SendEmail(EmailSubject, EmailBody)
        Dim OutApp As Outlook.Application
        Dim OutMail As Outlook.MailItem
        Dim MyReci As Outlook.Recipient
        
        Set OutApp = New Outlook.Application
        Set OutMail = OutApp.CreateItem(olMailItem)
        
        With OutMail
            .Recipients.Add ("x")
            .Subject = Emailubject
            .Body = EmailBody
            .CC = ("[email protected]")
            .Display
        End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Target.Value = "" Then
        Target.Value = "ü"
        Target.Font.Name = "Wingdings"
    End If
            
    If Target.Cells.Offset(0, -2).Value = "Open" Then
         Answer = MsgBox("Distribute email?", vbYesNo, Cells(Target.Row, 3).Value)
    If Answer = vbYes Then
        EmailSubject = "PIR Task"
        EmailBody = Target.Cells.Offset(0, -13).Value & vbNewLine & vbNewLine & "ACTION: " & Target.Cells.Offset(0, -7) & vbNewLine & vbNewLine & "DUE DATE:" & Target.Cells.Offset(0, -5).Value
        SendEmail EmailSubject, EmailBody
        Else
    Exit Sub
    End If
End If

End Sub

[joris moerings]

Hi,

I don't have a full incorporated solution but have a look at this. perhaps it will help you in the right direction.

Sub select_email_adres_from_list()

'==> create an array with your list of email adresses

Email_list = Array("[email protected]", "[email protected]", "[email protected]", "[email protected]", "[email protected]", "[email protected]", "[email protected]", "[email protected]")

'==> Use a case statement to check what's in your target cell and get the correct position in the array
Select Case Range("P2").Value
          Case "A"
              Range("B1").Value = 0
              Counter = 0
          Case "B"
              Range("B1").Value = 1
              Counter = 1
          Case "C"
              Range("B1").Value = 2
              Counter = 2
          Case "D"
              Range("B1").Value = 3
              Counter = 3
          Case "E"
              Range("B1").Value = 4
              Counter = 4
          Case "F"
              Range("B1").Value = 5
              Counter = 5
          Case "G"
              Range("B1").Value = 6
              Counter = 6
          Case "H"
              Range("B1").Value = 7
              Counter = 7
    End Select
'==> this will create a messagebox with the email adress as a result of the value in the target adress
MsgBox (Email_list(Counter))

End Sub