Excel VBA send an email when the file has been Saved or Saved As

[Biz]

Dear All,

I have macro which works, but it's I wondering why can't I use GoTo aEmail where I highlighted text in Bold Green.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


    Dim varWorkbookName As String
    Dim FileFormatValue As Integer


    On Error GoTo Quit
    Application.EnableEvents = False


    If SaveAsUI = True Then
        varWorkbookName = Application.GetSaveAsFilename(ThisWorkbook.Path & Application.PathSeparator, _
        fileFilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm")
        
        Cancel = True
       


        If varWorkbookName <> "False" Then
            Select Case LCase(Right(varWorkbookName, Len(varWorkbookName) - InStrRev(varWorkbookName, ".", , 1)))
            Case "xlsm": FileFormatValue = 52
            End Select
            
            'Default Directory based on based ThisWorkbook.Path or Selected Directory Path
            ActiveWorkbook.SaveAs varWorkbookName
            '~~> File Save As
[COLOR=#00ff00][B]            Call Email[/B][/COLOR]
             
        End If
     
    End If
'~~> File "Save" or Save As
aEmail: If Not Cancel = True Then Call Email
Quit:


    If Err.Number > 0 Then
        If Err.Number <> 1004 Then


            MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
                "Title", vbCritical


        End If
    End If
    
    
    Application.EnableEvents = True


End Sub


Sub Email()
Dim Outlook As Object, Email As Object


Set Outlook = CreateObject("Outlook.Application")


Set Email = Outlook.CreateItem(0)


With Email
    .To = "Email1@aol.com" '; Email2@aol.com"
    .CC = ""
    .BCC = ""
    .Subject = "Workbook Saved!"
    .Body = "Hello! - the workbook in " & ActiveWorkbook.FullName & " was saved by " & Environ("USERNAME") & " at " & Format(Now(), "ddd dd mmm yy hh:mm")
    '.Attachments.Add ActiveWorkbook.FullName ' To add active Workbook as attachment
    '.Attachments.Add "C:\Test.xlsx" ' To add other files just use path, Excel files, pictures, documents pdf's ect.
    .Send
    '.Display   'or use .Send to skip preview
End With




Set Email = Nothing


Set Outlook = Nothing


End Sub

Your help would be greatly appreciated.

Kind Regards,

Biz

 

[pbornemeier]

Adding the green text below allowed to run for me.

            'Default Directory based on based ThisWorkbook.Path or Selected Directory Path
            ActiveWorkbook.SaveAs varWorkbookName[COLOR="#0000CD"], FileFormat:=FileFormatValue[/COLOR]

You may wish to review: Use VBA SaveAs in Excel 2007-2016
and add code to handle other file extensions.

 

[Biz]

Hi M8,

Thank you for your help.

Kind Regards

Biz