In previous article of Send Email Tutorial using Excel Macro, you learnt how to send One Sheet (Active Sheet) as attachment in the email.
In this Article you are going to learn how to send more than one Sheet from a workbook as an attachment in Email. The below function sends two sheets Sheet2 and Sheet3 of the current workbook in a mail as attachment.
This subroutine again, uses the same logic as previous one.
1) Copy multiple Sheets in a new workbook
2) Save that Workbook at a temporary folder
3) Send Email by attaching that file
4) Close and Delete the temporary workbook.

Sub Email_Multiple_Sheets()

    'Do not forget to change the email ID
    'before running this code
    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim FileExt As String
    Dim TempFileName As String
    Dim FileFullPath As String
    Dim FileFormat As Variant
    Dim Wb1 As Workbook
    Dim Wb2 As Workbook

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set Wb1 = ThisWorkbook
    'here multiple Sheets Sheet2 and Sheet3
    'are copied to a new workbook and
    'that will be sent as attachment
    Wb1.Sheets(Array("Sheet3", "Sheet2")).Copy
    Set Wb2 = ActiveWorkbook
    'Below code will get the File Extension and
    'the file format which we want to save the copy
    'of the workbook with the active sheet.
    With Wb2
        If Val(Application.Version) 


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!