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

[fbataille]

Greetings

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
        ThisWorkbook.Save
        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, _
        IncludeDocProperties:=False
        MsgBox "PDF file has been created: " & AttachmentPath & vbNewLine _
        & "Please look over File and make changes if needed."
        Range("E43").Select
    Else
        ThisWorkbook.Save
        Range("E43").Select
    End If
   
    Sheets("Analyst Use").Select

    SendTo = ActiveSheet.Range("D1").Value
    With OutLookMailItem
    .To = SendTo
    .Subject = PrelimName
    myAttachment.Add AttachmentPath
    .Display
    End With
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
    Sheets(4).Select
End Sub

 

[vletm]

fbataille
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?

 

[fbataille]

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

 

[vletm]

fbataille
AttachmentPath do not have .pdf extension then the code could not attach that file - because there is not that file!

 

[fbataille]

fbataille
AttachmentPath do not have .pdf extension then the code could not attach that file - because there is not that file!

thanks