Consulting

Results 1 to 6 of 6

Thread: email excel update using VBA

  1. #1

    Question 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?

    Many Thanks

    Mark
    Attached Files Attached Files

  2. #2
    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

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

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

  5. #5
    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

  6. #6
    Hi Graham

    Sorted, many thanks for your help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •