Macro: Convert to PDF & E-mail. Possible?

[wilro85]

I'm re-vamping a form that I made last year. Currently the function I'
evalutating works in the following way. Upon confirmation that all th
entered information is complete, copies are e-mailed to the relivan
project leaders.

Problem: The problem with this is the average size of these copies i
roughly 130kb. If I convert the summary page (which is all that need
to be sent out) into a PDF, the file size goes down to about 8kb.

Why I think this should be possible. Both Adobe Acrobat and Nitro PD
(we use both) have "Convert to PDF & Email" buttons that are on th
toolbar, however, since these are add-ons, they aren't in the VBA hel
file, and we all know how good the support is that Adobe offers.

If nothing else, is it possible for me to do a print macro using an
then send the output as an attachment?

Does anyone have any ideas on how to do what I'm trying to do

 

[wilro85]

Shall I assume by the silence this is not a possible task?

 

[tony h]

I have been meaning to play about with this but my initial reaction is
that it should be possible.

I have a PDF creator setup as a printer option. By making this the
active Printer or passing this to the activesheet.printout I can get
the PDF to be created. It is then a fairly simple matter of using code
to attach the file to an email.

An issue I have here (which you may not have) is that in creating the
PDF a document profiling form comes up (part of our document management
system) for whcih the obvious way to handle it is to use sendkeys.

This is (sort of) fine if it is just you using the macro. But otherwise
you need to wrap this in code to make sure the window is created and
active before using the send keys.

I am sure there must be a better PDF creator for using in this sort of
environment.
I would look for one with an API rather than using it as a printer
option.

Probably not a huge amount of help.
regards

 

[tony h]

if you do a search for "create pdf vba) you get quite a few useful
articles. The first one to jump out at me is this
http://p2p.wrox.com/archive/vba_excel/2002-11/37.asp

Clearly this has a programmatic interface.

Also just discovered our PDF creator has a command line version which
means that I could save and close my workbook, do an application.run
command_line to create the pdf and then pick it up and email it.

regards

got to go I have a document to finish

 

[SA]

Wilro:

Stop by our web and look in the MS Excel developer tools area and you'll
find our XL PDF Wizard add in. It allows you to use any one of 10 different
PDF printer drivers (including Acrobat and some other very in-expensive
drivers) to automate create of PDF documents (including bookmarks etc.) from
XL worksheets or workbooks. It also allows you to merge in Word documents
(with bookmarks,) or external PDF files. You can also reference the Add
In in any workbook and use its properties and methods to create your own PDF
creation jobs. So yes, it can be done.

Hope this helps

 

[SA]

Tony:

Stop by our web and look in the MS Excel developer tools area and you'll
find our XL PDF Wizard add in. It allows you to use any one of 10 different
PDF printer drivers (including Acrobat as well as some other very
inexpensive drivers) to automate creation of PDF documents (including
bookmarks etc.) from XL worksheets or workbooks. It also allows you to
merge in Word documents (with bookmarks,) or external PDF files. You can
also reference the Add In in any workbook and use its properties and methods
to create your own PDF creation jobs. So yes, it can be done.

Hope this helps