File not found error when trying to attach PDF to Email

[qitjch]

Hello,

I am having an issue adding an attachment to an email. This problem was non existent for the past few days and just started today. I've tried everything I can think of and just cannot seem to figure out what the issue could be. Basically, I have some code that exports two worksheets as a PDF. Then, in another module I later run code that adds the exported PDF as an attachment to an email.

PDF Export:

'Export Renewal Letter as PDF

ProposalContract = wsConsolidated.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Value
InstalledAtName = wsConsolidated.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Value



ThisWorkbook.Sheets(Array("Renewal Letter Template", "Renewal Letter Page 2")).Select


pdfName = ProposalContract & " - " & InstalledAtName & " Agreement Renewal.pdf"
    ChDir Environ("HOMEPATH") & "\Desktop\ServiceAgreement Renewals\" 'This is where you set a default file path.
    fileSaveName = pdfName
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    End If
    'MsgBox "File Saved to" & " " & fileSaveName

Here is the email attachment code:

pdfName = ProposalContract & " - " & InstalledAtName & " Agreement Renewal.pdf"
pdfPath = Environ("HOMEPATH") & "\Desktop\ServiceAgreement Renewals\"


        'DRAFT EMAIL MESSAGE
           ' Create a new mail message item.
            Set NewMail = olApp.CreateItem(0)
            With NewMail
                .Subject = "OurCompany - Service Agreement Renewal Notification - " & ProposalContract
                '.SentOnBehalfOfName = "SISRep@ourcompany.com"
                .to = CustEmail
                .CC = "[SIS REP][DM][Preferred Eng]"
                .Attachments.Add (pdfPath & pdfName)

The error occurs on the last line of the Email code and returns run time error -2147024894 (80070002). I have checked the file path, and file name several times manually as well as the immediate window and they match between both sets of code. I am clueless as to what the issue could be now.

[Kenneth Hobs]

You don't need the ()'s. Before that line, put this code. After a run, check the Immediate window in VBE.

Debug.print pdfPath & pdfName, Len(Dir(pdfPath & pdfName))<>0

The Dir() checks for the file existing or not which might be a good idea to avoid the error. IF True, the file exists, if False, the file does not exist.

I don't know that I have ever found ChDir() useful. I would just set the full path. e.g. In the first code:

pdfName = Environ("HOMEPATH") & "\Desktop\ServiceAgreement Renewals\ProposalContract" & " - " & InstalledAtName & " Agreement Renewal.pdf" 
'or
pdfName =CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\ServiceAgreement Renewals\ProposalContract" & " - " & InstalledAtName & " Agreement Renewal.pdf"

Of course then just use pdfName in your 2nd code for the file attachment name.

[qitjch]

I made both changes that you suggested and it seems to be working well now. Definitely think it was your 2nd recommendation about getting rid of the ChDir() that ultimately fixed the issues I was having. Thanks again for the quick response and fix. Greatly appreciated!