Complicated Macro (idiot's guide)

[CraigFord]

Afternoon

new to this forum and very new to macros

im looking for a macro do do something very specific (it may not be possable)

i have an excel workbook which has 2 tabs
it is saved in a file on a server
file name is in E5

im looking for 2 individual macros

1) Save_Email (save and print)
2) Save_Print (save and email)

these will be two buttons that my engineers can choose which one to select.

the save function should save the document as a pdf into the same file as the workbook and the file name should be as E5

the email function should open up the window that allows it to be emaild as a PDF attachment

the print function should open up the print box

i dont even know if this is possable, if it is please help.

 

[CraigFord]

apologies im using excell 2007

 

[CraigFord]

sorry small mistake above.

1) Save_Email (save and email)
2) Save_Print (save and print)

 

[CraigFord]

Anybody?

 

[Zaf]

Do you intend to do this through microsoft outlook?

If so you will need to reference the "Microsoft Outlook 12.0 Object Library".

Have a look at...

http://www.outlookcode.com/article.aspx?ID=40

 

[Comfy]

Your probably looking at somthing like:
http://download.cnet.com/Excel-to-PDF-Converter/3000-18483_4-10310370.html

 

[CraigFord]

Your probably looking at somthing like:
http://download.cnet.com/Excel-to-PDF-Converter/3000-18483_4-10310370.html

i was hoping to do it through excel's own PDF converter, we have lots of people working off different machines.

i can make it export to PDF & save using the macro record function, the result is below, however i cant make this add a cell refrence as the PDF name.

Sub Macro2()
'
' Macro2 Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\craigford\Desktop\101009891.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,3,,,TRUE,,FALSE)"
End Sub

 

[Comfy]

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\craigford\Desktop\" & Cells(5,5).Value & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,3,,,TRUE,,FALSE)"
End Sub

This will save onto your desktop, you can change the save directory as you see fit.

 

[CraigFord]

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\craigford\Desktop\" & Cells(5,5).Value & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,3,,,TRUE,,FALSE)"
End Sub

This will save onto your desktop, you can change the save directory as you see fit.

this works perfectly, however when i save and close the document, the macro dosent work,