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:
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") & _
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
ActiveWorkbook.SaveAs ThisWorkbook.Path & sFileName
.To = "email@example.com"
.CC = ""
.BCC = ""
.Subject = "Subject line"
.Body = "www.vit-consulting.com"
.Attachments.Add (ThisWorkbook.Path & sFileName)
Kill ThisWorkbook.Path & sFileName
Set OLMail = Nothing
Set OLApp = Nothing
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! 😀