asked on

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, "", "")
       RS.MoveNext
    Loop
    'Clean up process
    RS.Close
    DoCmd.Close A_REPORT, docname
    Set RS = Nothing
    Set MyDB = Nothing
 
exit_send_click:
    
    Application.Echo True
    Exit Function
 
Err_Send_Click:
    Resume exit_send_click
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Lambert Heenan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cole100

ASKER

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.

Lambert Heenan

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.

Cole100

ASKER

So, If my Access DB is in My Documents I need to put the drivers in the my Documents folder as well?

Lambert Heenan

That's right.

Cole100

ASKER

this got it.......Awesome!