Temporarily save a Report pdf

[ellenr]

I have a working cdo function that allows the user to specify a file at a location on his computer to be attached to the outgoing emails. Now I would like to add code to attach a pdf version of an Access report. I am trying to avoid opening the report, saving it as a pdf to some location and then entering that location into my the attachment address.
In order to automate the process I tried:

DoCmd.OutputTo acOutputReport, "CLGAFullList", acFormatPDF, "C:\CLGAFullList.pdf"
    sAttachmentLocation = "C:\CLGAFullList.pdf"

but without "my administrator" allowing me to save in that location, it erred. I realize I can save to a different location, but need something that will work for whomever has the program.

 

[theDBguy]

Hi. Are you trying to attach more than one file to the same email? If not, try using the SendObject method instead.

 

[ellenr]

Hi, theDBguy--thanks for the response. How can I use that to send to a list of email addresses? That is why I am trying to use cdo code.

 

[June7]

You want to attach an external file as well as report? If so, cannot avoid saving report to external location. Perhaps use: CurrentProject.Path

Multiple addresses would be a string using semi-colon separator. Open a recordset of these desired addresses and loop through recordset to build the address string. This address string will work with either CDO or SendObject.

But do you really want everyone to see everyone's email address? Should use BCC instead? Use yourself as the To address.

 

[theDBguy]

Hi. You could try using this function to generate the list of email. That is, if you’re sending one email with one attachment to multiple people.

 

[ellenr]

The recipient list varies depending upon how many members' "send email" boxes are checked (what subset of members are chosen). There are times when too many are in the list. My cdo code sends emails individually, and all begin with "Dear " & firstname. Just wish I could automate attaching one of the program's reports. This program may be run under Runtime by users who don't use outlook.

There must be some way to attach a report as a pdf within the cdo code??

 

[June7]

With CDO the file must first be saved to external location then that file is attached to email. Period. Do you know code for attaching file?

Did you try CurrentProject.Path? Or maybe can save file to Desktop.

"C:\Users\" & Environ("USERNAME") & "\Desktop\filename.pdf"

 

[ellenr]

Yes, June7, my cdo routine has the code for attaching a file specified in the calling form. Thank you for clarifying the question about attaching an internal report. Is there a way to, via code, to open the form, save it to the folder containing the access program, then attach it? Just seems like it ought to be possible.

 

[The_Doc_Man]

June7's response in post #4 is your answer. The "CurrentProject" object is essentially a self-referential pointer to the project in which the code is running. The .Path property is then the device and path to the folder in which that project exists. For example, I went into a code window to examine MY project path. I typed the stuff in RED. The computer returned the stuff in black.

[COLOR="Red"]debug.Print currentproject.Path[/COLOR]
C:\Users\Richard\Documents\Projects\AncestryFiles

So... if all you want to do is store a file in a place where you HAVE to have write privileges, which you MUST have for the location of the .ACCDB file, use something like

sAttachmentLocation = CurrentProject.Path & "\CLGAFullList.PDF"
DoCmd.OutputTo acOutputReport, "CLGAFullList", acFormatPDF, sAttachmentLocation

Then use the location for your CDO attachment step. Once you have sent the message and you are ready to clean up, don't forgot to use something like

Kill sAttachmentLocation

to delete the file. But of course you could choose to let it sit until you need to create another file of the same name.

 

[Deleted member 147267]

research Docmd.OutputTo to output a report as a pdf, then email from the same stored location.

 

[theDBguy]

Hi ellen. Just in case you missed it, June already mentioned how to save the file to the folder containing the program. It’s the “CurrentProject.Path” part in his post.

Edit: Oops, too slow...

 

[ellenr]

Fantastic! Thank you all!