Hello all! - only 3 more days til the weekend!
I have some code where by if the user changes the value of a cell within a dynamic range an email is delivered.
First of all the code below doesnt work!! I thought i was on the right track! -
Second of all, in an ideal world I would like to send only one email when one or more changes have been made to the cells in this dynamic range. I know this is possible but my VBA skills are very limited! Can anyone help here?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim thirdparties As Range
ThisWorkbook.Names.Add Name:="thirdparty", RefersToR1C1:="=OFFSET('SDF Approval Summary'!R1C1,0,0,COUNTA('SDF Approval Summary'!C1)-1,9)"
Set thirdparties = Range("thirdparty")
If Target.Address = thirdparties.Address Then
If MsgBox("Confirm EMail notification??", vbQuestion Or vbYesNo) = vbYes Then
ThisWorkbook.SendMail "[email protected]", "A 3rd Party CCN has been changed"
Else: MsgBox ("Email notification Cancelled!!")
Naturally very appreciative of any help -