Need to alter code in access function

In the macro.........the function below automatically exports the access report to the server > opens Outlook > attaches the doc to an email > sends out to select email addresses contained in a table > closes Outlook.

I want to alter this code to export reports as PDF instead.  I recently installed full version Acrobat to get the export capability, but I do not have option for PDF in the export menu.  Acrobat has its own menu in Access 07, so this function isnt finding the PDF export selection and stops.

Option Compare Database
Function Send_Daily_Stat()
   On Error GoTo Err_Send_Click
    Dim MyDB As Database, RS As Recordset
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Dim docname As String, ctl As Control, strTo As String
    Dim path As String, subject As String, body As String
    Dim attach As String, blnSuccessful As Boolean
    Application.Echo False
    'Get all the email addresses  for the daily stat report
    Set RS = MyDB.OpenRecordset("qry_daily_stats_email")
    'Report to run and make a snapshot of the report and put it on the server
    path = "c:\temp\"
    docname = "BBI DAILY REGIONAL"
    subject = "Daily Stat Report"
    body = "Daily Report is Attached"
    attach = path + docname + ".rtf"
    'DoCmd.OpenReport docname, A_PREVIEW
    DoCmd.OutputTo acOutputReport, docname, acFormatRTF, _
    attach, False
    'This loops through all of the email addresses and sends the report to everyone
    Do Until RS.EOF
      strTo = RS!Email
       'Old format for sending reports with warnings
       'DoCmd.SendObject A_REPORT, docname, A_FORMATTXT, strTo, , , "Daily Stat Report", , False
       'New way of sending reports without warnings
       blnSuccessful = FnSafeSendEmail(strTo, subject, body, attach, "", "")
    'Clean up process
    DoCmd.Close A_REPORT, docname
    Set RS = Nothing
    Set MyDB = Nothing
    Application.Echo True
    Exit Function
    Resume exit_send_click
End Function

Open in new window

Cole100IT Systems ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Check out the code here...

This code (which should convert just fine to Access 2007) will produce PDF files for you *without* any need for Acrobat to be installed.

I use this with one small modification.

In the routine ConvertReportToPDF there is a call

DoCmd.OutputTo acOutputReport, RptName, "SnapshotFormat(*.snp)", etc.

I have found that sometimes this results in the error "OutputTo not available", but I discovered through Google that this can be fixed by first selecting and viewing the tables window, and so the above single line of code was expanded to...

              ' Export the selected Report to SnapShot format
200           DoCmd.Echo False
              'Show the db window
210           DoCmd.SelectObject acTable, , True
220           DoCmd.OutputTo acOutputReport, RptName, "SnapshotFormat(*.snp)", _
230           DoCmd.SelectObject acTable, , True
240           DoCmd.RunCommand acCmdWindowHide
              'turn the echo back on
250           DoCmd.Echo True

... and this works all the time.

Because of the use of Docmd.Echo False the error handler for the routine was also modified by adding these three lines as the first action...
830       DoCmd.SelectObject acTable, , True
840       DoCmd.RunCommand acCmdWindowHide
          'turn the echo back on
850       DoCmd.Echo True

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cole100IT Systems ManagerAuthor Commented:
This looks really good, but I am novice.  Could you break down the installation of this module and the drivers.  Not sure how to integrate this into my existing process.

Thanks for your help.
All you need to do is download the example zip file from Steven's web site. The two DLL  filesit contains need to be place in the same folder as the application that will be using them, but there's no special registration or installation to do.

Then open the MDB file in the Zipped archive. There are two modules that you need to export to your own application: clsCommonDialog and modReportToPDF.

Inside modReportToPDF is the routine ConvertReportToPDF  where you should make the modifications mentioned in my first post.

Once the change has been made and the modules and DLLs dealt with as above, you can then print a report to PDF with a simple call...

ConvertReportToPDF strWaverReport, , strFilePath, , False.

Here "strWaverReport" as a variable holding the name of a report object
"strFilePath" as a varaible holding the full path an file name of the PDF to create
and the 'False' parameter simplaysays to NOT open the file a a PDF viewer.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Cole100IT Systems ManagerAuthor Commented:
So, If my Access DB is in My Documents I need to put the drivers in the my Documents folder as well?
That's right.
Cole100IT Systems ManagerAuthor Commented:
this got it.......Awesome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.