Excel VBA - E-Mailing a Selection, Range or whole worksheet (as Email Body)

Hi Everyone,

I will start straight away with the code as Purpose is very much clear from the Title. Sending worksheet as Attachment is one way of doing that. But there are some extra steps involved and presentation is another reason why I am choosing this code over manual steps. The following code will mail the desired table or ranges or the whole worksheet to the desired recipients. So here are the the codes:-

1. For Selection 
Select a range of cells to be mailed and run the following code:-

       
Sub Mail_Selection_Worksheet()
    ActiveWorkbook.Save                           'Saving workbook to counter
                                                  '"the MailEnvelope method of Worksheet object failed" error
    
    With ActiveSheet.MailEnvelope                 'Creating an Email Environment.
    .Introduction = "Add your Introduction here."
    
        With .Item                                'Creating a MailItem
            .To = "Add Second Recipient Here"
            .BCC = "Add First Recipient Here"
            .CC = "Add Third Recipient Here"
            .Attachments.Add "Path of the file or Document"
            .Subject = "Enter your Subject here."
                                                  'The Body of the Email would be the selected content of the activesheet.
            
            ActiveWorkbook.EnvelopeVisible = True 'OR False - The line below will show/hide the Envelope on the workbook.
            .Send                                 'Or remove .send to pull the trigger yourself using Envelope interface.
        End With

    End With

End Sub
       
 

2. For whole Worksheet as Body
The above code will do the job for sending the worksheet over by mail. You just need to select any single cell on the worksheet and run the above code.

3. For Ranges defined within the VBA

       
Sub Mail_VariableRange()

ActiveWorkbook.Save                             'Saving workbook to counter
                                                '"the MailEnvelope method of Worksheet object failed" error

Dim Body As Range
                                                'Assuming a table in sheet1 staring from A1
                                                'You can change Sheet1 (Sheet Name) with actual one
Set Body = Sheets("Sheet1").Range("A1").CurrentRegion

                                                'Using Body.Parent to return and select Sheet1 worksheet object
Body.Parent.Select

                                                'Selecting the range to be mailed
Body.Select

With ActiveSheet.MailEnvelope                   'Creating an Email Environment.
.Introduction = "Add your Introduction here."

    With .Item                                  'Creating a MailItem
        .To = "Add Second Recipient Here"
        .BCC = "Add First Recipient Here"
        .CC = "Add Third Recipient Here"
        .Attachments.Add "Path of the file or Document"
        .Subject = "Enter your Subject here."
        
                                                'The Body of the Email would be the selected content of the activesheet.
                                                        
        ActiveWorkbook.EnvelopeVisible = True   ' or False - The line below will show/hide the Envelope on the workbook.
        .Send                                   ' Or remove .send to pull the trigger yourself using Envelope interface.
    End With

End With

End Sub

Some Explanations:-
1.  The above codes will use default mailing program for sending the mail. I have tested them with MS Outlook. Preferably Start MS outlook client software before running the code.

2. You can always use worksheet references for .To , .BCC, .CC, .Attachments.Add, .Subject properties. For example. if you have listed all your recipients in Sheet2 column A then you can use the following Code line to define .To property:-

       


' Declaring and Defining the recipients range.
Dim RecTo as Range
Set RecTo = Sheets("Sheet2").Range("A1:A" & Sheets(2).Cells(Rows.count, 1).end(xlup).row)
'or Set RecTo = Sheets("Sheet2").Range("A1").CurrentRegion.Columns(1)

'Use following line appropriately to replace one in the above code.
.To = Join(Application.Transpose(RecTo), ";")

       
 

3. You can use ActiveWorkbook.EnvelopeVisible = True ' or False to show or hide Mail Envelope on the worksheet itself.

4. Better if you create a button and make a reference to the macro OR Use run the macro manually.

I guess, I have been able to explain it well. However, if any of you has any suggestions and doubts then your comments are always welcome.

Regards,
Vikas Gautam