Manual

In Excel 2002-2010 you have a option in the User Interface to mail a selection or worksheet
in the body of the mail.

Excel 2002-2003 : You find this button next to the save icon in the Standard toolbar
or use File>Send to>mail recipient.

Excel 2007-2010 : Microsoft hide it (It is not in the Office button>Send menu or in BackStage View)

But you can add this option to the QAT (Quick Access Toolbar)

1) Office Button > Excel Options
2) Customize
3) Choose "Commands Not in the Ribbon" in the "Choose Commands from" list
4) Select the command "Send to Mail Recepient"
5) Add
6) OK

Note: it is a toggle button and if you look closely, you'll see that that icon is depressed
when you see the envelope.

Important:
It is not working if your Outlook version is newer then your Excel version.
For example Excel 2002 and Outlook 2003
Error <Excel could not start the E-mail program>

If you use Conditional Formatting Icons or Data bars in your Excel 2007 workbook the
receiver will not see them in the mail.


VBA code

Important: MailEnvelope code is not working if your Outlook version is newer then your Excel version.
The two links below are working if you are in that situation

If you use Conditional Formatting Icons or Data bars in your Excel 2007-2010 workbook the
receiver will not see them in the mail.

I personally not like to use the VBA MailEnvelope code because it will use the Excel window instead of
creating a separate Outlook mail. When you use the code examples below you can use .Display instead of .Send in the code and it will create a separate Outlook mail and you can view it first if you want.

Mail worksheet in the body
http://www.rondebruin.nl/mail/folder3/mail2.htm

Mail Range or Selection in the body
http://www.rondebruin.nl/mail/folder3/mail4.htm


But the big advantage of MailEnvelope code is that if you have a logo or picture on your worksheet
it will also send them in the body of the mail, if you use the examples in the links above not.

Note : It is possible that Outlook must be your default mail program if you want to use the code.

Close Excel first and check out if Outlook is your mail program for Office.
Start>Settings>Control Panel....Internet options (Program Tab)
In Vista : Start>Default programs



Basic code examples


Note: it is also possible to add attachments with MailEnvelope code like this for example
    .Attachments.Add "C:\Users\Ron\Test\MyPdfFile.pdf"

Add this line in the With .Item part of the code


Selection or whole sheet

If the selection is one cell it will send the whole worksheet.
Read the information in the code.

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()
    Dim Sendrng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Selection

    'Create the mail and send it
    With Sendrng

        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "This is a test mail."

            ' In the "With .Item" part you can add more options
            ' See the tips on this Outlook example page.
            ' http://www.rondebruin.nl/mail/tips2.htm
            With .Item
                .To = "ron@debruin.nl"
                .Subject = "My subject"
                .Send
            End With

        End With
    End With

StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub

Fixed range or worksheet

Note: The worksheet/range not have to be active when you run the code.
Read the information in the code.

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Worksheets("Sheet1").Range("A1:B15")

    'Remember the activesheet
    Set AWorksheet = ActiveSheet

    'Create the mail and send it
    With Sendrng

        ' Select the worksheet with the range you want to send
        .Parent.Select

        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "This is a test mail."

            ' In the "With .Item" part you can add more options
            ' See the tips on this Outlook example page.
            ' http://www.rondebruin.nl/mail/tips2.htm
            With .Item
                .To = "ron@debruin.nl"
                .Subject = "My subject"
                .Send
            End With

        End With

        'select the original ActiveCell
        rng.Select
    End With

    'Activate the sheet that was active before you run the macro
    AWorksheet.Select

StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub