Automate Outlook using vba: Sending Email from Excel using Outlook
————————————————————————————————
Contents:
Send the Host Workbook as an attachment with the mail, using Early Binding
Send multiple mails to ids sourced from the Host Workbook’s sheet, using Late Binding
————————————————————————————————
In this section it is explained how to Send Emails from Excel using Outlook, using Automation in vba. Examples have been given to automate using both Early Binding and Late Binding.
When you use vba in an Office Application, say Outlook, a reference to the Outlook Object Library is set by default. When you Automate to work with Outlook objects from another application, say Excel, you can add a reference to the Outlook object library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. This is a must when you automate using Early Binding (explained earlier). Once this reference is added, a new instance of Outlook application can be created by using the New keyword.
Automate Outlook from Excel, using Late Binding: You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code. In Late Binding, the object variable is declared as an Object Type which can be a reference to any object, and this makes an object late bound. In Late Binding, the object library is not exposed during design-time, but the binding happens during run-time using the CreateObject or the GetObject functions. CreateObject creates a new instance of Outlook and GetObject returns an already running instance of the Outlook object.
Click here for a detailed explanation of Automation using Early Binding and Late Binding.
Example 1: Send text and also contents from the host workbook’s worksheet range as Mail Body, and add an attachment with the mail, using Early Binding.
Sub OutlookMail_1()
‘Automate Sending Emails from Excel, using Outlook. Send text and also contents from the host workbook’s worksheet range as Mail Body, and add an attachment with the mail.
‘Automating using Early Binding: Add a reference to the Outlook Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.
‘variables declared as a specific object type ie. specific to the application which is being automated:
Dim applOL As Outlook.Application
Dim miOL As Outlook.MailItem
Dim recptOL As Outlook.Recipient
Dim ws As Worksheet
‘set worksheet:
Set ws = ThisWorkbook.Sheets(“Sheet1”)
‘Create a new instance of the Outlook application. Set the Application object as follows:
Set applOL = New Outlook.Application
‘create mail item:
Set miOL = applOL.CreateItem(olMailItem)
‘Add mail recipients, either the email id or their name in your address book. Invalid ids will result in code error.
Set recptOL = miOL.Recipients.Add(“info @globaliconnect.com”)
recptOL.Type = olTo
Set recptOL = miOL.Recipients.Add(“support @globaliconnect.com”)
recptOL.Type = olTo
Set recptOL = miOL.Recipients.Add(“John Kelly”)
recptOL.Type = olCC
Set recptOL = miOL.Recipients.Add(“Amit Tandon”)
recptOL.Type = olbcc
‘with the mail item:
With miOL
‘subject of the mail:
.Subject = “Financial Analysis – 2012”
‘Chr(10) represents line feed/new line, & Chr(13) represents carriage return. Send text and also contents from the host workbook’s worksheet range as Mail Body.
.Body = “Hi Chris,” & Chr(10) & Chr(10) & “Enclosing the financial analysis.” & Chr(10) & Chr(10) & Trim(ws.Range(“A1”)) & Trim(ws.Range(“A2”)) & Chr(10) & Chr(10) & Trim(ws.Range(“A3”)) & Chr(10) & Chr(10) & “Best Regards,” & Chr(10) & “John Pollard”
‘set importance level for the mail:
.Importance = olImportanceHigh
‘add an attachment to the mail:
.Attachments.Add (“C:\Users\Amit Tandon\Documents\Analysis.docx”)
‘send the mail:
.send
End With
‘clear the object variables:
Set applOL = Nothing
Set miOL = Nothing
Set recptOL = Nothing
End Sub
Example 2: Send the Host Workbook as an attachment with the mail, using Early Binding.
Sub OutlookMail_2()
‘Automate Sending Emails from Excel, using Outlook. Send the Host Workbook as an attachment with the mail.
‘Automating using Early Binding: Add a reference to the Outlook Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.
‘variables declared as a specific object type ie. specific to the application which is being automated:
Dim applOL As Outlook.Application
Dim miOL As Outlook.MailItem
‘Create a new instance of the Outlook application. Set the Application object as follows:
Set applOL = New Outlook.Application
‘create mail item:
Set miOL = applOL.CreateItem(olMailItem)
With miOL
.To = “info @globaliconnect.com”
.CC = “”
.Importance = olImportanceLow
.Subject = “Mail Automation”
.Body = “Sending the Active Excel Workbook as attachment!”
‘add host workbook as an attachment to the mail:
.Attachments.Add ActiveWorkbook.FullName
.ReadReceiptRequested = True
.send
End With
‘clear the object variables:
Set applOL = Nothing
Set miOL = Nothing
End Sub
Example 3: Send multiple mails to ids sourced from the Host Workbook’s sheet, using Late Binding.
For live code of this example, click to download excel file.
Sub OutlookMail_3()
‘Automate Sending Emails from Excel, using Outlook. Send multiple mails to ids sourced from the Host Workbook’s sheet.
‘Automating Outlook from Excel, using Late Binding. You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code.
‘variables declared as Object Type, which can be a reference to any object:
Dim oApplOL As Object
Dim oMiOL As Object
Dim lastRow As Long
Dim ws As Worksheet
Dim strMailSubject As String
Dim strMailMessage As String
‘set worksheet:
Set ws = ActiveWorkbook.Sheets(“Sheet1”)
‘Email ids are entered in column A of Sheet5 – determine last data row in column A of the worksheet:
lastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row
‘Create a new instance of the Outlook application, if an existing Outlook object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set oApplOL = GetObject(, “Outlook.Application”)
‘if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOL = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘ignores an invalid mail id syntax, and code execution will resume
On Error Resume Next
‘loop through all cells in column A:
For i = 1 To lastRow
‘validate mail syntax:
‘Like operator in vba: If the string satisfies the specified pattern, it will return True. * denotes zero or more characters, ? denotes a single character.
‘The following statement returns True if the string has an “@” and atleast one character before it, and atleast one character after it, then followed by “.” and atleast one character after it.
If Trim(ws.Cells(i, 1).Value) Like “*?@?*.?*” Then
‘pick mail subject from Range(“C1”):
strMailSubject = ws.Cells(1, 3)
‘Add text to mail message and also pick text from Range(“C2”) and Range(“C3”):
strMailMessage = “Hello “ & ws.Cells(i, 2) & vbCrLf & vbCrLf & ws.Cells(3, 3) & vbCrLf & vbCrLf & “Best Regards,” & Chr(13) & “Administrator”
‘create mail item:
‘Built-in constant olMailItem has been replaced by its value 0.
Set oMiOL = oApplOL.CreateItem(0)
With oMiOL
‘pick mails ids from column A:
.To = ws.Cells(i, 1)
‘Built-in constant olImportanceLow has been replaced by its value 0.
.Importance = 0
.Subject = strMailSubject
.Body = strMailMessage
.ReadReceiptRequested = False
.send
End With
End If
‘set a 2 seconds time interval:
Application.Wait (Now + TimeValue(“0:00:02”))
Next i
‘clear the object variables:
Set oApplOL = Nothing
Set oMiOL = Nothing
End Sub
Example 4: Copy the Host Workbook, Add a New Sheet and Copy-Paste a Range to the Workbook copy, then send as an attachment with the mail, using Late Binding.
For live code of this example, click to download excel file.
Sub OutlookMail_4()
‘Automate Sending Emails from Excel, using Outlook. Copy the Host Workbook, Add a New Sheet and Copy-Paste a Range to the Workbook copy, then send as an attachment with the mail.
‘Automating Outlook from Excel, using Late Binding. You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code.
‘variables declared as Object Type, which can be a reference to any object:
Dim oApplOL As Object
Dim oMiOL As Object
Dim oRecptOL As Object
Dim wb As Workbook
Dim wsNewSheet As Worksheet
Dim strWbName As String
Dim strWbPath As String
‘Application.ScreenUpdating Property. If set to False, screen updating will be turned off, and you will not be able to view what your code does but it executes faster. It is common to turn off screen updating in vba procedures to make codes run faster.
Application.ScreenUpdating = False
‘Create a new instance of the Outlook application, if an existing Outlook object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set oApplOL = GetObject(, “Outlook.Application”)
‘if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOL = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘set name for the Workbook copy, which is same as the host workbook preceded with “Copy of”:
strWbName = “Copy of “ & ThisWorkbook.Name
‘set path where to save the Workbook copy, to the same location as the host workbook:
strWbPath = ThisWorkbook.Path
‘create the Workbook copy with the name and in the location set above:
ActiveWorkbook.SaveCopyAs strWbPath & “\” & strWbName
‘set object variable for the opened Workbook copy:
Set wb = Workbooks.Open(strWbPath & “\” & strWbName)
‘confirm if you want to add a new sheet as a cover sheet in the Workbook copy:
If MsgBox(“Want to add a cover sheet in the new workbook?”, vbYesNo + vbQuestion, “Cover sheet?”) = vbYes Then
‘add the new sheet as the first sheet in the Workbook copy:
Set wsNewSheet = wb.Sheets.Add(Before:=Sheets(1), Count:=1, Type:=xlWorksheet)
‘name the new sheet:
wsNewSheet.Name = “CoverSheet”
‘Copy-Paste from host workbook to the new cover sheet in the Workbook copy:
ThisWorkbook.Sheets(“Sheet1”).
Range(“A1:A2”).Copy
wsNewSheet.Range(“E15:E16”).PasteSpecial Paste:=xlValues
wsNewSheet.Range(“E1”).Value = “Cover Sheet”
End If
‘save the Workbook copy:
wb.Save
‘create mail item:
‘Built-in constant olMailItem has been replaced by its value 0.
Set oMiOL = oApplOL.CreateItem(0)
‘add mail recipients:
Set oRecptOL = oMiOL.Recipients.Add(“info @globaliconnect.com”)
‘Built-in constant olTo has been replaced by its value 1.
oRecptOL.Type = 1
With oMiOL
‘Built-in constant olImportanceLow has been replaced by its value 0.
.Importance = 0
.Subject = “Mail Automation”
.Body = “Sending a Copy of the Active Excel Workbook, with a New Added Sheet and Range Copied, as attachment!”
‘attach the Workbook copy in the mail item:
.Attachments.Add wb.FullName
.ReadReceiptRequested = True
.send
End With
‘close the Workbook copy after saving:
wb.Close savechanges:=True
Application.ScreenUpdating = True
‘clear the object variables:
Set oApplOL = Nothing
Set oMiOL = Nothing
Set oRecptOL = Nothing
End Sub
Example 5: Create a New Workbook, Add a New Sheet from the Host Workbook, send the new workbook as an attachment with the mail, using Late Binding.
For live code of this example, click to download excel file.
Sub OutlookMail_5()
‘Automate Sending Emails from Excel, using Outlook. Create a New Workbook, Add a New Sheet from the Host Workbook, send the new workbook as an attachment with the mail.
‘Automating Outlook from Excel, using Late Binding. You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code.
‘variables declared as Object Type, which can be a reference to any object:
Dim oApplOL As Object
Dim oMiOL As Object
Dim oRecptOL As Object
Dim wbHost As Workbook
Dim wb As Workbook
Dim wsNewSheet As Worksheet
Dim strWbName As String
Dim strWbPath As String
Dim strFileExtn As String
Dim lFileFrmt As Long
‘Application.ScreenUpdating Property. If set to False, screen updating will be turned off, and you will not be able to view what your code does but it executes faster. It is common to turn off screen updating in vba procedures to make codes run faster.
Application.ScreenUpdating = False
‘Create a new instance of the Outlook application, if an existing Outlook object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set oApplOL = GetObject(, “Outlook.Application”)
‘if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOL = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
Set wbHost = ActiveWorkbook
‘Choose the excel File Type & Format for the new workbook:
‘It is advisable to use values in lieu of built-in constants, so that the code runs across all office / excel versions: xlWorkbookNormal = -4143; xlExcel8 = 56; xlOpenXMLWorkbook = 51; xlOpenXMLWorkbookMacroEnabled = 52; xlExcel12 = 50.
‘Built-in constants have been replaced by their values below.
If MsgBox(“Save as .xls file (97-2003 format) if you are using Excel 97-2003?”, vbYesNo + vbQuestion, “Excel File Format”) = vbYes Then
‘note that you can work with macros in this file format:
strFileExtn = “.xls”
lFileFrmt = -4143
ElseIf MsgBox(“Save as .xls file (97-2003 format) even though you are using Excel 2007?”, vbYesNo + vbQuestion, “Excel File Format”) = vbYes Then
‘note that you can work with macros in this file format:
strFileExtn = “.xls”
lFileFrmt = 56
ElseIf MsgBox(“Save as .xlsx file (2007 format – without macros) if you are using Excel 2007?”, vbYesNo + vbQuestion, “Excel File Format”) = vbYes Then
‘note that you cannot save macros while using this file format:
strFileExtn = “.xlsx”
lFileFrmt = 51
ElseIf MsgBox(“Save as .xlsm file (2007 format – macros enabled) if you are using Excel 2007?”, vbYesNo + vbQuestion, “Excel File Format”) = vbYes Then
strFileExtn = “.xlsm”
lFileFrmt = 52
ElseIf MsgBox(“Save as .xlsb file (2007 format, Excel Binary Workbook – macros enabled) if you are using Excel 2007?”, vbYesNo + vbQuestion, “Excel File Format”) = vbYes Then
strFileExtn = “.xlsb”
lFileFrmt = 50
Else
Exit Sub
End If
‘set path where to save the New Workbook, to the same location as the host workbook:
strWbPath = ThisWorkbook.Path
‘set name for the New Workbook with the defined path and the file extension as determined above:
strWbName = strWbPath & “\” & “NewWorkbook” & strFileExtn
‘add new workbook:
Set wb = Workbooks.Add
‘save the new workbook with its name, file format and password:
wb.SaveAs fileName:=strWbName, FileFormat:=lFileFrmt, Password:=“123”, ReadOnlyRecommended:=False
‘copy sheet from host workbook to new workbook, at the end:
wbHost.Sheets(“Sheet2”).Copy After:=wb.Sheets(wb.Sheets.Count)
‘save the new workbook:
wb.Save
‘create mail item:
‘Built-in constant olMailItem has been replaced by its value 0.
Set oMiOL = oApplOL.CreateItem(0)
‘add mail recipients:
Set oRecptOL = oMiOL.Recipients.Add(“info @globaliconnect.com”)
‘Built-in constant olTo has been replaced by its value 1.
oRecptOL.Type = 1
With oMiOL
‘Built-in constant olImportanceNormal has been replaced by its value 1.
.Importance = 1
.Subject = “Mail Automation”
.Body = “Sending a New Excel Workbook, after Adding a Sheet, as attachment!”
‘send new workbook as attachment to the mail:
.Attachments.Add wb.FullName
.ReadReceiptRequested = False
.send
End With
‘close new workbook after mail is sent:
wb.Close
Application.ScreenUpdating = True
‘clear the object variables:
Set oApplOL = Nothing
Set oMiOL = Nothing
Set oRecptOL = Nothing
End Sub
Example 6: Find a mail item in the Inbox folder meeting a specified criteria, delete some or all its attachments, add a new attachment, forward it to a specific email id, and move the mail item to a newly created mail folder, using Late Binding.
Sub OutlookMail_6()
‘Automate Sending Emails from Excel, using Outlook. Find a mail item in the Inbox folder meeting a specified criteria, delete some or all its attachments, add a new attachment, forward it to a specific email id, and move the mail item to a newly created mail folder.
‘Automate Outlook from Excel, using Late Binding. You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code.
‘variables declared as Object Type, which can be a reference to any object:
Dim oApplOL As Object
Dim oNsOL As Object
Dim oMFolder As Object
Dim oPFolder As Object
Dim oNFolder As Object
Dim oMItems As Object
Dim oMItem As Object
Dim oMItemF As Object
Dim oAtt As Object
Dim oAtts As Object
Dim i As Long, n As Long
‘Create a new instance of the Outlook application, if an existing Outlook object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set oApplOL = GetObject(, “Outlook.Application”)
‘if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOL = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘use the GetNameSpace method to instantiate (ie. create an instance of) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set oNsOL = oApplOL.GetNamespace(“MAPI”)
‘assign the object variable oPFolder to the “Personal Folders” folder:
Set oPFolder = oNsOL.Folders(“Personal Folders”)
‘A new folder is being created to which the forwarded mails will be moved. Use the Folders.Add Method to create a new folder “New Folder 1” in the “Personal Folders” folder:
Set oNFolder = oPFolder.Folders.Add(“New Folder 1”)
‘Assign the object variable oMFolder to the default Inbox folder. Built-in constant olFolderInbox has been replaced by its value 6.
Set oMFolder = oNsOL.GetDefaultFolder(6)
‘To assign the object variable oMFolder to the default sent items folder. Built-in constant olFolderSentMail has been replaced by its value 5.
‘Set oMFolder = oNsOL.GetDefaultFolder(5)
‘set the items collection:
Set oMItems = oMFolder.Items
‘search mails in Inbox folder whose Importance is set to High:
Set oMItem = oMItems.Find(“[Importance]=“”High”””)
‘to search mail items per sender’s name:
‘Set oMItem = oMItems.Find(“[SenderName] = ‘James Bond’“)
‘to search mails which have no subject:
‘Set oMItem = oMItems.Find(“[Subject]=“””””)
‘loop through all mails in the folder:
For i = 1 To oMItems.Count
‘Check if mail item (of High Importance) is not found. TypeName function returns the data type information of a variable ex. MailItem. If the function returns “Nothing”, it means that no object is currently assigned to the variable.
If TypeName(oMItem) = “Nothing” Then
MsgBox “No more mail whose Importance is set to High!”
MsgBox “Total Mails found whose Importance is set to High: ” & i – 1
Exit Sub
End If
‘search result no. of the mail item being referenced:
MsgBox “Search Result No: ” & i
‘received time of the High Importance mail:
MsgBox “Received Time: ” & oMItem.ReceivedTime
‘sender’s name of the High Importance mail:
MsgBox “Sender’s Name: ” & oMItem.SenderName
‘use the Forward method to Forward a mail item, wherein a copy of the mail item is returned as a new object, which is set as oMItemF:
Set oMItemF = oMItem.Forward()
With oMItemF
.Display
.To = “info @globaliconnect.com”
.CC = “”
‘Built-in constant olImportanceLow has been replaced by its value 0.
.Importance = 0
.Subject = .Subject & ” – Forwarding Mail!”
‘confirm each attachment in the forwarded mail (oMItemF) for deletion:
Set oAtts = .Attachments
For n = oAtts.Count To 1 Step -1
If MsgBox(“Want to Delete Attachment: ” & oAtts(n).DisplayName, vbYesNo + vbQuestion, “Delete Attachment?”) = vbYes Then
oAtts(n).Delete
End If
Next n
‘add your own attachment to the forwarded mail, oMItemF:
.Attachments.Add (“C:\Users\Amit Tandon\Documents\Friends.docx”)
.ReadReceiptRequested = False
‘move forwarded mail to a new folder:
.Move oNFolder
.send
End With
‘search next mail item whose Importance is set to High:
Set oMItem = oMItems.FindNext
Next i
‘clear the object variables:
Set oApplOL = Nothing
Set oNsOL = Nothing
Set oMFolder = Nothing
Set oPFolder = Nothing
Set oNFolder = Nothing
Set oMItems = Nothing
Set oMItem = Nothing
Set oMItemF = Nothing
Set oAtt = Nothing
Set oAtts = Nothing
End Sub