I have form in my database that i use to generate a purchase order to send out to suppiers. The purchase order is just a report that i print out and take to accounting so they can process it. I also have to send this in an email, which i attach as a .pdf file, to our contact at the supplier company. I have no problem starting an email then attaching the file manually but i would like to use the "OutputTo" command to output a pdf file as i print the hardcopy and save it in a particular place on the company shared drive. The output command lets me specify which report, what file type, and also where to save the file to but it doesn't work unless i specify an exact file name. I would like to save the file as the purchase order name, which is a field included in the report. Any ideas how to do this using the output command in the macro builder(and yes i know everyone avoid macros like the plague, but i don't know how to program using VB, yet.
The only way I know of to do it is through VBA code. If you search the Internet for RunReportAsPDF there is code to do it. But the code is fairly advanced. It manipulates registry settings to save the filename that should be used by Adobe to create the PDF.
Before I found the code, I popped up an InputBox with the filename that I wanted to use, just before creating the PDF. So the InputBox would pop up, I would copy the filename (Ctrl-C), close the InputBox and then the PDF would prmpt for a filename. I pasted the filename and it created the PDF where I wanted it with the name I wanted.
Not very polished but it worked.
RunReportAsPDF is someone else's work so I won't post it. Besides, the version I have I tweaked to make it work for my situation.
I have been playing around and finally go what i wanted working.
Private Sub Command30_Click()
DoCmd.OpenReport "rptCoatedPartCache", acViewPreview
DoCmd.OutputTo acOutputReport, "rptCoatedPartCache", acFormatPDF, _
"U:\POBushingsAndPins\" & "PurchaseOrder" & [PurchaseOrder#] & ".pdf", False
This will open the report rptCoatedPartCache in print preview mode and outputs the file in .pdf format to the location on our shared company drive U:\ to the folder POBushingsAndPins and names the file PurchaseOrderXXXX.pdf, with the XXXX being the purchase order number from the report. It will dynamically name the file whatever field you choose from the database.