sanantonio
Board Regular
- Joined
- Oct 26, 2021
- Messages
- 124
- Office Version
- 365
- Platform
- Windows
Whereabouts in the below code is this macro deciding where it's saving this PDF? I ask because I've used this macro is numerous reports over the years and it's always functioned without question. But in it's latest iteration it seems to save the pdf in some weird and wonderful places (seemingly at random) and then debugs at the " .Attachments.Add PdfFile" line.
S2 contains only the filename, in this case: "Report_Wk1"
S2 contains only the filename, in this case: "Report_Wk1"
VBA Code:
Sub email()
'
' email Macro
'
If MsgBox("This will automatically generate and send the PDF tab via email, are you sure you wish to proceed?", vbYesNo) = vbNo Then Exit Sub
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
' Not sure for what the Title is
Title = Range("S2")
' Define PDF filename
PdfFile = Range("S2")
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ".pdf"
' Export activesheet as PDF
With Worksheets("PDF")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0
' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)
' Prepare e-mail
.Subject = Title
.To = "xxx@xxx.com" ' <-- Put email of the recipient here
.CC = "xxx@xxx.com" ' <-- Put email of 'copy to' recipient here
.Body = "Hi," & vbLf & vbLf _
& "Please find attached latest Report." & vbLf & vbLf _
& "Regards eLWIS Team" & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile
' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0
End With
On Error Resume Next
' Delete PDF file
Kill PdfFile
On Error Resume Next
' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit
' Release the memory of object variable
Set OutlApp = Nothing
'
End Sub