Save Access Report Automatically after printing to PDF

[nalongar]

Hi!

First off, bear with me; I've just started to learn VBA and this is my first post on this site. My question could be extremely basic, but I haven't been able to find anything via the internet that fits my needs exactly.

I need a way to save a report to a specific path after I have printed it as a PDF, so that I can send it as an email attachment. The idea is that an associate would be able to press a button and the report would print to PDf and send. I have the printing and the email code, but where I'm stuck is that I don't know how to save the report automatically to a path after using the PDF Printer. Right now it prints but then opens a "Save As" dialogue box. I'd like it to print and then save itself to a path.

This is the code I'm using at the moment to print the report:

Private Sub Command0_Click()
 
Dim defPrinter As String, NewPrinter As Printer
 
defPrinter = Application.Printer.DeviceName
 
Set NewPrinter = Application.Printers("Nationwide PDF Printer")
 
Set Application.Printer = NewPrinter
 
DoCmd.OpenReport "Weekly Application Status Update", acViewNormal
 
DoCmd.Close acReport, "Weekly Application Status Update", acSaveNo
 
Set NewPrinter = Application.Printers(defPrinter)
Set Application.Printer = NewPrinter
End Sub
 

Thanks in advance!

[jforbes]

I think if you are using a Print Driver to generate the PDF, you are at the mercy of the Print Driver when it comes to it's options. So if it has a way to automatically save to a directory based on a settings file or something similar then that is the route you will need to take.

If you are using Access 2007 or newer, you can use DoCmd.OutputTo to have Access create the PDF for you and use the options that is supplies to save the PDF to a hard drive.

Here is some code from a project I worked on recently:

DoCmd.OpenReport sReportName, acViewPreview, , "ID='" & sID & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFileName, True
DoCmd.Close acReport, sReportName

In my example, I supply a Filter for the Report. Which requires opening the report first and then outputting to a PDF instead of just producing the output. The Report could be based on a Query to Filter the Report records and then the above code would be simpler, but there were other reasons that I decided against it.

So if you don't need to Filter on open, which it doesn't look like you do, you should be able to getaway with just the single DoCmd.OutputTo command; something like this:

DoCmd.OutputTo acOutputReport, "Weekly Application Status Update", acFormatPDF, sFileName, True

[nalongar]

That worked like a dream! And so simple too! Thank you for the help!