Email Notification after table is updated

[user8314]

I am currently using the vba code below to send an email update after a change in each cell, however i would like the email update to be sent after the workbook is saved, rather than get alerted after any cell is changed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nConfirmation As Integer
Dim objNewWorkbook As Excel.Workbook
Dim objNewWorksheet As Excel.Worksheet
Dim objOutlookApp As Object
Dim objMail As Object

nConfirmation = MsgBox("Do you want to send an email notification about the sheet updating now?", vbInformation + vbYesNo, "Mail Sheet Updates")

If nConfirmation = vbYes Then
ActiveWorkbook.Save

On Error Resume Next
Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)

'Change the email details as per your needs
With objMail
.To = "test@Email.com"
.Subject = "Email Notifying Sheet Updates"
.Body = "Hi," & vbCrLf & vbCrLf & "The worksheet " & Chr(34) & ActiveWorkbook.Sheets(1).Name & Chr(34) & " in this Excel workbook attachment is updated."
'Attach this workbook
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
End If
End Sub

 

[Jeffrey Mahoney]

How I would do it is this: Put all that code into a SUB called SendEmailConfirmation. Add these two subs in the THISWORKBOOK module

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  If Worksheets("Setup").Range("CellChange").Value = True Then
    Worksheets("Setup").Range("CellChange").Value = False
    CellChanges = False
    SendEmailConfirmation
  End If
  
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If CellChanges = False Then
    CellChanges = True
    Worksheets("Setup").Range("CellChange").Value = True
  End If
End Sub

You'll need to create a Sheet Called "Setup" and make a named range called "CellChange". This saves the status if a cell has changed.
You'll need to the code below to a standard module. This remembers the value of the CellChange as long as the workbook is open or until you reset the VBAproject. This way the "CellChange" doesn't get updated every time a cell changes.

Public CellChanges As Boolean

As soon as the user saves the workbook an email will get sent.

 

[user8314]

Putting all code how you explained returns an error, and fills the cell range with "TRUE"

 

[Jeffrey Mahoney]

You're going to have to put the SendEmailConfirmation SUB in a standard module