Sending a report to PDF using VBA

  • Thread starter Thread starter WMD
  • Start date Start date
W

WMD

Cheryl,

Thanks for the feedback. I like the adobe suggestion and
will like to implement that. I have adobe writer and
reader and the user community has at minimum adobe reader.

I tested it manually with a report and the results were
exactly as I would like. However, I do have one challenge.
That challenge is this: I don't want the users to have to
take a manual step to save the report as a pdf. The
challenge is I am not sure what the object is to select
the adobe driver as the printer for this particular report.

I have checked my access dev handbook and there is code to
control your printer but I am wondering if there is a
different route that will get me to the adobe driver.

Thanks
 
WMD,

Here's the way I do it. Create a printer that prints to a file. Set the
report to print to this printer by default. When the report prints, the
user will be prompted for a filename. You can send this using SendKeys.
Put it in a watched folder, wait a few seconds, then attach it to an e-mail
message. Simple right?

Here's some sample code pulled straight out of one of my apps...

-----------------------------------
Dim stDocName As String

stDocName = "QuoteEMail"
SendKeys ("c:\quotes\in\" & Forms!QuoteMenu!txtQuoteNumber & ".ps~")
DoCmd.OpenReport stDocName, acViewNormal, , "QuoteNumber=" &
Forms!QuoteMenu!txtQuoteNumber
DoCmd.Close acForm, "PrintFaxQuote"

DoCmd.Hourglass True
Dim sngStart As Single, sngEnd As Single
sngStart = Timer
Do Until Timer > sngStart + 3
Loop
DoCmd.Hourglass False

SendMessage "c:\quotes\out\" & Forms!QuoteMenu!txtQuoteNumber & ".pdf"

-----------------------------------
Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

' Set the Subject of the message.
.Subject = "Quote #" & Forms!QuoteMenu!Text30

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

.Display

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
 
Andy,

That is nice, however, I don't have the permissions to
create printers that print to files in the environment
that I am working in. So, I will need to send the report
directly to the adobe writer driver. I plan on doing this
as part of a command button function. If I can find
something simpler than the code that is my access
developer's handbook (uses the prtdevname/mode) properties.

Thanks for the snippet...I will bank it for future use.

wmd.
-----Original Message-----
WMD,

Here's the way I do it. Create a printer that prints to a file. Set the
report to print to this printer by default. When the report prints, the
user will be prompted for a filename. You can send this using SendKeys.
Put it in a watched folder, wait a few seconds, then attach it to an e-mail
message. Simple right?

Here's some sample code pulled straight out of one of my apps...

-----------------------------------
Dim stDocName As String

stDocName = "QuoteEMail"
SendKeys ("c:\quotes\in\" & Forms!QuoteMenu! txtQuoteNumber & ".ps~")
DoCmd.OpenReport stDocName,
Click to expand...
acViewNormal, , "QuoteNumber=" &
 
We make a similiar product and are currently giving away source code at the
regular price.

Don't use SendKeys. Most PDF creation involves setting some registry keys
or INI entries and then just printing to the PDF driver.

HTH,
Mark
RPT Software
http://www.rptsoftware.com
 
Back
Top