Initiate PDF from VBA Code

[Mark Bruso]

Hi,

I would like to have a some code that will:

- Open a report in Preview Mode (got that)
- Print the report in the PDF format, (changing the printer driver to Adobe
Acrobat)
- Save the document to directory set programatically using variables.
- Attach the document to an email. Address the email from a data in the
database.
- Send the e-mai.

Can this be done. Any suggestions on how to start?

 

[SA]

Mark:

Stop by our web and look at our PDF and Mail Library for Access. You can do
exactly what you are trying to do with our library.

 

[Ian Claridge]

After a long trawl I have just managed to crack this one
for myself!

IF you have a programme that can create pdf's already
then the back of the work has been broken (I've found
that acrobat pro is best as it can be configured to write
the pdf to a file name sent programmatically without any
real fuss).

Next just set up your report to print to the default
printer and then you can use the command...

application.printer = application.printers
(Pdfprintername) } where pdfprintername is a string
containing the name of the pdf printer e.g. "acrobatPDF"
reportname.DoCmd.Printout

You need to add a short pause to allow the application to
make the pdf file.. i used a msgbox with an OK button

Then you can use code available from microsoft to open
outlook, address it and add attachments. It goes
somewhere along the lines of...

Set myOLapp = CreateObject("Outlook.Application")
Set myItem = myOLapp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
myAttachments.Add FullAttachmentName, _
olByValue, 1, AttachmentLabel ' where fullname is
a string containing the name (including drive and path)
of the file to attach & AttachmentLabel is a string
containing the name you want to appear against the
attachment when it is shown in the email message
myItem.To = SendAddress 'string containing a valid email
address
myItem.CC = CCAddress ' String with any CC list
myItem.Subject = Subject ' String to add a subject
myItem.Body = MessageText ' String to add message body
myItem.Display ' shows the email message ready for
sending & allows editing/correction

This works great with office XP but i don't know if it
does with previous office versions.

Another tip - there are various "try before you buy" pdf
creators available that you may be able to use without
resorting to the hefty asking price of acobat!

 

[Andy Williams]

I use a dummy printer that prints to a file with a .PS extension, then spit
that out into a watched file in Distiller. Then I open up an Outlook
session, attach the result, and send. Works great.

The following KB article might help if you're not familiar with the Outlook
object model:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209948

Feel free to e-mail me if you want me to send you the code that I use.