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

 

[Joe4]

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.

You cannot do that.
Event procedure code (VBA code that runs automatically based on some "event" happening) has very strict rules. One is that the procedure has to be named a certain way, and CANNOT be changed at all.

You need to have all those calls in a single "Worksheet_Change" event procedure code.
There are a few different ways of doing that, such as:
1. Simply having different sections of block in your code, i.e. the first pertaining to range C2:C50, the next referring to D2:D50.
2. Only having one block of code, but have the "CC" and "BC" files looked up from some list you have stored somewhere (so that those are dynamic).
3. Create other sub-procedures that do what you want (i.e. one for each section), and then simply have your "Worksheet_Change" event procedure code call the appropriate one, i.e.
if in range C2:C50, call Macro1 to run, if in range D2:D50, call Macro2 to run, etc.

Personally, I would probably try to implement option 2. I do not like repeating sections of code that are virtually all the same, if just one or two variables change. I will just make those variable dynamic, and calculate what they need to be based on the conditions.

 

[Sven62]

You cannot do that.
Event procedure code (VBA code that runs automatically based on some "event" happening) has very strict rules. One is that the procedure has to be named a certain way, and CANNOT be changed at all.

You need to have all those calls in a single "Worksheet_Change" event procedure code.
There are a few different ways of doing that, such as:
1. Simply having different sections of block in your code, i.e. the first pertaining to range C2:C50, the next referring to D2:D50.
2. Only having one block of code, but have the "CC" and "BC" files looked up from some list you have stored somewhere (so that those are dynamic).
3. Create other sub-procedures that do what you want (i.e. one for each section), and then simply have your "Worksheet_Change" event procedure code call the appropriate one, i.e.
if in range C2:C50, call Macro1 to run, if in range D2:D50, call Macro2 to run, etc.

Personally, I would probably try to implement option 2. I do not like repeating sections of code that are virtually all the same, if just one or two variables change. I will just make those variable dynamic, and calculate what they need to be based on the conditions.

Thank you so much. I think - even though it's clunky - the easiest for me would be Option #1. There will only be a few people involved. Could you give me an example of how to do Option #1? I should be able to figure out from that how to add a couple more.

 

[Joe4]

Actually, option 2 should be easier. Based on what cells were updated, we are going to set our values of "cc" and "bcc" to send the emails to.
Here is how that code might be structured.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cc As String
    Dim bcc As String

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Determine which of three ranges have been updated, and set mail to people
'   First check range C2:C50
    If Not Intersect(Target, Range("C2:C50")) Is Nothing Then
        cc = "Person1 email address"
        bcc = "Person2 email address"
    Else
'       If not found, then check range D2:50
        If Not Intersect(Target, Range("D2:D50")) Is Nothing Then
            cc = "Person3 email address"
            bcc = "Person4 email address"
        Else
'           If not found, then check range D2:50
            If Not Intersect(Target, Range("E2:E50")) Is Nothing Then
                cc = "Person5 email address"
                bcc = "Person6 email address"
            Else
'               No changes in any watched ranges, so exit
                Exit Sub
            End If
        End If
    End If
   
'   Code below to send email to users in "cc" and "bcc" variables
    ...

End Sub

You would just replace each "PersonX email address" with the email address you want to use in that situation, and add your code to create and send the email at the bottom where I placed the space holder.

 

[Sven62]

Actually, option 2 should be easier. Based on what cells were updated, we are going to set our values of "cc" and "bcc" to send the emails to.
Here is how that code might be structured.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cc As String
    Dim bcc As String

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Determine which of three ranges have been updated, and set mail to people
'   First check range C2:C50
    If Not Intersect(Target, Range("C2:C50")) Is Nothing Then
        cc = "Person1 email address"
        bcc = "Person2 email address"
    Else
'       If not found, then check range D2:50
        If Not Intersect(Target, Range("D2:D50")) Is Nothing Then
            cc = "Person3 email address"
            bcc = "Person4 email address"
        Else
'           If not found, then check range D2:50
            If Not Intersect(Target, Range("E2:E50")) Is Nothing Then
                cc = "Person5 email address"
                bcc = "Person6 email address"
            Else
'               No changes in any watched ranges, so exit
                Exit Sub
            End If
        End If
    End If
  
'   Code below to send email to users in "cc" and "bcc" variables
    ...

End Sub

You would just replace each "PersonX email address" with the email address you want to use in that situation, and add your code to create and send the email at the bottom where I placed the space holder.

You are a great help and I'm trying here. I'm just not good at VBA. Here is what I have added to make it email.. and it ain't working.

Private Sub Worksheet_Change(ByVal Target As Range)
' I changed CC and BCC to M1 and CC
    Dim M1 As String
    Dim CC As String
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Determine which of three ranges have been updated, and set mail to people
'   First check range C2:C50
    If Not Intersect(Target, Range("C2:C50")) Is Nothing Then
        M1 = "xxxxx@xxxxx.com"
        CC = "xxxxx@xxxxx.com"
    Else
'       If not found, then check range D2:50
        If Not Intersect(Target, Range("D2:D50")) Is Nothing Then
            M1 = "xxxxx@xxxxx.com"
            CC = "xxxxx@xxxxx.com"
        Else
'           If not found, then check range E2:E50
            If Not Intersect(Target, Range("E2:E50")) Is Nothing Then
                M1 = "Person5 email address"
                CC = "Person6 email address"
            Else
'               No changes in any watched ranges, so exit
                Exit Sub
            End If
        End If
    End If
  
'   Code below to send email to users in "cc" and "bcc" variables
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 = M1
            .CC = CC
            .Subject = "Worksheet modified in " & ThisWorkbook.FullName
            .Body = xMailBody
            .Attachments.Add (ThisWorkbook.FullName)
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

OK, what exactly are you doing to trigger the code to run?

 

[Sven62]

OK, what exactly are you doing to trigger the code to run?

I am changing the value in a cell in the given range. Basically the cell will be empty and someone will type a word in or a numerical value, anything change really, and then Outlook will open with the email completed ready to send to the appropriate person. The original code I posted does work.

 

[Joe4]

OK, please walk me through a specific example that is not working, so I can try to recreate it here.
1. What cell exactly are you manually updating?
2. What exactly are you entering into that cell?
3. What happens exactly? Are you getting some sort of error message (if so, what is it?), or are does it just appear that nothing is happening?

 

[Sven62]

This is based on the modified code I last posted that I said wasn't working
1. I am making and entry into Cell C2
2. I am typing a word... any word.. or any number... or any sentence... doesn't matter, the sub just needs to recognize that something was typed in there and create the appropriate email
3. I am getting an error message and the DEBUG shows the highlighted section

 

[Joe4]

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.