Hi all!
I'm looking to use VBA to save a spreadsheet as a PDF and then automatically attach the PDF to a new Outlook email. The code below works fine for this, however only if the file is saved in the default location when the 'Save As' window pops up. If the user changes the folder into which the folder is saved, the code can no longer find the file and fails to open a new email.
If anyone knows a solution to this - i.e. a code that finds the file that was just saved under the specific name and attaches this to the email, that would be massively appreciated!
'=========================================
'CREATE AND SAVE PDF
'=========================================
Dim wb As Workbook
Dim ws As Worksheet
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Set wb = ActiveWorkbook
Set ws = TermSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")
'get active workbook folder, if saved
strPath = wb.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & ""
'name PDF file (strFile) - "Client, Issue, Date_Time"
strName = Range("B4")
strFile = strName & ", " & strTime & ".pdf"
strPathFile = strPath & strFile
'enter name and select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
'export to PDF if a folder was selected
If myFile <> "False" Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
'=========================================
'OPEN OUTLOOK EMAIL AND ATTACH PDF
'=========================================
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Open new Outlook email
With OutMail
.To = Range("C12") & "; " & Range("C13") & "; " & Range("C14") & "; " _
& Range("C15") & "; " & Range("C16")
.Subject = "TEST EMAIL" & " " & Range("B4")
.Body = "Test email - please ignore"
.Attachments.Add strPathFile
.Display
End With
I'm looking to use VBA to save a spreadsheet as a PDF and then automatically attach the PDF to a new Outlook email. The code below works fine for this, however only if the file is saved in the default location when the 'Save As' window pops up. If the user changes the folder into which the folder is saved, the code can no longer find the file and fails to open a new email.
If anyone knows a solution to this - i.e. a code that finds the file that was just saved under the specific name and attaches this to the email, that would be massively appreciated!
'=========================================
'CREATE AND SAVE PDF
'=========================================
Dim wb As Workbook
Dim ws As Worksheet
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Set wb = ActiveWorkbook
Set ws = TermSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")
'get active workbook folder, if saved
strPath = wb.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & ""
'name PDF file (strFile) - "Client, Issue, Date_Time"
strName = Range("B4")
strFile = strName & ", " & strTime & ".pdf"
strPathFile = strPath & strFile
'enter name and select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
'export to PDF if a folder was selected
If myFile <> "False" Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
'=========================================
'OPEN OUTLOOK EMAIL AND ATTACH PDF
'=========================================
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Open new Outlook email
With OutMail
.To = Range("C12") & "; " & Range("C13") & "; " & Range("C14") & "; " _
& Range("C15") & "; " & Range("C16")
.Subject = "TEST EMAIL" & " " & Range("B4")
.Body = "Test email - please ignore"
.Attachments.Add strPathFile
.Display
End With
