Need Help

Hi all,

I`m Dave and i`m new to both this forum and Excel Programming. i have made some simple excel programmes before but need a lot of help on the next project!

ill start from the top and hopefully it all makes sense.

i currently putting together a new ordering system for the company i work for and need a macro to operate as follows:


in the active worksheet:

1) save a copy of the work sheet as a pdf and always in the same specified file location
2) when saving the pdf file i would like it to be saved as what ever is entered into cell "B22"
3) auto print a copy of the pdf to the default printer
4) auto attach a copy of the pdf to an email (outlook)
5) auto email subject to be the contents of cell "B22" plus Cell "C22" followed by order in brackets - (format example below)
6) content of email to read "please see attached order"
7) auto close excel without saving



Cell B22 Contains the order number = P510001 (this is a manual input updated on every order)
Cell C22 contains the job reference = Downs Road (this will be fixed)

Example of Subject = P510001 - Downs Road (Order)



Currently i have managed to find 2 different programme on the net that are similar to what i want:

the first one:




Sub Process()

Dim WSName As String, CName As String, Directory As String, savename As String

WSName = "Order"

CName = "B22"

Directory = "C:\Documents and Settings\dave\Desktop\Orders\"

savename = Sheets(WSName).Range(CName).Text
If Directory = "" Then Directory = CurDir & "\"

On Error GoTo errorsub:
ActiveWorkbook.SaveAs FileName:=Directory & savename & ".xls"
Exit Sub

errorsub:
Beep
MsgBox "Changes not saved!", vbExclamation, Title:=savename & ".xls"


End Sub


This saves a copy of the active work sheet in a specified location with the file name as what ever is in cell B22. the problem with this programme is it doesnt save as a pdf but only as an .xls........ being new to this i tried changing the parts that say .xls to .pdf and the process worked but the pdf documents wouldnt open they just through up an error message. not sure why it does this or how to fix it... maybe someone could explain.



the second code i found was from the following link:

http://www.rondebruin.nl/pdf.htm

i downloaded the trial excel document and the option to turn to pdf and mail worked well but its to complex for me to adapt. (The second Example 2)



what i am trying to ask is there anyone, who doesn`t mind spending the time, that is able to adapt/combine the codes to make it work as the 7 steps i mentioned above???

or am i asking way to much from someone?

thanks

Dave