Print button help

[Guest]

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!

 

[Tom Ogilvy]

Turn on the macro recorder and print as PDF.

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

Ron de Bruin's site

 

[Guest]

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.

 

[Guest]

Noone?

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

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....

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[Ron de Bruin]

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