email attachment with file name problem (1 Viewer)

[Jimbob24]

Hi all,


I email my invoices as a pdf attachment from Access (2013).

After a long search and tinkering, I found a method that the invoice no. is set up as the pdf file name.

I do that with

DoCmd.OpenReport strDocName, acViewPreview, , , acHidden

  Reports(strDocName).Caption = sAttachmentName

i.e. the report is opened and the document name is replaced with the invoice no., then the invoice is send with

DoCmd.SendObject acSendReport, strDocName, acFormatPDF, "" & Me!EmailCus & "", _

and attached as a pdf.


Everything works well so far, but unfortunately the generation "swallows" when I want to send several invoices, i.e. the generation of the first invoice works perfectly. If I then want to create another subsequent invoice, the correct current invoice number is shown in the e-mail. (also as Attachment-name) but the attachment itself contains the previous invoice. If I cancel the process, the report printout of the previous invoice is displayed on the monitor. I then have to close it manually.


The close instructions didn't help either.

DoCmd.Close acReport, strDocName

  DoCmd.Close acReport, "CurrentRecord"

If I remove

DoCmd.OpenReport strDocName, acViewPreview, , , acHidden

Reports(strDocName).Caption = sAttachmentName

then I don't have the problem but also no invoice number as file name.


Can you help me?


P.S. I transfer the invoice number to the report with aInvID

 

[Jimbob24]

Sorry, I'm not able to post my complete VBA here - it is rejected.
How can I do that?

 

[Pat Hartman]

If sAttachmentName is a control on the form that is running this process, you can reference the form control directly and don't have to change the caption after the fact.

To use the form field reference, change the display control from a label to a textbox. Then in the ControlSource use something like:

=Forms!yourformname!sAttachmentName

If that field is a variable in the VBA, you can create a hidden form field and put the value there before you open the report.

Then, you stll have to close the report after the SendObjects in order for your loop to work.

 

[Gasman]

Crossposted from https://www.utteraccess.com/topics/2062798/posts/2794803