Help with VBA code

[WillemS]

Good day

Can I please ask for help?

I am using the below VBA code to send me a notification as and when a user updates an excel sheet.

It is supposed to send an email every time a user makes a change and or update to the said sheet. It actually does so but then continues to send the very same message over and over even if there are no changes made to the sheet. I disabled the autosave and auto-update on the sheet to see if it will be part of the problem but it did not work.

The data on the sheet get automatically updated from outlook as and when a new email arrives but I even disabled it and it still not solve the problem.

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("A2:E11")
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 = "willem.smith777@gmail.com"
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Send

End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub



Much appreciated
Tx
W

 

[6StringJazzer]

If it is sending the same message (that is, specifying the same cell address) then maybe you have recursion. I don't see where you are changing data in the sheet but generally it is a safe practice to add

Application.EnableEvents = False

at the beginning of an event handler to prevent recursion, then set it back to True at the end.

 

[Logit]

Interesting ... I used your same code here ... no problems.

 

[WillemS]

Cool thank you much appreciated.
Can you please help me with code to move the info on the excel sheet, as part of the above process, after the sending event happened, so that when a new info arrived in the main sheet the old info is already moved to a backup sheet please?

 

[deanop2]

I use this and it works. I'd like to specify the value. I changed the range to just one cell and want the email to send when the value is 3