Multiple cell change email notifications

[Sven62]

The VBA bellow will send an email to a specific person when the given cell range is changed. I need to make it email different people for different ranges. So if C2:C50 changes, then certain person(s) including CC and BCC gets an email. If D2:D50 changes, then a different person(s) get an email, including CC and BCC. I tried simply adding a new Private Sub with a new name ("Worksheet_Change2") and different cell range and recipient list... that didn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("D2:D50")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
        xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
            " in the worksheet '" & Me.Name & "' were modified on " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " by " & Environ$("username") & "."

        With xMailItem
            .To = "xxxxx@xxxxxx.com"
            .Subject = "Worksheet modified in " & ThisWorkbook.FullName
            .Body = xMailBody
            '.Attachments.Add (ThisWorkbook.FullName)
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

 

[Sven62]

You no longer have "xRgSel" defined in your code, but you are trying to reference it.
You actually don't need it. Just replace "xRgSel" with "Target".

I am also unclear what "Me.Name" is supposed to represent. That may not be necessary either, or might need some editing.

Replacing xRegSel with Target fixed it. Me.Name makes it insert the name of the worksheet in the body text message. That maybe antiquated and there might be a better way to reference it, but it does work. Thank you!

 

[Joe4]

Excellent!
You are welcome.
Glad we were able to get it working!