VBA Send Emails from Excel Through Outlook

This tutorial will show you how to send emails from Excel through Outlook using VBA.

Sending the Active Workbook

Function SendActiveWorkbook(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String) As Boolean
   On Error Resume Next
   Dim appOutlook As Object
   Dim mItem As Object
'create a new instance of Outlook
   Set appOutlook = CreateObject("Outlook.Application")
   Set mItem = appOutlook .CreateItem(0)
   With mItem 
     .To = strTo
     .CC = ""
     .Subject = strSubject
     .Body = strBody
     .Attachments.Add ActiveWorkbook.FullName
'use send to send immediately or display to show on the screen
    .Display 'or .Send
   End With
'clean up objects
  Set mItem = Nothing
  Set appOutlook = Nothing
End Function

The function above can be called using the procedure below

Sub SendMail()
   Dim strTo As String
   Dim strSubject As String
   Dim strBody As String
'populate variables
   strTo = "jon.smith@gmail.com"
   strSubject = "Please find finance file attached"
   strBody = "some text goes here for the body of the email"
'call the function to send the email
   If SendActiveWorkbook(strTo, strSubject, , strBody) = true then
      Msgbox "Email creation Success"
   Else
      Msgbox "Email creation failed!"
   End if
End Sub

Using Early Binding to refer to the Outlook Object Library

The code above uses Late Binding to refer to the Outlook Object. You can add a reference to Excel VBA, and declare the Outlook application and Outlook Mail Item using Early Binding if preferred. Early Binding makes the code run faster, but limits you as the user would need to have the same version of Microsoft Office on their PC.

Click on the Tools menu and References to show the reference dialog box.

Add a reference to the Microsoft Outlook Object Library for the version of Office that you are using.

You can then amend your code to use these references directly.

 

A great advantage of early binding is the drop down lists that show you the objects that are available to use!

Sending a Single Sheet from the Active Workbook

To send a single sheet, you first need to create a new workbook from the existing workbook with just that sheet in it, and then send that sheet.

Function SendActiveWorksheet(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String) As Boolean
   On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim strDestName As String
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim OutApp As Object
   Dim OutMail As Object
   Dim strTempName As String
   Dim strTempPath As String
'first create destination workbook
   Set wbDestination = Workbooks.Add
   strDestName = wbDestination.Name
'set the source workbook and sheet
   Set wbSource = ActiveWorkbook
   Set wsSource = wbSource.ActiveSheet
'copy the activesheet to the new workbook
   wsSource.Copy After:=Workbooks(strDestName).Sheets(1)
'save with a temp name
   strTempPath = Environ$("temp") & "\"
   strTempName = "List obtained from " & wbSource.Name & ".xlsx"
   With wbDestination
      .SaveAs strTempPath & strTempName
'now email the destination workbook
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
      With OutMail
         .To = strTo
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add wbDestination.FullName
'use send to send immediately or display to show on the screen
         .Display 'or .Display
      End With
      .Close False
  End With
'delete temp workbook that you have attached to your mail
   Kill strTempPath & strTempName
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsSource = Nothing
   Set OutMail = Nothing
   Set OutApp = Nothing
Exit Function
eh:
   MsgBox Err.Description
End Function

and to run this function, we can create the following procedure

Sub SendSheetMail()
   Dim strTo As String
   Dim strSubject As String
   Dim strBody As String
   strTo = "jon.smith@gmail.com"
   strSubject = "Please find finance file attached"
   strBody = "some text goes here for the body of the email"
   If SendActiveWorksheet(strTo, strSubject, , strBody) = True Then
      MsgBox "Email creation Success"
   Else
      MsgBox "Email creation failed!"
   End If
End Sub