Adding attachments to email, saving attachments

[Gicu]

Your path reverted to be missing the back slash so the file name will be "C:\Documents\InvoicesForApprovalSECIM08-78.pdf", is that what you want?


Can you please try it with the back-slash?
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fileName

[Gicu]

And make sure the path is valid, do you have a subfolder named Documents in your C: drive?

[snsmith]

Oh my gosh, what facepalm moment that was! I didn't have the full folder path: This worked almost perfectly! The invoice is generated, renamed and placed into the appropriate folder BUT it's not refreshing the data on the form that I have. This is supposed to be filtered with "[Study ID]=" & [Study ID]

Updated Code: I hope this is helpful. I'm just trying to help so you don't have to go back and look at other posts.
Dim reportName As String
Dim fileName As String
Dim criteria As String




reportName = "Invoice"
fileName = "c:\Users\Snsmith\Desktop\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]


DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

[Welshgasman]

You still do not have the trailing backslash for the path?????

Post what you are actually using, within code tags would be appreciated as well? Use the # icon

[snsmith]

So odd as I do have the backslash on my code. Not sure why it's not on the paste side of the code. I'll try again.

Dim reportName As StringDim fileName As String
Dim criteria As String




reportName = "Invoice"
fileName = "c:\Users\Snsmith\Desktop\InvoicesForApproval\" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]


DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

[Gicu]

So what exactly is happening? You get a PDF invoice not matching the [Study ID]? I haven't asked before, just assumed it is a number, but if it is text it should be wrapped in single quotes:

criteria = "[Study ID]='" & [Study ID] & "'"

Maybe you could prepare a small db sample with just the required objects (tables, form, report) with just a few "dummy records to illustrate the issue.

You could also look at this thread in another forum for an alternate way of doing what you originally posted (emailing individual reports):
https://www.access-programmers.co.uk.../#post-1764086

Cheers,
Vlad

[Welshgasman]

Added in error

[Welshgasman]

So odd as I do have the backslash on my code. Not sure why it's not on the paste side of the code. I'll try again.

Likely because you were not using code tags

Another good reason to use them, as the site strips out some characters if not.

[snsmith]

The Study ID is an autogenerated number. When I do the PDF the file name is as it should be and in the right location, but when I open it the invoice # is a different invoice# and the data within the pdf includes everything on that particular report with no filters. I'm trying to do a mini db of the issue but it's taking some time.

[Gicu]

You should check the Where condition:
- Is the [Study ID] field included in the Invoice report record source (table\query)?
- Is the text box bound to [Study ID] on your form called "Study ID"?
If you would use Me. then the VBA Intellisense would kick in and help you choose the right control:

criteria = "[Study ID]=" & Me.[Study ID]

TO check if you get the right invoice right away change remove the acHidden from the OpenReport line and put a break on the Docmd.OutputTo one; when the code stops you should be able to inspect the report to see if you have the right one.
Cheers,

[snsmith]

That did help as I was able to see that I had a space in the name so needed to add the underscore. I've done a very scaled down version of what I have so you can see what's happening.

The "Generate Invoice Emails" works perfectly on my side, but issue with the test db. Ideally, I'd love to have that button generate the email, create the invoice, save the invoice then attach the same invoice to the email. I'm hoping that if I can at least get it to cycle through to save the invoices then I can consolidate into the code to generate the emails.

[Welshgasman]

Break it down into steps.
I would
Create and Save an Invoice, Then create an email and then attach it to an email.
3 steps there to work on.

[Gicu]

Are you using Outlook as the email client?

[snsmith]

Yes I am using Outlook.

[Gicu]

Please have a look at the attached file, clicking the first button should do what you want (I had to change some field names in the recordset to match the query fields and removed the amount as it was not in the query, you should be able to edit it to match your actual db).

Cheers,
Vlad