I'm trying to write code so that my workbook is saved and then attached to an email. If the file already exists, however, and the file is overwritten, I want the script to continue. If the file is not overwritten, I want the script to display a message box, and then continue.
Here is the script I have so far, but I'm doing something wrong. Can someone point out what I need to do to fix it?
'Save File
On Error GoTo Errhandler
ChDir _
"C:\"
ActiveWorkbook.SaveAs Filename:= _
"C:\Confirm " & Format(Now, "mm.dd.yy") & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
On Error GoTo Next
Errhandler:
MsgBox "Before running this script, there was an existing version of the confirm. Please be aware of the version attached to the email."
'Attach to Email
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
'Make any changes to email recipients below
.To = "xyz@xyz.com"
.CC = ""
.BCC = ""
.Subject = "Confirm " & Format(Date, "mm.dd.yy")
.body = "Please see attached for your confirm."
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks so much!
Bookmarks