email excel update using VBA

I am trying to get Excel to email when there is a change made to a spreadsheet but only when saving

The attached code tries to send an email every time a cell change is made rather than when saving.

Can anyone help with this?
:banghead:
Many Thanks

Mark

You could use instead the AfterSave event using the bulk of your code e.g.

Code:

---

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim nConfirmation As Integer
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
        On Error Resume Next
        Set objOutlookApp = GetObject(, "Outlook.Application")
        If Err <> 0 Then
            Set objOutlookApp = CreateObject("Outlook.Application")
            bStarted = True
        End If
        On Error GoTo 0

        Set objMail = objOutlookApp.CreateItem(0)

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

---

Thanks for that but still does not ask to or send an email...

When you say the bulk of the code should I be taking something out?

If the code posted is in the ThisWorkbook module of the workbook in question, it should prompt to send the message when you save the workbook. The workbook must be saved as macro enabled. The bStarted = True line should have been removed from the code as it will cause an error condition.

Hi Graham

Sorted, many thanks for your help :clap: