email excel update using VBA

[Mark Smith]

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

[gmayor]

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


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

[Mark Smith]

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

[Mark Smith]

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

[gmayor]

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.

[Mark Smith]

Hi Graham

Sorted, many thanks for your help :clap: