VBA macro

[Rufes]

Hello!
I have a question. I am a PeoplePlanner. The teams have to take inventory when they want to have a day off. They do this by putting the letter V in a certain cell. Sometimes 15 changes have to be made and then 15 emails are generated (and this for 60 people) Then it is clear to me that someone is free. However, with the code below, a new email is always generated when a change is made. Is it possible after making various changes and the worksheet is saved that an email is generated? So not with every change, but one email containing the changes (such as cell A1 is changed, cell A2 is changed and so on) I have already tried several things like

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

but unfortunately this doesn't work. I would like to know if this is possible and with what code. Thanks in advance!

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("A1:AA100")
    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 = "email"
            .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

 

[Colo]

Welcome to MrExcel!

As you explained, every time there is a change in the cell value, an email is generated. All you have to think about with the Worksheet Change event is creating an update history when the cell value has changed, instead. Then using the Workbook_AfterSave event, you can send the update history. The update history should be cleared after sending the email.

We'd like to know why the Workbook_AfterSave event didn't work.