Note: The information from this page is also published on MSDN, if you want to read it there go to
http://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx

And If you not like VBA code have a free mail add-in that also have a option to create/send PDF files.
http://www.rondebruin.nl/mail/add-in.htm


Summary, Tips and Warnings

A new feature of Microsoft Excel 2007 (with Microsoft Office Service Pack 2 installed) is the
ability to create and mail Acrobat Reader PDF files. If you do not wish to install Microsoft
Office SP2, you can install just the add-in. You can download it here :
2007 Microsoft Office Add-in: Microsoft Save as PDF

After the add-in is installed you can use the code below or do a manual Save As PDF.
Office Button >Save As ….PDF
Office Button >Send ….PDF

Note: In Excel 2010 the big round Office Button is replaced by File


Tips / warnings :

1) If you have also installed Acrobat Reader you can change OpenAfterPublish in the code to
    True to open the PDF file after you create it.
2) The mail code example is not working with Outlook Express or Windows Mail.
3) If you set OpenAfterPublish in the code to True then you can do a manual send in
    Acrobat Reader (also with Outlook Express or Windows Mail).
4) If there is no printer installed the add-in will not work. You only have to install a printer driver
    of one of the printers in the default printer list, you not need a real printer to use the add-in.
5) When you use a hyperlink to another place in the workbook or if you use the Hyperlink
    worksheet function the hyperlinks are not working in the PDF.



Example code to Create and Mail PDF files

You can copy/paste the code from the TXT file to your own workbook or download the example workbook,
I suggest that you download the example file below.

Download a workbook with all the code to test the macros
Download PDF-Examples.zip

Or:

Copy the macros and functions from this TXT file in a Standard module of your workbook.
See this page if you not know how how to do this.
http://www.rondebruin.nl/code.htm

In the workbook you find three modules with eigth macros and three functions.
Note: You not have to edit or change the functions in the module named "FunctionsModule"


Tip: Check out also my Sheet template for a easy way to create and mail PDF's
http://www.rondebruin.nl/mail/outlooksheettemplatepdf.htm



The macros to create a PDF in the "CreatePDF" module

Macro 1 : Create a PDF of the workbook
Macro 2 : Create a PDF of the ActiveSheet or selected sheets
Macro 3 : Create a PDF of the selection or range
Macro 4 : Create a PDF with every sheet with a specific sheet level name

Tip: In Macro 2 you can also use Sheets("Sheet3") instead of ActiveSheet in the code.
The worksheet not have to be active then to create the the PDF.

The first three macros calls a function named RDB_Create_PDF
Macro four calls a function named Create_PDF_Sheet_Level_Names
 
You see there are four arguments in both functions.

1: What do we want to publish  (In macro four this is the sheet level name)
2: Path/File name of the PDF file, when you use "" you can enter a file name in the GetSaveAsFilename dialog
3: Overwrite the file you choose in the GetSaveAsFilename dialog if it already exist :True or False
4: OpenAfterPublish : True or False

In the macros in the workbook you see that we use one code line to create the PDF


This will create a PDF of the ActiveWorkbook and overwrite the file if it exist and open the PDF.
FileName = RDB_Create_PDF(ActiveWorkbook, "", True, True)

Note: For a fixed file name and overwrite it each time you run the macro use this
FileName = RDB_Create_PDF(ActiveWorkbook, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)


To create a PDF of the sheets with a sheet level named range "addtopdf" and
overwrite the file if it exist and open the PDF.
FileName = Create_PDF_Sheet_Level_Names("addtopdf", "", True, True)

Note: For a fixed file name and overwrite it each time you run the macro use this
FileName = Create_PDF_Sheet_Level_Names("addtopdf", "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

How do I add sheet level names to the sheets I want ?

1:  Ctrl-F3 to open the Name manager
2:  Click on New
3:  Enter addtopdf as name
4:  Change scope to the sheet you want
5:  Refers to is not important because we only use the name
6:  OK
7:  Repeat the steps above for every sheet you want in the PDF.




The macros that create and mail the PDF in the "CreatePDFMail" module

Important : The code is only working if you use Outlook
Note : Example 5 in this module wil create a PDF of each sheet that have a mail address in A1
           and Mail this sheet to the address in A1.

The macros in this module are almost the same as in the "CreatePDF" module
I replaced this two comments lines in the macros

            'Ok, you find the PDF where you saved it
            'You can call the mail macro here if you want

With this code line :

RDB_Mail_PDF_Outlook Filename, "ron@debruin.nl", "This is the subject", _
        "See the attached PDF file with the last figures" _
            & vbNewLine & vbNewLine & "Regards Ron de bruin", False

    'If you also want to delete the PDF file from your system after it create the mail add also this line:
    Kill FileName


You see there are five arguments in the function call.

1: FileName (do not change this)
2: To who do we want to send the mail ?
3: What is the subject ?
4: What do we want in the body of the mail ?
5: Do we want to Display (False) the mail or send it directly(True)

Also set the last argument to False of the code line that create the PDF so it not open the PDF.
FileName = RDB_Create_PDF(ActiveWorkbook, "", True, False)


For much more mail code examples visit my mail page
http://www.rondebruin.nl/sendmail.htm