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?
Many Thanks
Mark
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?
Many Thanks
Mark
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
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
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.
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
Hi Graham
Sorted, many thanks for your help