VBA - Send an email with PDF file attached (exporting active sheet as PDF)

[GuillaumeC]

Hi everyone,

Using a code from Internet, I have been trying to send an email with a PDF file attached. On Microsoft Office 365, while running the macro it works just fine. However, on former version of Microsoft Office, I get the following message 'execution error: -2147024894 (80070002)', popping-up from time to time. Could someone help me figuring out what to change in my code? Thank you in advance. PS. Find below my VBA codes.





Sub Macro_Send_Facture_An()


'MACRO OBJECT: SEND AN EMAIL TO A SPECIFIC CLIENT WITH AN ANNUAL INVOICE ATTACHED

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 = Left(Range("b24"), 5) & " _ " & Range("b17") & " " & Range("c17")

' Define PDF filename
PdfFile = Range("A1") & ".pdf"

' Export activesheet as PDF
With ActiveSheet
.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 = Range("E27")
.CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Bonjour," & vbLf & vbLf _
& "Veuillez trouver ci-joint votre facture CeREN pour l'année " & Format(Sheets("FACTURE AN").Range("b3")) & "." & vbLf & vbLf _
& "Cordialement," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile
.Display 'or use .Send

End With

' Delete PDF file
Kill PdfFile

' 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

 

[John_w]

Does A1 on the active sheet contain the folder path and file name, excluding the .pdf extension, of the PDF file?

If not, change this line:

PdfFile = Range("A1") & ".pdf"

to include a specific folder path, for example:

PdfFile = ThisWorkbook.Path & "\" & Range("A1") & ".pdf"

PS please use VBA code tags

 

[GuillaumeC]

Hi John,

Indeed, cell A1 didn't contain the folder path. With your line of code, it works just fine now. Thank you very much for your help. I really appreciate!
(Well noted as to VBA code tags)