Need to alter code in access function

Posted on 2008-11-05
Last Modified: 2013-11-27
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

Question by:Cole100
    LVL 11

    Accepted 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


    Author Comment

    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.
    LVL 11

    Expert Comment

    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.

    Author Comment

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

    Expert Comment

    That's right.

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now