Solved

Save Access Report to PDF and Email the PDF file via Outlook

Posted on 2013-10-31
31
5,141 Views
Last Modified: 2013-11-13
I have some code which I found on the MS Access Help Center which saves (print) a report to a PDF file by using code.  I revised this slighly based on my needs.  In addition, once I have the PDF saved, I added the code to email the PDF file via Outlook.

Everything works perfectly with one exception.  When I run the code, I get the prompt "Save PDF File As" and have to pick the location where I want to save the PDF file.  I choose C:\TimeOff.pdf since my "Email the pdf file via Outlook" code looks for the file here.  Then the PDF file opens and the email is sent.

I need to be able to run the code without it prompting me where to save it and to save it automatically under C:\TimeOff.pdf and then not open the PDF file.  What I'm trying to accomplish is when the code is ran that it just emails the PDF file to someone.  My ultimate goal will be to run this code every day automatically using Windows Scheduled Tasks.

Here is my code.

Private Sub cmdEmailPTOCal_Click()
    Dim prnt As Printer
    Dim strRptName As String
    Dim strPDFPathNameMe As String
    Dim objOutlookApp As Object
    Dim objMailItem  As Object

    Call RunReportAsPDF("rptCalendar_Month", "c:\", "TimeOff.pdf")

'Email the pdf file via Outlook
     Dim ol As Object
     Dim itm  As Object '(Creates the Outlook Application Object by accessing the MS Outlook COM Type Library)
     Set ol = CreateObject("Outlook.Application")
     Set itm = ol.CreateItem(0) '(Outlook a Mail Message)
     itm.To = "LMueller@Senniger.com"
     itm.Subject = "PTO Calendar Report"
     itm.Body = "Attached is the PTO Calendar"
     itm.Attachments.Add ("c:\TimeOff.pdf")

     itm.Send  '(to send)
     Set itm = Nothing '(Cleans up)
     Set ol = Nothing
End Sub



mdlPDFwriter

Option Compare Database
'**************************************
'Windows API/Global Declarations for :Create PDF from MS Access Report
'**************************************
Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Declare Function RegSetValueEx Lib "advapi32" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal szData As String, ByVal cbData As Long) As Long
Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long
Declare Function RegCreateKeyEx Lib "advapi32" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As Long, lpdwDisposition As Long) As Long

    #If Win32 Then
        Public Const HKEY_CLASSES_ROOT = &H80000000
        Public Const HKEY_CURRENT_USER = &H80000001
        Public Const HKEY_LOCAL_MACHINE = &H80000002
        Public Const HKEY_USERS = &H80000003
        Public Const KEY_ALL_ACCESS = &H3F
        Public Const REG_OPTION_NON_VOLATILE = 0&
        Public Const REG_CREATED_NEW_KEY = &H1
        Public Const REG_OPENED_EXISTING_KEY = &H2
        Public Const ERROR_SUCCESS = 0&
        Public Const REG_SZ = (1)
    #End If

Type SECURITY_ATTRIBUTES
    nLength As Long
    lpSecurityDescriptor As Long
    bInheritHandle As Boolean
    End Type
'**************************************
' Name: Create PDF from MS Access Report
'
' Description:On a machine where the Adobe 'Adobe PDF' is installed, the current printer is swapped out with the 'Adobe PDF'
' and the PDF file is created. The original printer is then restored.
'
' Inputs:rptName = Microsoft Access report name you want to create pdf from. sPDFPath = the directory path where you want
' to create the pdf file (ex. - "c:\data\"). sPDFName = the name of the pdf file you are wanting to create
' (ex. - "file001.pdf").
'
' Returns:None
'
' Assumes:This code is easily modified to be used in other programs
'
' Side Effects:Please use the most recent installs of Adobe Exchange or 'Adobe PDF' to ensure proper functionality.
' This code is copyrighted and has limited warranties.
'**************************************

Public Function bGetRegValue(ByVal hKey As Long, ByVal sKey As String, ByVal sSubKey As String) As String
    Dim lResult As Long
    Dim phkResult As Long
    Dim dWReserved As Long
    Dim szBuffer As String
    Dim lBuffSize As Long
    Dim szBuffer2 As String
    Dim lBuffSize2 As Long
    Dim lIndex As Long
    Dim lType As Long
    Dim sCompKey As String
    Dim bFound As Boolean
    lIndex = 0
    lResult = RegOpenKeyEx(hKey, sKey, 0, 1, phkResult)

    Do While lResult = ERROR_SUCCESS And Not (bFound)
        szBuffer = Space(255)
        lBuffSize = Len(szBuffer)
        szBuffer2 = Space(255)
        lBuffSize2 = Len(szBuffer2)
        lResult = RegEnumValue(phkResult, lIndex, szBuffer, lBuffSize, dWReserved, lType, szBuffer2, lBuffSize2)

        If (lResult = ERROR_SUCCESS) Then
            sCompKey = Left(szBuffer, lBuffSize)

            If (sCompKey = sSubKey) Then
                bGetRegValue = Left(szBuffer2, lBuffSize2 - 1)
                RegCloseKey phkResult
                Exit Function
            End If
        End If
        lIndex = lIndex + 1
    Loop
    RegCloseKey phkResult
End Function
Public Function bSetRegValue(ByVal hKey As Long, ByVal lpszSubKey As String, ByVal sSetValue As String, ByVal sValue As String) As Boolean
    On Error Resume Next
    Dim phkResult As Long
    Dim lResult As Long
    Dim SA As SECURITY_ATTRIBUTES
    Dim lCreate As Long
    RegCreateKeyEx hKey, lpszSubKey, 0, "", REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, SA, phkResult, lCreate
    lResult = RegSetValueEx(phkResult, sSetValue, 0, REG_SZ, sValue, CLng(Len(sValue) + 1))
    RegCloseKey phkResult
    bSetRegValue = (lResult = ERROR_SUCCESS)
End Function
Public Function RunReportAsPDF(rptName As String, sPDFPath As String, sPDFName As String)
    '---------------------------------
    'rptName = Microsoft Access report name you want to create pdf from sPDFPath = the directory path where you
    'want to create the pdf file (ex. - "c:\data\") sPDFName = the name of the pdf file you are
    'wanting to create (ex. - "file001.pdf")
    '
    '---------------------------------
    Dim sMyDefPrinter As String
    On Error GoTo Err_RunReport
    'Save current default printer
    sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
    ' Set default printer to PDF Writer
    bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Adobe PDF"
    'Setting value for PDFFileName in the registry stops file dialog box from appearing
    bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Adobe PDF", "PDFFileName", sPDFPath + sPDFName
    'Run the report
   
  DoCmd.OpenReport rptName, acViewNormal

Exit_RunReport:
        ' Restore default printer
        bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", sMyDefPrinter
        Exit Function
Err_RunReport:
        MsgBox Err.Description
        Resume Exit_RunReport
    End Function
       

Thanks in advance for your assistance!
0
Comment
Question by:Senniger1
  • 12
  • 8
  • 5
  • +2
31 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 450 total points
ID: 39614203
Instead of all that RunAsPDF code you should look as the Access Help for the

Docmd.OutputTo

method.  That method will allow you to save the report as a PDF to a specific file name with a single line of code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614216
I presume this is Access 203 or older where PDF Support is not built in.

Not sure about your code, but this utility has worked flawlessly for me over the years to create PDF in Access 2003 and older:
http://www.lebans.com/reporttopdf.htm
...using this code/example you would hardcore the path/name for the PDF
Typically this will be the unique identifier of the record(s), or the whatever they have in common
...you can pull this in a a variable...

I an sure there is a way to do this with your code, I am just weary about modifying someone else's code without knowing what all the ramifications might be...

JeffCoachman
0
 

Author Comment

by:Senniger1
ID: 39614348
Jeff - I'm on Access 2010.
0
 

Author Comment

by:Senniger1
ID: 39614389
fyed - I think I tried that before and it didn't work for me.

Per your suggestion I tried again and when I use the following code I get a Run-Time error 2282 (The format in which you are attempting to output the current object is not available).

    DoCmd.OutputTo acOutputReport, "TimeOff", acFormatPDF, "c:\TimeOff.pdf", True

When I checked this out it says to install the PDF add-in, but I don't know what add-in I should install.

On another note, in Access 2010 when I highlight my report (rptCalendar_Month), and click the "Create and Attach to Email" button on the Acrobat toolbar in the "reate and Email"group, it works perfectly fine.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614402
Odd...? The addin should only be needed for Access/Office 2007...
...as PDF support was built into Access/Office 2010 and 2013...

OK, I'll leave you with fyed, I am sure he can get you sorted.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39614525
Jeff,

I was thinking the same think.  I saw the tag indicating 2010, so I assumed the PDF format would be available.

Senninger1, put that line in your immediate window and leave the format argument out.  Then hit enter.  It should ask you for a format, if so, are you able to select the PDF Format?
0
 

Author Comment

by:Senniger1
ID: 39614631
I've done as you've asked, but PDF format isn't an option.  

My choices are
Excel
HTML
RTF
SNP
TXT
XML
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39614662
Are you sure you are running Access 2010?  I don't have it on the computer I'm working from, so I cannot determine whether that has to be enabled in the Access Options or not.

If you are actually using 2007, then you can download the SaveAsPDF addin from microsoft here
0
 

Author Comment

by:Senniger1
ID: 39614686
Yes I'm certain I'm using Access 2010.  Here is my database.
Calendar.accdb
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 39614710
acFormatPDFn is available on my A2010 system - in your db

1
2
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39614722
Joe,

Thanks for looking at this, I don't have 2010 on the system I'm working at at the moment.  Is there any chance that there is a setting in the 2010 Access Options that would disable the acFormatPDF option?
0
 
LVL 75
ID: 39614772
Not that I am aware of. Notice in the first image ... after you type a comma, you don't immediately get intellisense.  You have to right click at that point and select List Properties and Methods, then you will see what is in the 2nd image - acFormatPDF
0
 

Author Comment

by:Senniger1
ID: 39614857
I get the same things on my screens as you do.  However when I run the following code, I get the Run-time Error 2282 - The format in which you are attempting tol output the current object is not available.

DoCmd.OutputTo acOutputReport, "TimeOff", acFormatPDF, "c:\timeoff.pdf", True

What do you get when trying to run this code from my database in your Access 2010?

Thanks!
0
 
LVL 75
ID: 39614889
I ran this test code

Private Sub btnDo3Things_Click()
DoCmd.OutputTo acOutputReport, "rptMain", acFormatPDF, "C:\Users\adi\Downloads\Test.pdf", True
End Sub

and it created a PDF.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39614912
do you have all of the service packs for Office 2010 installed?

Have you tried repairing your Office installation?

I also found this on one of the Microsoft support sites.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Senniger1
ID: 39615012
Yes I repaird both Office 2010 and Adobe Acrobat X Pro.  I also went to another user's PC and tried it and it didn't work.

I added the Dim acFormatPDF so now I don't get the error.  I get a "Select Output Format" popup, but I don't have PDF as a choice.

I checked my Add-ins, I have the following:
   Acrobat PDFMaker Office COM Addin
   Microsoft Access Package SolutionWizard 2010 COM Addin


Could it be I'm missing something under Tools, References?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39615066
Here is some standard code to output an Access 2010 report as a PDF and email it:

Private Function SendPDFReport() 
'Created by Helen Feddema 17-Jan-2010 
'Last modified 17-Jan-2010 
 
On Error GoTo ErrorHandler 
 
   Dim appOutlook As New Outlook.Application 
   Dim itm As Outlook.MailItem 
   Dim strFileName As String 
   Dim rpt As Access.Report 
   Dim strReport As String 
   Dim strCurrentPath As String 
   Dim strFileNameAndPath As String 
    
   strCurrentPath = Application.CurrentProject.Path & "\" 
   strReport = "rptProductPrices" 
   strFileName = "Product Prices.pdf" 
   strFileNameAndPath = strCurrentPath & strFileName 
    
   'Output report to PDF in current path 
   DoCmd.OutputTo objecttype:=acOutputReport, _ 
      objectname:=strReport, _ 
      outputformat:=acformatpdf, _ 
      outputfile:=strFileNameAndPath, _ 
      autostart:=False 
    
   'Create new mail message and attach text file to it 
   Set itm = appOutlook.CreateItem(olMailItem) 
   With itm 
      .To = "someone@xyz.com" 
      .Subject = "Daily report" 
      .Body = "Your message" 
      .Attachments.Add strFileNameAndPath 
      'To edit before sending 
      .Display 
      'To send automatically 
      '.Send 
   End With 
    
ErrorHandlerExit: 
   Exit Sub 
 
ErrorHandler: 
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description 
   Resume ErrorHandlerExit 
 
End Function

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39615238
I'm stumped, but will check my 2010 box when I get home.

I would remove the declaration.

Dim acFormatPDF   <==   Delete this

If you notices in the response to that post acFormatPDF is a predefined constant member of Access Constants Enumeration. It is defined as below:

    Const acFormatPDF = "PDF Format (*.pdf)"

Please type the following code (do not copy and paste) in the VBE environment to see if there is an auto-complete list after typing the dot:

    Debug.Print Access.Constants.acFormatPDF
0
 
LVL 75
ID: 39615327
You don't need Acrobat at all.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39615888
FWIW,
Like MX, I can use the pdf format fine in the file you uploaded.

Are you sure you did a *Full* install and not a *typical* install of Office 2010?
0
 

Author Comment

by:Senniger1
ID: 39616760
Helen_Feddema - I tried that code many times before.  Just tried it again and got the error "Error No 2282 - The format in which you are attempting to output the current object is not available.

fyed - I typed in the code Debug.Print Access.Constants.acFormatPDF and it did autocomplete properly.

I'm going to try uninstalling my Office and reinstalling again.  I'll be in touch.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39616949
Yes, sounds like some kind of general Office problem which somehow disabled the Print to PDF feature.
0
 

Author Comment

by:Senniger1
ID: 39617109
I uninstalled my MS Office 2013 and have confirmed I have everything installed except Microsoft InfoPath Microsoft SharePoint Workspace.

I'm still having the same problem.  I also tried logging in as another user on my PC to eliminate some sort of corruption in my profile, but this didn't help either.

Could it be I'm missing something under Tools, References?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39617163
You mentioned above that you are using 2010, but in your most recent post you mentioned 2013.  Do you have more than one version of Access running on this computer?
0
 

Author Comment

by:Senniger1
ID: 39617179
So sorry, that was a typo.  I am using Access 2010.

On another note...

I changed making it a PDF (which I will need) to RTF just to see how it flowed.  

Here is my code...

    Dim prnt As Printer
    Dim strRptName As String
    Dim strPDFPathNameMe As String
    Dim objOutlookApp As Object
    Dim objMailItem  As Object

    DoCmd.OutputTo acOutputReport, "rptCalendar_Month", acFormatRTF, "c:\timeoff.rtf", True

'Email the pdf file via Outlook
     Dim ol As Object
     Dim itm  As Object '(Creates the Outlook Application Object by accessing the MS Outlook COM Type Library)
     Set ol = CreateObject("Outlook.Application")
     Set itm = ol.CreateItem(0) '(Outlook a Mail Message)
     itm.To = "LMueller@Senniger.com"
     itm.Subject = "PTO Calendar Report"
     itm.Body = "Attached is the PTO Calendar"
     itm.Attachments.Add ("c:\TimeOff.rtf")

     itm.Send  '(to send)
     Set itm = Nothing '(Cleans up)
     Set ol = Nothing
   
It works, but brings me back to my original post.  I need it to create the PDF and send it, but not open Adobe (or Word using the RTF code).  So at this time, even if I could resolve why I can't print to PDF, it doesn't resolve my issue of it creating the file and emailing it so I can use Windows Schedule Task so it runs each day automatically.

Thanks!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39617192
Change the last argument of the OutputTo method to False to prevent it from opening the document after it is saved.
0
 

Author Comment

by:Senniger1
ID: 39617208
Great, that worked.

So apparently this will work great for me if I can ever figure out why I cannot print to PDF.

I'm taking an extra PC and trying to install it from scratch to see if there could be some software on my PC conflicting with this.  I'll be in touch.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39617255
Remember to do a "Full" install, specifically.
The default is a "typical" installation
0
 
LVL 75
ID: 39617304
Side note:
You don't need all the Registry hacking to temporarily the Printer from Default to another printer, then back again.

mx
0
 

Assisted Solution

by:Senniger1
Senniger1 earned 0 total points
ID: 39624918
Okay I have done everything I can think to do to correct this issue and it still doesn't work for me.

I uninstalled my Office 2010 and Adobe Acrobat X Pro and reinstalled.  I am certain I did a complete install of the Office 2010.

In researching I found the following link:
http://msofficeuser.com/pages/access/creating-a-macro-to-save-a-report-as-pdf-in-access-2010

I should be able to go to the Macro Builder window, for the “Add New Action” drop down menu, select the “ExportWithFormatting” macro (in Access 2007, you would choose the “OutputTo” macro). For the “Object Type” parameter, choose “Report”. For the “Object Name” parameter, choose “Customer Address Book”. For the “Output Format” parameter, choose the “PDF Format” option.

I don't have PDF Format as a choice.

I would think this has to factor in, however, I don't know why I don't have this.  I'm also contacted Adobe support, but they haven't been able to help me either.

I'm open to suggestions at this point.
0
 

Author Closing Comment

by:Senniger1
ID: 39644051
In the end a took a PC and reimaged it with an image that just has a clean copy of Windows XP on it.  I installed Office 2010 and Access DOES have the PDF Format as a choice.  Therefore, my database works on this PC.

At this point I'm going to add this PC to our network.  Since all I need (at this time) is to be able to run this code each day, I'm going to try and create a macro to run the code and then use Windows Schedule Tasks to run it each day at a particular time.

Many thanks to everyone for your help!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Resolve DNS query failed errors for Exchange
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

762 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

17 Experts available now in Live!

Get 1:1 Help Now