INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sending email with attachment - can't attach a pdf

Sending email with attachment - can't attach a pdf

(OP)
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:

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
.Attachments.Add strPath 'this is where I get the error
 
.Display
End With

Set olMailItem = Nothing

Set olApp = Nothing

End Function 

Any help will be gratefully accepted!

RE: Sending email with attachment - can't attach a pdf

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"

RE: Sending email with attachment - can't attach a pdf

(OP)
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:

CODE -->

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

But this code throws an error:

CODE -->

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

Any ideas why?

RE: Sending email with attachment - can't attach a pdf

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.

CODE -->

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.

RE: Sending email with attachment - can't attach a pdf

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.

CODE -->

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.

RE: Sending email with attachment - can't attach a pdf

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close