Hi All, I would like an email sending when the content of any cell in a range of cells change. I found a VBA script (I have no VBA experience) on a website and that does the first part, when a cell's content changes it open an email with the message saying which cell has changed.
However, I then have to click send, when I actually want it to go without intervention. I also need it to go independent of someone having Outlook open, so if someone else changes the content, then it is generated and sent from their Outlook.
The VBA I am using which as I say opens up an email is below. Any help would be appreciated.
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("AA3:AD100") 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 = " firstname.lastname@example.org" .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 End Sub