• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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

  • 3
  • 3
1 Solution
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

Cole100Author 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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Cole100Author 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.
Cole100Author Commented:
this got it.......Awesome!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now