Print button help
 

I am trying to creat a print button on one of my worksheets. The button will
print worksheet 1, 2, and 3. This part was simple I just made this macro:
Sub print_worksheet()
Worksheets("Kund data").PrintOut Copies:=1
Worksheets("Schakt dörr").PrintOut Copies:=1
Worksheets("Korg dörr").PrintOut Copies:=1

However, I also want the macro to save the 3 worksheets as pdf files and
open a new e-mail where those files are attached. Right now I can't even get
the 3 sheets to convert to PDF with a macro. I have acrobat Professional
installed and can print to pdf so I asume there is some way to do it with a
VBA macro... Help please!

Print button help
 

Turn on the macro recorder and print as PDF.

for mailing:
http://www.rondebruin.nl/sendmail.htm

Ron de Bruin's site

--
Regards,
Tom Ogilvy


"Calle" wrote in message
...
I am trying to creat a print button on one of my worksheets. The button
will
print worksheet 1, 2, and 3. This part was simple I just made this macro:
Sub print_worksheet()
Worksheets("Kund data").PrintOut Copies:=1
Worksheets("Schakt dörr").PrintOut Copies:=1
Worksheets("Korg dörr").PrintOut Copies:=1

However, I also want the macro to save the 3 worksheets as pdf files and
open a new e-mail where those files are attached. Right now I can't even
get
the 3 sheets to convert to PDF with a macro. I have acrobat Professional
installed and can print to pdf so I asume there is some way to do it with
a
VBA macro... Help please!

Print button help
 

hi!
Well it's not that easy. I did that and got this code:

Application.ActivePrinter = "Adobe PDF på Ne03:"
Worksheets("Kund data").PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF på Ne03:", Collate:=True

This changes the printer to adobe pdf and prints but the problem is:

1.Is "Ne03" the same on all computer?
2. No it wants me to select the file name on the pdf but I want the macro to
do that.
3. How do I set the default printer again. After runing this macro it's set
to adobe pdf.

Print button help
 

Noone?

Print button help
 

Try something along these lines.

s = Application.ActivePrinter


for i = 1 to 9
err.clear
On error resume Next
Application.ActivePrinter = "Adobe PDF på Ne0" & i & ":"
if err.Number = 0 then exit for
On error goto 0
Next
On Error goto 0
ActiveSheet.Printout PrintToFile:=True, PrToFileName:="ABC.PDF"

Application.ActivePrinter = s



--
Regards,
Tom Ogilvy

"Calle" wrote in message
...
Noone?

Print button help
 

hi Tom!
That's a clever piece of cod. I will try it as soon as possible. thx.

Print button help
 

ok. I tried the makro and it almost worked.... there was one line I had to
change to avoid an error message:

I changed ActiveSheet.Printout PrintToFile:=True, PrToFileName:="ABC.PDF"

to: Worksheets("Kund data").PrintOut Copies:=1
however no I am not aloved to set the file name in the makro. However it
might still work with the mail part since I now will have to get the mail
makro to feel what pdf was last created.... or something like that....

Print button help
 

the only problem is that adobe keeps opening everytime i creat a pdf
file....is there a way to buypass this?

Print button help
 

Is there a way to stop adobe acrobat to open up every time I print a file to
adobe?

Print button help
 

Ok, I managed to fix this problem.

Now the question is how do I get the mail makro to know what files to send.
Is there anyway to record what three pdf files I just created?

Print button help
 

Excel 2007 is great with the pdf option
See
http://www.rondebruin.nl/pdf.htm

I finish the first beta version of the Excel 2007 SendMail add-in this week with this option


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
Turn on the macro recorder and print as PDF.

for mailing:
http://www.rondebruin.nl/sendmail.htm

Ron de Bruin's site

--
Regards,
Tom Ogilvy


"Calle" wrote in message ...
I am trying to creat a print button on one of my worksheets. The button will
print worksheet 1, 2, and 3. This part was simple I just made this macro:
Sub print_worksheet()
Worksheets("Kund data").PrintOut Copies:=1
Worksheets("Schakt dörr").PrintOut Copies:=1
Worksheets("Korg dörr").PrintOut Copies:=1

However, I also want the macro to save the 3 worksheets as pdf files and
open a new e-mail where those files are attached. Right now I can't even get
the 3 sheets to convert to PDF with a macro. I have acrobat Professional
installed and can print to pdf so I asume there is some way to do it with a
VBA macro... Help please!