• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automate Excel Sheet to PDF and Send PDF as an Attachment in Outlook


New Member

I have a MACRO I'm working on that is supposed to convert specific sheets in a workbook to a PDF file and attach the PDF in an outlook mail to send out to managers. The sample code is below. I'm able to create the PDF file with no issue but the MACRO can't find the same file path and name used in order to attach to the email. Can anyone help me?

>>> use code - tags <<<
Sub SendPrelimTest()
    Dim answer As VbMsgBoxResult
    Dim FilePath As String
    Dim PrelimName As String
    Dim BaseFileName As String
    Dim FileNameArray() As String
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim myAttachment As Object
    Dim SendTo As Variant
    Dim AttachmentPath As String
    Set OutLookApp = CreateObject("Outlook.Application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    Set myAttachment = OutLookMailItem.Attachments
    answer = MsgBox("Are you ready to send preliminary report?", vbYesNo + vbQuestion)
    If answer = vbYes Then
        FilePath = ThisWorkbook.Path & "\"
        FileNameArray = Split(ThisWorkbook.Name, ".")
        BaseFileName = FileNameArray(0)
        PrelimName = "Preliminary " & BaseFileName
        AttachmentPath = FilePath & PrelimName
        Sheets(Array("Break Even", "Summary - Preliminary")).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=AttachmentPath, _
        MsgBox "PDF file has been created: " & AttachmentPath & vbNewLine _
        & "Please look over File and make changes if needed."
    End If
    Sheets("Analyst Use").Select

    SendTo = ActiveSheet.Range("D1").Value
    With OutLookMailItem
    .To = SendTo
    .Subject = PrelimName
    myAttachment.Add AttachmentPath
    End With
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
End Sub
Last edited by a moderator:


Excel Ninja
Have You verified value of AttachmentPath then You Add it?
Do it have same value as You created PDF?
How do it ends? ... with .pdf?

You have: Set myAttachment = OutLookMailItem.Attachments

An later You're using
With OutLookMailItem
myAttachment.Add AttachmentPath
instead of
.Attachments.Add AttachmentPath
... have You copy & pasted Your code somewhere?

Do Your code give any error messages?


New Member
Yes I verified the value of the AttachmentPath as the same string that I used to make the PDF. I still get the same error when using .Attachments.Add AttachmentPath. I Keep getting the same error message "Run-time error '-2147024894 Cannot find this file. Verify the path and file name are correct."
I found the problem though. I didn't add the ".pdf" extension to the name for use