VBA – Mailing Excel Range as attachment

In this article I will show some easy code, that you can embed in your Excel tool, if you want to send its used range as an attachment. The code, I present is a little hard-coded, but it can be easily made flexible. In order to run it, you should add a reference to MS Outlook from your VBA Editor. This is done the following way – open the VBA Editor (Alt+F11) and then go to Tools>References. There you should look for the reference, as in the screenshot:

Once you have done it, you may simply copy and paste my code into a module and run it. If you do not enter some wrong data into the InputBox (e.g. characters, not allowed file names), it would work. Here is the code:

Option Explicit
Sub SendRange()

    Dim OLApp           As Outlook.Application
    Dim OLMail          As Object
    Dim sFileName       As String

    sFileName = "\" & _
    InputBox(Prompt:="Please enter a name for the file", Title:="File Name") & _
    ".xlsx"

    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)

    ActiveSheet.UsedRange.Copy
    Workbooks.Add

    With Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With

    ActiveWorkbook.SaveAs ThisWorkbook.Path & sFileName

    OLApp.Session.Logon

    With OLMail
        .To = "donotspam@please.com"
        .CC = ""
        .BCC = ""
        .Subject = "Subject line"
        .Body = "www.vit-consulting.com"
        .Attachments.Add (ThisWorkbook.Path & sFileName)
        .Display
        '.Send
    End With

    ActiveWorkbook.Close SaveChanges:=True
    Kill ThisWorkbook.Path & sFileName

    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

How does it work? Pretty much, the first thing it does is to ask us for name of the file, which we are going to attach. Then it copies the used range from the  active sheet and pastes it at a newly created Excel file. Once the outlook application object is created, the VBA code uses its properties to define it. In the “Attachments.Add” property, it adds our file.

At the end it deletes the newly created excel file and sets the outlook object variables to Nothing. Pretty much that is how it runs. It can be edited with different features, but this basic one works pretty well.

Enjoy the code! 😀