Ron de Bruin
Excel Automation

Mail Range/Selection in the body of the mail with MailEnvelope

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Important read this :

The code on this page is only working when you use Outlook as your mail program.
Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

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-2016 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 in the two links 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

Mail Range or Selection in the body

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.

Example 1 : Selection or whole sheet

If the selection is one cell it will send the whole worksheet, if more then one cell the selection

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()
'Working in Excel 2002-2016
    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."

            With .Item
                .To = "ron@debruin.nl"
                .CC = ""
                .BCC = ""
                .Subject = "My subject"
                .Send
            End With

        End With
    End With

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

End Sub

Example 2 : Fixed range or worksheet

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

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
'Working in Excel 2002-2016
    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

    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 test mail 2."

            With .Item
                .To = "ron@debruin.nl"
                .CC = ""
                .BCC = ""
                .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

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

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs