Sending email with attachment - can't attach a pdf

[WalkieTalkie]

Hi, its been a long time since I last posted, as I can usually find answers by looking through other people's posts. But this time I have a problem which I just cannot solve, despite all the help there is out there.

Using Windows 10, Access 2016 and VBA 7.1, with a database that was designed in Access 2003:

I am trying to attach a pdf invoice (generated from an Access report) to an email and then display the email before sending it. I have a form (frmEmailMessage) which contains textboxes with the recipient's name (txtAccountName), email address (txtAcctEmail) and body text (txtMessage).
Everything works fine, except that I cannot attach the pdf. I get an error "Run time error '-2147024894(80070002)': Cannot find this file. Verify the path and filename are correct". I have tried replacing the file input variable with an actual filename and that works OK. How can I get the file I have just created to attach?
Here's my code:

Private Sub cmdReportToPDF_Click()
Dim stDocName As String
Dim strPath As String

'saves the report as a pdf
strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtRowerName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"

DoCmd.OutputTo acOutputReport, "rptInvoiceIndividual", acFormatPDF, strPath, False

'*******************************************************************************************************

'send pdf by email
Dim MsgBody As String
Dim MyStatementID As Integer

Dim blRet As Boolean

MsgBody = "Hi " & Me.txtAccountName & Chr$(13) & _
             Chr$(13) & Me.txtMessage
             
'Show the Outlook message before sending
Call SendEmailDisplayOutlook(Me.txtAcctEmail, "Invoice " & txtStatementID, MsgBody, strPath)

DoCmd.Close acReport, "rptInvoiceIndividual"

DoCmd.Close acForm, "frmEmailMessage"
End Sub

Public Function SendEmailDisplayOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
strPath As String)

strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtAccountName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"


' Get Outlook 

Dim olApp As New Outlook.application

Dim olMailItem As Outlook.MailItem

' Create a new email object

Set olMailItem = olApp.CreateItem(0)

' Add the To/Subject/Body/Attachments to the message then display the message

With olMailItem
.To = MsgTo
.Subject = MsgSubject
.Body = MsgBody
[highlight #EDD400].Attachments.Add strPath[/highlight] 'this is where I get the error
 
.Display
End With

Set olMailItem = Nothing

Set olApp = Nothing

End Function

Any help will be gratefully accepted!

 

[MajP]

The names are not the same when you save it. RowerName versus accountname.

strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtRowerName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"


strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtAccountName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"

 

[WalkieTalkie]

Thanks for the quick reply. Good spotting! Sorry, that was me trying to remove confusion in my post by replacing RowerName with AccountName, but now I see I didn't change them all in the post. The real code has RowerName in all instances.

More info: I have narrowed it down to something to do with the Format(Now) expression in the path name:
This code works:

.Attachments.Add "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtRowerName & "_" & Me.txtStatementID & ".pdf"

But this code throws an error:

.Attachments.Add "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtRowerName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"

Any ideas why?

 

[MajP]

If what you posted is correct this time, it still makes no sense and again there is no way for your names to match. So you cannot find what you are looking form.

You save strPath in the procedure cmdReportToPDF
Then you path this path to the procedure sendEmailDisplayOutlook
But then you get the path again instead of using the path you passed in. So of course the seconds have changed.

Public Function SendEmailDisplayOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
strPath As String)

strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtAccountName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"

You need to get rid of the line where you find the path, above. You pass in the correct name, but then get a new name that is not the name of the saved document.

 

[MajP]

For some reason it will not let me edit the above post, so I had to repost. It had some grammar and spelling mistakes making it confusing.

If what you posted is correct this time, it still makes no sense and again there is no way for your names to match. So you cannot find what you are looking for.

You save strPath in the procedure cmdReportToPDF
Then you pass this path to the procedure sendEmailDisplayOutlook
But then you get the path again instead of using the path you passed in. So of course the seconds have changed.

Public Function SendEmailDisplayOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
strPath As String)

strPath = "C:\Users\KASC\Documents\Invoices emailed pdfs\" & Me.txtAccountName & "_" & Me.txtStatementID & "_" & Format(Now, "YYYYMMDD_hms") & ".pdf"

You need to get rid of the line where you regenerate the path. You pass in the correct name, but then get a new name that is not the name of the saved document.

 

[WalkieTalkie]

Thanks you! Its so obvious once you've pointed it out! I knew it must be something simple - maybe I was too tired to look at it properly yesterday. Thanks again for your help.