VBA Send Emails from Excel through Outlook
In this Article
This tutorial will show you how to send emails from Excel through Outlook using VBA.
Sending the Active Workbook
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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, 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.
|
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 45 46 47 48 49 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
