Emailing from excel to an address based on a cell selection

[Adsman21]

Hi Everyone,

First time poster and apologies if it's wrong however i'm looking for abit of help. I'm looking to send an email via excel which works but I want it to be sent to a different email address based a the escalation type in cell b7.

I can send it to one person but there are 3 different areas and I don't want it to go to all of them if it's not for them.

Any help is great.

Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False


If [Approved] = "Approved" Then
        MsgBox "Approved"
        ' Select the range of cells on the active worksheet.
   ActiveSheet.Range("B8:C20").Select
   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
  
   With ActiveSheet.MailEnvelope
      .Introduction = "Escalation"
      .Item.To = "email@email.com"
      .Item.Subject = "Escalation For " & Range("c8").Value
      .Item.Send
   End With
    Else
       MsgBox "Declined: Sorry but this doesn't meet our criteria."
   End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Thanks

 

[daverunt]

Hi,

I think I know what you mean.....

have a look here:

https://www.mrexcel.com/forum/excel-questions/680327-macro-send-email-through-outlook.html

This uses Select Case to decide what message to use in the body.
You could equally apply it to whatever is found in B7 and mail to a single contact or multiple.

 Select Case Range("B7").Value
 
 Case Is = "escalationTypeA"
   
   MailTo = "test1@yahoo.com; test2@yahoo.com"
 
 Case Is = "escalationTypeB"
 
   MailTo = "test3@yahoo.com"
    
 Case Is = "escalationTypeC"
 
    MailTo = "test4@yahoo.com"
    
 Case Else
    '"???"
 End Select