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

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,737
Office Version
  1. 2010
Platform
  1. Windows
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.



Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,903
Adding the green text below allowed to run for me.

Code:
            '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.
 
  • Like
Reactions: Biz
Upvote 0

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,737
Office Version
  1. 2010
Platform
  1. Windows
Hi M8,

Thank you for your help.

Kind Regards

Biz
 
Upvote 0

Forum statistics

Threads
1,186,603
Messages
5,958,759
Members
438,374
Latest member
Nater

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top