Need code to e-mail a report-based .pdf via outlook

[Ingeneeus]

Hi, All --

I've searched through the forum (and several others) to try and get some idea of how to go about generating an e-mail which contains a .pdf report -- specifically, a purchase order.
I've found several threads which reference this, but I can't seem to figure out how to adapt the advice in them to my own ends.

Most of the posts I've seen involve first creating a .pdf from a report and then e-mailing it with the DoCmd.SendObject command.

The thing is that the Access-based application I'm using already has a module that runs a report that generates purchase orders as .pdf files. Basically, it looks for a parameter in the "Supplier Information" table which tells it to a) Send the report to a printer for mailing/faxing, b) create a .pdf to be saved, or c) fax the report to a supplier automatically. I really, really don't want to mess around with the code that creates the POs.

I had a thought that I could add a DoCmd.SendObject line into the OnOpen Event of the "b)" report (the one that creates a .pdf).

 DoCmd.SendObject acSendReport, , acFormatPDF, , , , "PO From Action", "Hello, this is a Purchase Order", True

This way, I wouldn't have to muck around in the module itself and possibly screw it up (it's about 10 grades above my level). Unfortunately, when I tried that, it produced a Run-time error 2585: This action can't be carried out while processing a form or report event.

I guess what I'm looking for is a way to take the report that the module generates, and modify it so that instead of creating a .pdf to be saved, it sends it to an Outlook email; lather, rinse, repeat through all suppliers for whom POs need to be generated.

Any thoughts on how I can make this happen from the report end, rather than the module? I hope I'm articulating this all right. I had hoped to post the module code, but as it turns out I can't even find the lines that call the report.

As always, any insight would be most welcome

 

[James Deckert]

Let it create the pdf and save it, then attach it to your email.

 

[Ingeneeus]

Let it create the pdf and save it, then attach it to your email.

Hi, James --

Could you by chance offer some guidance as to how to go about doing that?

 

[Cronk]

You could for a start look up

Docmd.outputto and use the acformatpdf option to generate the PDF file.

Then you could search this site for "send email with attachment"

 

[Ingeneeus]

You could for a start look up

Docmd.outputto and use the acformatpdf option to generate the PDF file.

Then you could search this site for "send email with attachment"

Thanks, Cronk. Unfortunately, the module is already creating a .pdf from the report. When I run it, I get a prompt to save the file. What I'm looking for is a way to make this process a little more automatic. We typically generate 15 to 20 of these .pdf purchase orders at a time. I think it needs to happen when the report is actually active, so that the SendObject sequence can grab the email address from the supplier table, but I don't know how to DO that.

 

[Cronk]

What do you mean it is "already producing a PDF"?

Are you providing a full path and file name for the output to be saved to in the docmd.output line? Have you designated a PDF generator as the specified printer for the report in the report design?

 

[Ingeneeus]

What do you mean it is "already producing a PDF"?

Are you providing a full path and file name for the output to be saved to in the docmd.output line? Have you designated a PDF generator as the specified printer for the report in the report design?

Hi, Cronk -- Sorry, I should have been more specific. There is no path and filename specified in the output line (hence the "Save As" window), and you are correct that the designated "printer" for the report is a .pdf generator.

That's why I was really hoping that I could do this at the report level instead of the module (kind of a "print" to e-mail idea). I've got a backup copy of the report in question, so if I mess something up there, it's no big deal. If I mess up something in the module, I'm kind of scr3wed.

 

[James Deckert]

I'm attaching files using Outlook, so don't know if there is a different way. Below is my code you may be able to figure out. I'm also sending HTML, but don't think you need to do that.
Dim objMsg As Object
Set objOutlook = CreateObject("Outlook.Application")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Const olMailItem = 0
Set objMsg = objOutlook.CreateItem(olMailItem)
Const olFormatHTML = 2
With objMsg
.To = strRecipient
.Subject = strSubject
.BodyFormat = olFormatHTML
.HTMLBody = strBody
If Len(strFileAttach1) > 0 Then
.Attachments.Add (strFileAttach1)
End If
If Len(strFileAttach2) > 0 Then
.Attachments.Add (strFileAttach2)
End If
If Len(strFileAttach3) > 0 Then
.Attachments.Add (strFileAttach3)
End If

.Send
End With

Set objOutlook = Nothing
Set objMsg = Nothing

 

[Cronk]

Ingeneeus

Re-read my post #4. Do not use docmd.openreport using a pdf generator.

Instead use the command docmd.outputto
with a file name to generate the PDF document and save it to the path and filename you give, Then you can use something like the code in the previous post, to generate the email with the pdf attached.

Put the whole lot in a loop and you have the 15 to 20 emails you want without any manual intervention.

 

[Ingeneeus]

I'm attaching files using Outlook, so don't know if there is a different way. Below is my code you may be able to figure out. I'm also sending HTML, but don't think you need to do that.
Dim objMsg As Object
Set objOutlook = CreateObject("Outlook.Application")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Const olMailItem = 0
Set objMsg = objOutlook.CreateItem(olMailItem)
Const olFormatHTML = 2
With objMsg
.To = strRecipient
.Subject = strSubject
.BodyFormat = olFormatHTML
.HTMLBody = strBody
If Len(strFileAttach1) > 0 Then
.Attachments.Add (strFileAttach1)
End If
If Len(strFileAttach2) > 0 Then
.Attachments.Add (strFileAttach2)
End If
If Len(strFileAttach3) > 0 Then
.Attachments.Add (strFileAttach3)
End If

.Send
End With

Set objOutlook = Nothing
Set objMsg = Nothing

Hi, James --
Thank you for the code. I believe I understand the underlying idea. I'm more than a bit leery about trying it out though, as -- if I understand it -- I'd have to actually make changes in the module itself, not the report. I'm more than a little intimidated at the prospect; I could really mess up our operation if I screw something up.

 

[Ingeneeus]

Ingeneeus

Re-read my post #4. Do not use docmd.openreport using a pdf generator.

Instead use the command docmd.outputto
with a file name to generate the PDF document and save it to the path and filename you give, Then you can use something like the code in the previous post, to generate the email with the pdf attached.

Put the whole lot in a loop and you have the 15 to 20 emails you want without any manual intervention.

Hi, Cronk --

Thank you for continuing to follow this thread. I understand what you are trying to tell me, but -- as I said in my reply to James -- this would require me to change the docmd.openreport command (if indeed that is how this is written) in the module, which I am exceedingly reluctant to do. Unfortunately, I'm not an Access programmer; I'm just the guy who's been tasked with making this off-the-shelf application do things it wasn't designed to do .
The code in this purchase order module is pretty deep water to me.
I think I may have to let this one go.
Have a good weekend -- I will ruminate on this a bit and maybe take another stab on Monday or Tuesday.

~Gene