Macro to send Email notification to specific people

I have written a Macro that transfers data from and input sheet into a data collection table. On the click of the button, the data is transferred to a new row, the input sheet it cleared, the workbook is saved and I receive an email notification to say that new information has been added to the sheet.

I would like to know how to get the email notification to go to different people depending on the contents of a cell.

For example,

I have a merged cell (B9:C10) that details from which department action is required (Operations, Engineering or Quality).

How do I adapt my code so if the cell = “Operations” it sends the email to PERSON A

If the cell = “Engineering” it send the email to PERSON B etc etc.

My current email code is as follows:

'Email Send Notification
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "I have added a new turnback into the data sheet." & vbNewLine & vbNewLine & _
              "Please see the following link: " & _
                  """file://" & ActiveWorkbook.FullName & _
                  """" & vbNewLine & vbNewLine & _
              "Some action is required from your department." & vbNewLine & _
              "" & vbNewLine & _
              "Regards"

    On Error Resume Next
    With OutMail
        .To = "EMAIL ADDRESS"
        .CC = ""
        .BCC = ""
        .Subject = "Sheet Updated - Action Required"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

Please help.
Thanks

JDGreen17,

You should simply be able to have a lookup somewhere hidden in your sheet that has the Group and Email address.
When you then select the group, it looks up the email address and passes it to a variable (which we will call strEmail) which you can then use in your code:

Dim strEmail as String
strEmail = Range(CELL ID).Value

 With OutMail
        .To = strEmail
        .CC = ""
        .BCC = ""
        .Subject = "Sheet Updated - Action Required"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With