Hello,
I used to do VBA coding 30+ years ago, but it has changed dramatically over the years.
I need to send an email notification to different email addresses based on which range of cells has been changed.
Example:
If range J4:NJ4 is changed, send email to "resource1@email.com"
If range J5:NJ5 is changed, send email to "resource2@email.com"
If range J6:NJ6 is changed, send email to "resource3@email.com"
I found some code online that was close to what I wanted. I tried to tweak it to do what I need above, but am clearly out of my depth. The code I attempted is below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
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("J4:NJ9")
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
If xRgSel Is Range("J4:NJ4") Then
Set .To = "resource1@email.com"
Set .Subject = "Resource 1 - Your Schedule Has Been Changed"
End If
If xRgSel Is Range("J5:NJ5") Then
Set .To = "resource2@email.com"
Set .Subject = "Resource 2 - Your Schedule Has Been Changed"
End If
.Body = xMailBody
'.Attachments.Add (ThisWorkbook.FullName)
'.Display
.Send
End With
I used to do VBA coding 30+ years ago, but it has changed dramatically over the years.
I need to send an email notification to different email addresses based on which range of cells has been changed.
Example:
If range J4:NJ4 is changed, send email to "resource1@email.com"
If range J5:NJ5 is changed, send email to "resource2@email.com"
If range J6:NJ6 is changed, send email to "resource3@email.com"
I found some code online that was close to what I wanted. I tried to tweak it to do what I need above, but am clearly out of my depth. The code I attempted is below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
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("J4:NJ9")
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
If xRgSel Is Range("J4:NJ4") Then
Set .To = "resource1@email.com"
Set .Subject = "Resource 1 - Your Schedule Has Been Changed"
End If
If xRgSel Is Range("J5:NJ5") Then
Set .To = "resource2@email.com"
Set .Subject = "Resource 2 - Your Schedule Has Been Changed"
End If
.Body = xMailBody
'.Attachments.Add (ThisWorkbook.FullName)
'.Display
.Send
End With