Email Access Report's as PDF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DazSheps2001 - 11 Jun 2007 15:30 GMT

Hi,

I am upgrading an MS A97 database to A2k. due to an upgrade from NT to XP. I
need to change/update the code for auto PDF'ing the reports that are sent via
Outlook. Currently it saves the files as PDF to a temp location and then
opens an email and attached them.

I cannot get Access to auto save them as PDF in XP A2k? Can somebody help?
(Ps I cannot install any other programs so it has to be done in current
environment, I do have Acrobat 7 standard installed.

Many Thanks

Signature

Darryl Shephard

Reply to this Message

Scott McDaniel - 16 Jun 2007 12:57 GMT

>Hi,
>
[quoted text clipped - 6 lines]
>(Ps I cannot install any other programs so it has to be done in current
>environment, I do have Acrobat 7 standard installed.

Can you post the code you're using to save the PDF? AFAIK, there are no limitations in 2000 that would prevent this.

>Many Thanks

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

DazSheps2001 - 18 Jun 2007 09:17 GMT

Hi,

Thanks, I think I have done it now by manually changing the settings in the
PDF writer to default to C: root Directory and choosing not to preview the
PDF at all. However it would be good if I could change this in the code and
then reset back to the PDF defaults in the code, as currently it means that
we have to make the manual change on any PC before they can use this Database
to send reports.

The Code I have used is;

' To set the default priner to the PDF Writer
DoCmd.OpenReport "Consultant Key Measures Scores", acViewPreview
Application.Reports("Consultant Key Measures Scores").Printer =
Application.Printers("Adobe PDF")
' Prints to PDF Writer, which saves to C: without preview
DoCmd.PrintOut
' Closes Access Preview above
DoCmd.Close
' Rename and move File for sending in Auto email.
Dim oldname
Dim newname
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
oldname = "C:\Consultant Key Measures Scores.pdf"
newname = "T:\mi\Adobe Acrobat Files\Temp\Weekly Key Measures Report.pdf"
If fso.fileExists(newname) = True Then fso.deletefile (newname)
fso.CopyFile oldname, newname
If fso.fileExists(oldname) = True Then fso.deletefile (oldname)
Set fso = Nothing
' Replace Default Printer
DoCmd.OpenReport "Consultant Key Measures Scores", acViewPreview
Application.Reports("Consultant Key Measures Scores").Printer =
Application.Printers(0)
DoCmd.Close

Many thanks for any assistance you can give, and hope this helps anybody
else...

Signature

Darryl Shephard

> Can you post the code you're using to save the PDF? AFAIK, there are no limitations in 2000 that would prevent this.

Reply to this Message

Scott McDaniel - 26 Jun 2007 15:12 GMT

>Hi,
>
[quoted text clipped - 4 lines]
>we have to make the manual change on any PC before they can use this Database
>to send reports.

I don't use Adobe, so I couldn't tell you how to change the PDF Writer defaults via code, but Adobe is pretty well
programmable so I'm sure you could do this. Perhaps you could try the Adobe website/knowledgebase/forums to see if this
has been discussed before.

>The Code I have used is;
>
[quoted text clipped - 25 lines]
>Many thanks for any assistance you can give, and hope this helps anybody
>else...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

Sergey Poberezovskiy - 02 Jul 2007 04:28 GMT

DazSheps2001,

You will need to change registry just before the report print.
The following is an extract from a procedure I use to do this:

Private Const REG_SZ As Long = 1

Private Function printToPdf(byVal vsReportName As String, byval vsFileName
as string, byval vsFilter as string) As String
Const sPdf As String = ".pdf"
Const sSection As String = "Software\Adobe\Acrobat PDFWriter"
Dim nErrNumber As Long
Dim sErrSource As String
Dim nOrientation As Long
    ' first open the report
    DoCmd.OpenReport vsReportName, acCiewPreview, , vsFilter
    If StrComp(Right(vsFileName, 4), sPfd, vbTextCompare) <> 0 Then
        vsFileName = vsFileName & sPdf
    End If
    '    Ensure that invalid characters do not appear in the file name
    '    m_TempPath is preset as a module variable upon application init
    vsFileName = m_TempPath & "\" & Replace(vsFileName, "/", "-")
    '    now set registry in HKEY_CURRENT_USER
    SetKeyValue sSection, "PDFFilename", vsFileName, REG_SZ
    SetKeyValue sSection, "bExecViewer", 0, REG_SZ

    '    now change the report's settings
    On Error Resume next
    With Reports
        '    need to record printer's orientation
        nOrientation = .Printer.Orientation
        Set .Printer = Printers("Acrobat PDFWriter")
        If .Printer.Orientation <> nOrientation Then
            .Printer.Orientation = nOrientation
        End If
    End With
    '    now check the error
    With Err
        nErrNumber = .Number
        nErrSource = .Source
    End With
    '    reset default error handling
    On Error GoTo 0
    If nErrNumber Then
        '    do not save the settings
        DoCmd.Close acReport, vsReportName, acSaveNo
        '    propagate the error back to the caller
        Err.Raise nErrNumber, sErrSource, "Acrobat PDFWriter is not installed!"
    End If
    DoCmd.PrintOut
    printToPdf = vsFileName
    '    close the report
    DoCmd.Close acReport, vsReportName, acSaveNo
End Function

There is no need to reset the registry keys after the report is printed -
they are automatically revert back to their defaults (I think pdf writer
takes care of it)

Hope this helps

> Hi,
>
[quoted text clipped - 8 lines]
>
> Many Thanks

Reply to this Message