Email Access Report's as PDF

[DazSheps2001]

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

 

[Scott McDaniel]

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.

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

 

[DazSheps2001]

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

 

[Scott McDaniel]

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.

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;

' 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...

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

 

[Sergey Poberezovskiy]

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