Automatically Save an Access Report as a PDF and attach it to an email

What PDF do u have in mind? The only one that I know is to use PDF995. This allows u to then specify a filename, print and u can then attach to the email

Add the following into a module


'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
   "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpDefault As String, _
   ByVal lpReturnedString As String, ByVal nSize As Long, _
   ByVal lpFileName As String) As Long

'Write settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
   "WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpString As Any, _
   ByVal lpFileName As String) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)

    ' Runs an Access report to PDF995 to create a pdf file from the report.
    ' Input parameters are the name of the report within the current database,
    ' the path for the output file, and an optional criteria for the report
   
    ' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
    ' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
    ' the most reliable indication that PDF995 is done writing the pdf file.
   
   
    ' Note: The application.printer object is not valid in Access 2000
    ' and earlier. In that case, set the printer in the report to pdf995
    ' and comment out the references herein to the application.printer
   
    Dim syncfile As String, maxwaittime As Long
    Dim iniFileName As String, tmpPrinter As Printer
    Dim outputfile As String, x As Long
    Dim tmpoutputfile As String, tmpAutoLaunch As String
   
'***** IMPORTANT - SEE THIS *****
    ' set the location of the PDF995.ini and the pdfsync files
    iniFileName = "C:\Program Files\pdf995\res\pdf995.ini"
    syncfile = "c:\documents and settings\all users\application data\pdf995\pdfsync.ini"
   
    ' build the output file name from the path parameter and the report name
    If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
    outputfile = destpath & reportname & ".pdf"
   
    ' PDF995 operates asynchronously. We need to determine when it is done so we can
    ' continue. This is done by creating a file and having PDF995 delete it using the
    ' ProcessPDF parameter in its ini file which runs a command when it is complete.
   
    ' save current settings from the PDF995.ini file
    tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
    tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)
   
    ' remove previous pdf if it exists
    On Error Resume Next
    Kill outputfile
    On Error GoTo Cleanup
   
    ' setup new values in PDF995.ini
    x = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)
   
    ' change the default printer to PDF995
    ' if running on Access 2000 or earlier, comment out the next two lines
    Set tmpPrinter = Application.Printer
    Application.Printer = Application.Printers("PDF995")
   
    'print the report
    DoCmd.OpenReport reportname, acViewNormal, , strcriteria
   
    ' cleanup delay to allow PDF995 to finish up. When flagfile is nolonger present, PDF995 is done.
    Sleep (10000)
    maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
    Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
        Sleep (10000)
        maxwaittime = maxwaittime - 10000
    Loop
   
    ' restore the original default printer and the PDF995.ini settings
Cleanup:
    Sleep (10000)
    x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
    On Error Resume Next
   
    ' if running on Access 2000 or earlier, comment out the next line
    Application.Printer = tmpPrinter

End Sub

Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
   
    Dim x As Long
    Dim sDefault As String
    Dim sRetBuf As String, iLenBuf As Integer
    Dim sValue As String
   
    'Six arguments
    'Explanation of arguments:
    'sSection: ini file section (always between brackets)
    'sEntry : word on left side of "=" sign
    'sDefault$: value returned if function is unsuccessful
    'sRetBuf$ : the value you're looking for will be copied to this buffer string
    'iLenBuf% : Length in characters of the buffer string
    'sFileName: Path to the ini file
   
    sDefault$ = ""
    sRetBuf$ = String$(256, 0)   '256 null characters
    iLenBuf% = Len(sRetBuf$)
    x = GetPrivateProfileString(sSection, sEntry, _
               sDefault$, sRetBuf$, iLenBuf%, sFilename)
    ReadINIfile = Left$(sRetBuf$, x)

End Function



Save it as PDFPrintng or something
now u can call the function

pdfwrite

passing in the name of your report and the destination where u want the PDF to live
note the paths in the function, u need to set that to where u have it installed. If in a common place on all users then u got nothing to worry about


Now in order to send emails, u can use outlook automation or cdo. If the former, and u use office2003 u may get security warnings

see the emails sending threads here

https://www.experts-exchange.com/questions/21866975/How-do-I-get-Access-to-send-an-email-with-a-txt-file-attached-without-user-intervention.html

create pdf report
https://www.experts-exchange.com/questions/10289691/Printing-to-Adobe-Acrobat-PDF-Writer.html



generating the email with attachment

http://support.microsoft.com/?kbid=209948

Greetings and good morning gents...
I just went through this...and I think my saving grace was the fact that I had a full blown copy of Adobe. But what I found was that the Lebans method worked really well. And although I think there are still some questions out there as to whether or not some Dll is used legally, I am very satisfied with my results. I'd be more than happy to cut and paste in here....but I want to yield to Rock and Cap for now. I'll watch the thread.
J

go for it Jeff!!!
dont forget the email part though

one button click to do all :)

why inhibit yourself jeff?

I do not think I have PDF995 - I have never hear dof it, and I don't find any files when I search for it.

The links from capricorn1 look good in theory, but I don't think I need to go to the hasse of changing the defualt printer.  I have Adobe PDF saved as the printer in the report's page settings, and that seems to be sufficient.

I added the following code:

Private Sub Command0_Click()
Dim strNewFileName As String
strNewFileName = "Test"
Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName", strNewFileName)
DoCmd.OpenReport "rptClaimsHxAll", acViewNormal
End Sub

I get a "function or sub not defined error"

What am I missing?

If you're going to use the PDFWriter API, you need to make sure you have that reference selected.

Inside VBA, Tools/References

Do you have Adobe Writer selected in there?

J

where did u search for pdf995

first hit on google when searching PDF995 yields the website!
http://www.pdf995.com/

with adobe, the only way I know is if u save as a postscript file then convert the .ps to .pdf

I don't have Adobe Write as an option in references.  All I have is AdobePDFMakerX.  I tried selecting that one, but it did not help.

I looked at the PDF995 website; however, i don't think my company would permit me to download that software onto all of the user's machines.  

ok, so u have adobe then
have u tried Jeff's idea?

a quick search on EE, I found this
https://www.experts-exchange.com/questions/10289691/Printing-to-Adobe-Acrobat-PDF-Writer.html

very brief look (one api call check), it appears to be what Jeff is doing

rocki,
that link is already posted in this thread

oh, didnt see that, its so early on. sorry

Here's what I ended up using:

To create PDF:

https://www.experts-exchange.com/questions/21931037/Converting-report-to-PDF-problem.html

Generating e-mail:

http://support.microsoft.com/?kbid=209948


The othe rlink posted here (https://www.experts-exchange.com/questions/10289691/Printing-to-Adobe-Acrobat-PDF-Writer.html) relies on changes to the win.ini file.  Although the file updated successfully using the code provided, it appears that the win.ini file is not used by windows when generating the pdf file, so it seems to be an obsolete method.