How to send an Excel file using Microsoft Outlook

This is our second post on this subject (Click here to see the first, on our sister site, ExcelHints.Com). This post takes the Microsoft Excel VBA/Macro/code that Jacob (Microsoft Excel MVP) wrote up a notch in complexity from the first post (v1.0). This version of the code allows the Macro/VBA to read the names and email addresses from ranges in the MS worksheet, to be used in the code, versus having hardcoded text in the module, as it was in version 1.0, thus this code is much more advanced and more powerful.

In case you did not read the original post on this subject, what we are trying to do is to teach you how to click a button in Excel, which will take a copy of that exact workbook, and send it to one or more persons via Microsoft Outlook. The reason we are doing several posts on this subject is simple, you can write code that while it will do the job, is not as powerful as you would like, and still requires more effort on your part, to examples that totally automate and integrate the push-button interface, requiring a single click from you. In this case being the button that looks like an envelope.

 

Step 1: Download the Microsoft Excel workbook that contains the code that is used in this blog post.

 

excel-and-access-llc-excel-workbook-2

 

This example, as can be seen in the image above has three columns, two populated, one waiting to be populated. The names in column A will be used in the email, and the email addresses in column B will be used to specify who will receive the email. Pretty straight forward. We will get to column C shortly.

If you take a quick moment to look at the code, in the image below, you will see through rows being highlighted. These represent the three columns in the workbook.

When the code runs, two things will happen, 1) the Excel workbook will be sent to one or more people via Microsoft Outlook, 2) column C will be populated with the long-date recording when the file was sent.

 

Here is an image containing the code you will use. Notice the comments that explain the line(s) of code.

 

excel-and-access-llc-excel-workbook-code-1

 

Here is how you can use the code that Jacob wrote (Fully functioning) .

  • Download the workbook.
    • You can use the workbook as is, no changes needed, the code will run.
  • You can copy the code in the workbook, or copy it from below. You will then need paste it into your various workbooks.
    • You can alter the code to better serve your exact purposes.
  • If you have questions about the code, and possible changes you would like to make to it, use the link below, to our Microsoft Help forum, Vbaexpress.Com.
  • Finally, you will need to take the time to put the names and email addresses of your recipients.
    • One of your options is to add code that will clear that field if it is already populated, or append the new records to the lists.

 

 

 Copy and Paste this VBA into your Microsoft Excel Workbook to send Excel Files via Outlook EMail

 

Dim i                   As Long
Dim LastRow             As Long
Dim AppOL               As Object
Dim EmailItem           As Object

Const olMailItem    As Long = 0

‘Initialze Objects
Set AppOL = CreateObject(“Outlook.Application”)

‘Save Workbook
ActiveWorkbook.Save

‘Get Last Row
LastRow = Range(“A” & Rows.Count).End(xlUp).Row

‘Process Each Row
For i = 2 To LastRow
‘Create Email
Set EmailItem = AppOL.CreateItem(olMailItem)

‘Format Email
With EmailItem
.Subject = “Insert Subject Here”
.Body = “Dear ” & Range(“A” & i).Value
.to = Range(“B” & i).Value
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

‘Log Send Time
Range(“C” & i).Value = Now
Next i

ExitSub:

‘Release Object Variables
Set EmailItem = Nothing
Set AppOL = Nothing

End Sub

 

 

If this is beyond your skills set or if you just lack the time to work on this, allow us to assist you with your Microsoft Excel and Access programming needs.

Call us now and get started today 877-392-3539

Free Consultation