VBA creates PDF attachment & saves but does not generate the email and attach

%3CLINGO-SUB%20id%3D%22lingo-sub-2079697%22%20slang%3D%22en-US%22%3EVBA%20creates%20PDF%20attachment%20%26amp%3B%20saves%20but%20does%20not%20generate%20the%20email%20and%20attach%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079697%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI%20have%20used%20the%20following%20VBA%20to%20create%20a%20PDF%20attachment%20and%20email%20to%20a%20group%20of%20recipients.%20It%20is%20creating%20the%20PDF%20in%20the%20same%20folder%20as%20where%20the%20Excel%20file%20is%20but%20it%20does%20not%20create%20the%20email%20or%20attach.%20The%20debug%20highlights%20the%20.Attachments.Add%20PdfFile%20as%20the%20issue.%20I%20am%20not%20all%20that%20familiar%20with%20VBAs%20and%20it%20has%20worked%20in%20the%20paste.%20Any%20ideas%20on%20what%20the%20issue%20is%20or%20how%20to%20fix%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20This%20is%20my%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20EmailPDF()%3CBR%20%2F%3EDim%20AttachActiveSheetPDF()%3CBR%20%2F%3EDim%20IsCreated%20As%20Boolean%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3EDim%20PdfFile%20As%20String%2C%20Title%20As%20String%3CBR%20%2F%3EDim%20OutlApp%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Not%20sure%20for%20what%20the%20Title%20is%3CBR%20%2F%3ETitle%20%3D%20Range(%22P20%22)%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Define%20PDF%20filename%3CBR%20%2F%3EPdfFile%20%3D%20Title%3CBR%20%2F%3Ei%20%3D%20InStrRev(PdfFile%2C%20%22.%22)%3CBR%20%2F%3EIf%20i%20%26gt%3B%201%20Then%20PdfFile%20%3D%20Left(PdfFile%2C%20i%20-%201)%3CBR%20%2F%3EPdfFile%20%3D%20PdfFile%20%26amp%3B%20%22_%22%20%26amp%3B%20ActiveSheet.Name%20%26amp%3B%20%22.pdf%22%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Export%20activesheet%20as%20PDF%3CBR%20%2F%3EWith%20ActiveSheet%3CBR%20%2F%3E.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20Filename%3A%3DPdfFile%2C%20Quality%3A%3DxlQualityStandard%2C%20IncludeDocProperties%3A%3DTrue%2C%20IgnorePrintAreas%3A%3DFalse%2C%20OpenAfterPublish%3A%3DFalse%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Use%20already%20open%20Outlook%20if%20possible%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20OutlApp%20%3D%20GetObject(%2C%20%22Outlook.Application%22)%3CBR%20%2F%3EIf%20Err%20Then%3CBR%20%2F%3ESet%20OutlApp%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3EIsCreated%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EOutlApp.Visible%20%3D%20True%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Prepare%20e-mail%20with%20PDF%20attachment%3CBR%20%2F%3EWith%20OutlApp.CreateItem(0)%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Prepare%20e-mail%3CBR%20%2F%3E.Subject%20%3D%20Title%3CBR%20%2F%3E.To%20%3D%20Range(%22P21%22).Value%3CBR%20%2F%3E.Body%20%3D%20%22Please%20see%20attached%20evaluation.%22%20%26amp%3B%20vbLf%20%26amp%3B%20vbLf%20_%3CBR%20%2F%3E%26amp%3B%20%22The%20report%20is%20attached%20in%20PDF%20format.%22%20%26amp%3B%20vbLf%20%26amp%3B%20vbLf%20_%3CBR%20%2F%3E%26amp%3B%20%22Regards%2C%22%20%26amp%3B%20vbLf%20_%3CBR%20%2F%3E%26amp%3B%20Application.UserName%20%26amp%3B%20vbLf%20%26amp%3B%20vbLf%3CBR%20%2F%3E.Attachments.Add%20PdfFile%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Try%20to%20send%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3E.Send%3CBR%20%2F%3EApplication.Visible%20%3D%20True%3CBR%20%2F%3EIf%20Err%20Then%3CBR%20%2F%3EMsgBox%20%22E-mail%20was%20not%20sent%22%2C%20vbExclamation%3CBR%20%2F%3EElse%3CBR%20%2F%3EMsgBox%20%22E-mail%20successfully%20sent%22%2C%20vbInformation%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Delete%20PDF%20file%3CBR%20%2F%3EKill%20PdfFile%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Quit%20Outlook%20if%20it%20was%20created%20by%20this%20code%3CBR%20%2F%3EIf%20IsCreated%20Then%20OutlApp.Quit%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Release%20the%20memory%20of%20object%20variable%3CBR%20%2F%3ESet%20OutlApp%20%3D%20Nothing%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2079697%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2079964%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20creates%20PDF%20attachment%20%26amp%3B%20saves%20but%20does%20not%20generate%20the%20email%20and%20attach%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F936506%22%20target%3D%22_blank%22%3E%40JoeyS-1701%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECell%20P20%20should%20contain%20the%20path%20and%20filename.%20The%20code%20will%20add%20the%20sheet%20name%20to%20it%2C%20plus%20the%20extension%20.pdf.%3C%2FP%3E%0A%3CP%3EIf%20P20%20does%20not%20contain%20the%20path%2C%20you%20should%20add%20it%20in%20the%20code%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPdfFile%20%3D%20ThisWorkbook.Path%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Title%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20-%20at%20least%20temporarily%20-%20change%20.Send%20to%20.Display%3C%2FP%3E%0A%3CP%3EOutlook%20should%20then%20display%20the%20email%20message%20for%20you%20to%20inspect.%20You%20can%20click%20Send%20manually.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

   I have used the following VBA to create a PDF attachment and email to a group of recipients. It is creating the PDF in the same folder as where the Excel file is but it does not create the email or attach. The debug highlights the .Attachments.Add PdfFile as the issue. I am not all that familiar with VBAs and it has worked in the paste. Any ideas on what the issue is or how to fix? 

 

    This is my VBA.

 

Sub EmailPDF()
Dim AttachActiveSheetPDF()
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("P20")

' Define PDF filename
PdfFile = Title
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".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("P21").Value
.Body = "Please see attached evaluation." & vbLf & vbLf _
& "The report is attached in PDF format." & vbLf & vbLf _
& "Regards," & 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

' 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

 

2 Replies

@JoeyS-1701 

Cell P20 should contain the path and filename. The code will add the sheet name to it, plus the extension .pdf.

If P20 does not contain the path, you should add it in the code:

 

PdfFile = ThisWorkbook.Path & "\" & Title

 

I'd - at least temporarily - change .Send to .Display

Outlook should then display the email message for you to inspect. You can click Send manually.

@Hans Vogelaar 

 

Thank you. That does generate an email that is displayed, with the PDF attached, for review and can be sent. Much appreciated.