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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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! 😀