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

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

Problem: The problem with this is the average size of these copies is roughly 130kb. If I convert the summary page (which is all that needs 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 PDF (we use both) have "Convert to PDF & Email" buttons that are on the toolbar, however, since these are add-ons, they aren't in the VBA help 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 and then send the output as an attachment?

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

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

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:

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
--
SA
ACG Soft
http://www.groupacg.com

"tony h" <tony.h.2a7haz_1151655302.9712@excelforum-nospam.com> wrote in
message news:tony.h.2a7haz_1151655302.9712@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=556708
>

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

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
ACG Soft
http://www.groupacg.com


"wilro85" <wilro85.2a4w8z_1151534703.9091@excelforum-nospam.com> wrote in
message news:wilro85.2a4w8z_1151534703.9091@excelforum-nospam.com...
>
> I'm re-vamping a form that I made last year. Currently the function I'm
> evalutating works in the following way. Upon confirmation that all the
> entered information is complete, copies are e-mailed to the relivant
> project leaders.
>
> Problem: The problem with this is the average size of these copies is
> roughly 130kb. If I convert the summary page (which is all that needs
> 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 PDF
> (we use both) have "Convert to PDF & Email" buttons that are on the
> toolbar, however, since these are add-ons, they aren't in the VBA help
> 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 and
> then send the output as an attachment?
>
> Does anyone have any ideas on how to do what I'm trying to do?
>
>
> --
> wilro85
> ------------------------------------------------------------------------
> wilro85's Profile:
> http://www.excelforum.com/member.php...o&userid=26935
> View this thread: http://www.excelforum.com/showthread...hreadid=556708
>