How to Send ActiveSheet as Attachment in mail



Email

In previous article of Send Email Tutorial using Excel Macro, you learnt how to send current workbook as attachment in the email.
In this Article you are going to learn how to send the ActiveSheet as an attachment in Email. The below function sends the active sheet as an attachment in email.



Sub Email_One_ActiveSheet()

    '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
    ActiveSheet.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) < 12 Then
             FileExt = ".xls": FileFormat = -4143
        Else
            Select Case Wb1.FileFormat
            Case 51: FileExt = ".xlsx": FileFormat = 51
            Case 52:
                If .HasVBProject Then
                    FileExt = ".xlsm": FileFormat = 52
                Else
                    FileExt = ".xlsx": FileFormat = 51
                End If
            Case 56: FileExt = ".xls": FileFormat = 56
            Case Else: FileExt = ".xlsb": FileFormat = 50
            End Select
        End If
    End With

    'Save your workbook in your temp folder of your system
    'below code gets the full path of the temporary folder
    'in your system
    
    TempFilePath = Environ$("temp") & "\"

    'Now append a date and time stamp
    'in your new file
    
    TempFileName = Wb1.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss")

    'Complete path of the file where it is saved
    FileFullPath = TempFilePath & TempFileName & FileExt
    
    'Now save your currect workbook at the above path
    Wb2.SaveAs FileFullPath, FileFormat:=FileFormat
    
    'Now open a new mail
    
    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)
    
    On Error Resume Next
    With NewMail
        .To = "info@learnexcelmacro.com"
        .CC = "info@learnexcelmacro.com"
        .BCC = "info@learnexcelmacro.com"
        .Subject = "Type your Subject here"
        .Body = "Type the Body of your mail"
        .Attachments.Add FileFullPath '--- full path of the temp file where it is saved
        .Send   'or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0
    
    'Since mail has been sent with the attachment
    'Now close and delete the temp file from the
    'temp folder
    Wb2.Close SaveChanges:=False
    Kill FileFullPath
    
    'set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing
    
    'Now set the application properties back to true
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Note: The above code sends the activesheet as an attachment by email. But if you want to send any particular Sheet in the mail as an attachment, then use the below line in the above code:

ActiveSheet.Copy => Sheets(“Sheet_Name”).Copy

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Yousef Qaroos on July 14, 2017 at 9:37 am

Is there any way to send the ActiveSheet as an attachment and pdf in same outlook Email message?
Reply
- Vishwamitra Mishra on July 14, 2017 at 1:21 pm
  
  Yes ofcourse, you can refer these two articles and combine the codes for creating PDF out of activesheet and attaching Active sheet.
  
  http://learnexcelmacro.com/wp/2012/08/mail-one-sheet/
  http://learnexcelmacro.com/wp/2012/11/send-worksheet-as-a-pdf-attachment/
  [code language=”vb”]
  Sub Email_One_ActiveSheet()
  
  ‘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
  ActiveSheet.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) < 12 Then
  FileExt = ".xls": FileFormat = -4143
  Else
  Select Case Wb1.FileFormat
  Case 51: FileExt = ".xlsx": FileFormat = 51
  Case 52:
  If .HasVBProject Then
  FileExt = ".xlsm": FileFormat = 52
  Else
  FileExt = ".xlsx": FileFormat = 51
  End If
  Case 56: FileExt = ".xls": FileFormat = 56
  Case Else: FileExt = ".xlsb": FileFormat = 50
  End Select
  End If
  End With
  
  ‘Save your workbook in your temp folder of your system
  ‘below code gets the full path of the temporary folder
  ‘in your system
  
  TempFilePath = Environ$("temp") & "\"
  
  ‘Now append a date and time stamp
  ‘in your new file
  
  TempXLFileName = Wb1.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss")
  
  ‘THis is for your PDF file
  TempPDFFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
  
  ‘Complete path of the file where it is saved
  xlFileFullPath = TempFilePath & TempXLFileName & FileExt
  
  ‘PDF file path
  pdfFileFullPath = TempFilePath & TempPDFFileName
  
  ‘Now save your currect workbook at the above path
  Wb2.SaveAs xlFileFullPath, FileFormat:=FileFormat
  
  ‘Now Export the Activesshet as PDF with the given File Name and path
  With ActiveSheet
  .ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=pdfFileFullPath, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=False
  End With
  
  ‘Now open a new mail
  
  Set OlApp = CreateObject("Outlook.Application")
  Set NewMail = OlApp.CreateItem(0)
  
  On Error Resume Next
  With NewMail
  .To = "info@learnexcelmacro.com"
  .CC = "info@learnexcelmacro.com"
  .BCC = "info@learnexcelmacro.com"
  .Subject = "Type your Subject here"
  .Body = "Type the Body of your mail"
  .Attachments.Add xlFileFullPath ‘— full path of the temp xl file where it is saved
  .Attachments.Add pdfFileFullPath ‘— full path of the temp PDF file where it is saved
  .send ‘or use .Display to show you the email before sending it.
  End With
  On Error GoTo 0
  
  ‘Since mail has been sent with the attachment
  ‘Now close and delete the temp file from the
  ‘temp folder
  Wb2.Close SaveChanges:=False
  Kill xlFileFullPath
  Kill pdfFileFullPath
  
  ‘set nothing to the objects created
  Set NewMail = Nothing
  Set OlApp = Nothing
  
  ‘Now set the application properties back to true
  With Application
  .ScreenUpdating = True
  .EnableEvents = True
  End With
  End Sub
  
  [/code]
  Reply
Alex on August 15, 2017 at 4:24 pm

Hello,

This code works 99% perfect for me. I’m trying to get the code to save a specific sheet as an attachment and have tried using the code you suggested:

ActiveSheet.Copy => Sheets(“Sheet_Name”).Copy

however it comes up with a syntax error due to the “=>”. I changed this to be just “=” so it becomes this:

ActiveSheet.Copy = Sheets(“Sheet_Name”).Copy

When I go to run the code, it comes up with “Run-time error ‘9’: Subscript out of range”

Do you have any suggestions for how to resolve this?

Thank you for your help!
Reply
- Alex on August 15, 2017 at 4:33 pm
  
  I’ve modified the code to remove ActiveSheet.Copy = to be just:
  
  Sheets(“Sheet_Name”).Copy
  
  Am still getting the Run-Time error ‘9’: Subscript out of range however
  Reply
  - test on October 13, 2017 at 9:24 am
    
    Have you managed to resolve this?
    I think what the author meant is to replace ActiveSheet.Copy in the code with Sheets(“yoursheetname”).Copy
    Instead of ‘Sheet_Name’ you should insert the name of sheet you wish to attach.
    Reply
Bilal on September 15, 2018 at 6:57 am

I have replaced ActiveSheet.Copy in the code with Sheets(“mysheetname”), but got an error as highlighted by Other Users
Reply
EJ van der Beek on September 27, 2018 at 12:48 pm

I am searching for a macro which instead of outlook operate to Thunderbird.
Reply
Barbara on October 11, 2019 at 2:51 pm

I would like to have this code in my personal macro workbook, but send a tab from another workbook via e-mail. Using above code I keep sending my personal macro workbook instead. How can I get around this?
Reply
Zach on August 2, 2020 at 10:42 pm

How might I select just a range of cells to add to the new workbook, or simply use the PrintArea as the range?
Reply
- Zach on August 3, 2020 at 12:25 am
  
  I’d also like for the newly created worksheet to be sent with the macro’s intact
  Reply