Automate Microsoft Outlook from Excel, using VBA
—————————————————————————————————————————————
Contents:
Reference Existing Outlook Folders and Create New Folders, in Automation
Create New Outlook Items and Reference Outlook Items, in Automation
—————————————————————————————————————————————
In this section it is explained how to Automate Outlook from Excel, with practical examples and codes, and topics include: Automate using Early Binding and Late Binding, Create a new instance of the Outlook application, Create an instance of a NameSpace object variable, Reference Existing Outlook Folders and Create New Folders in Automation, Create New Outlook Items and Reference Existing Outlook Items in Automation, Outlook Contact item and the default Contacts folder, Mail item in Outlook and the default Inbox folder, relevant vba methods used in automation viz. Application.GetNamespace Method, Items.Add Method, Application.CreateItem Method, and so on.
The first steps in Automation are to create a new instance of the Outlook application (ie. the application object) and to create an instance of a NameSpace object variable, to access existing Outlook items.
Create a new instance of the Outlook application:
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.
Use the Application.GetNamespace Method to create an instance of a NameSpace object variable, to access existing Outlook items:
MAPI (Messaging Application Programming Interface) is a proprietary email protocol of Microsoft, used for development of Microsoft Outlook messaging applications. All information is stored in MAPI folders by Outlook. You can interact with the data stored in Outlook by setting a Namespace object to MAPI. The Namespace object provides methods to access data sources, and the only supported data source is MAPI.
Use the Application.GetNamespace Method to return the Outlook NameSpace object of type “MAPI”: ApplicationObjectVariable.GetNameSpace (“MAPI”). Doing this will allow access to all Outlook data. The GetNameSpace method has the same affect as the Application.Session Property which can alternatively be used to create the Outlook NameSpace object for the current session.
Reference Existing Outlook Folders and Create New Folders, in Automation
We show how to reference existing Outlook folders and add new folders, with the help of practical examples/codes, as below. Remember to 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.
NameSpace.GetDefaultFolder Method returns the default folder of the specified type.
NameSpace.GetDefaultFolder Method returns the default folder of the specified type. Outlook folder types include Calendar (for storing appointments), Contacts (for storing contact items), Inbox (for incoming e-mails), Tasks (for storing tasks), and so on. Though a user can create multiple folders of each type, it is usually convenient to store all information in the default folder ie. instead of creating multiple folders of the Contact type, all contacts are usually stored in the default Contacts folder.
Example 1: Return the default outlook folder of the specified type – automate using Early Binding.
Sub AutomateOutlook1()
‘Automate 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.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cfOutlook As Outlook.Folder
Dim ifOutlook As Outlook.Folder
‘Create a new instance of the Outlook application. Set the Application object as follows:
Set applOutlook = New Outlook.Application
‘use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable cfOutlook to the default Contacts folder:
Set cfOutlook = nsOutlook.GetDefaultFolder(olFolderContacts)
MsgBox cfOutlook
‘assign the object variable ifOutlook to the default Inbox folder:
Set ifOutlook = nsOutlook.GetDefaultFolder(olFolderInbox)
MsgBox ifOutlook
End Sub
Example 2: Return the default outlook folder of the specified type – automate using Late Binding.
Sub AutomateOutlook2()
‘Automate Outlook from Excel, using Late Binding. You need not add a reference to the Outlook 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.
Dim applOutlook As Object
Dim nsOutlook As Object
Dim cfOutlook As Object
Dim ifOutlook As Object
‘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 applOutlook = 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 applOutlook = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable cfOutlook to the default Contacts folder:
‘The numerical value of olFolderContacts is 10. The following code has replaced the Outlook’s built-in constant olFolderContacts by its numerical value 10.
Set cfOutlook = nsOutlook.GetDefaultFolder(10)
MsgBox cfOutlook
‘assign the object variable ifOutlook to the default Inbox folder:
‘The numerical value of olFolderInbox is 6. The following code has replaced the Outlook’s built-in constant olFolderInbox by its numerical value 6.
Set ifOutlook = nsOutlook.GetDefaultFolder(6)
MsgBox ifOutlook
End Sub
Example 3: Refer a subfolder of a default folder type by name or index number.
Sub AutomateOutlook3()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cfOutlook As Outlook.Folder
Dim csfOutlook As Outlook.Folder
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
Set cfOutlook = nsOutlook.GetDefaultFolder(olFolderContacts)
‘refer to a folder named “cont_1” which is a subfolder of the default Contacts folder (note that folder names are case-sensitive):
Set csfOutlook = cfOutlook.Folders(“cont_1”)
MsgBox csfOutlook
‘refer to the first folder which is a subfolder of the default Contacts folder.
Set csfOutlook = cfOutlook.Folders(1)
MsgBox csfOutlook
‘refer to the second folder which is a subfolder of the default Contacts folder.
Set csfOutlook = cfOutlook.Folders(2)
MsgBox csfOutlook
‘return the number of subfolders of the default Contacts folder:
MsgBox cfOutlook.Folders.Count
End Sub
Example 4: Refer a specific outlook folder by name and refer to subfolders going from up to down.
Sub AutomateOutlook4()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim fOutlook As Outlook.Folder
Dim sf1Outlook As Outlook.Folder
Dim sf2Outlook As Outlook.Folder
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable fOutlook to the “Personal Folders” folder:
Set fOutlook = nsOutlook.Folders(“Personal Folders”)
MsgBox fOutlook
‘”Friends” is a subfolder of “Personal Folders” folder:
Set sf1Outlook = fOutlook.Folders(“Friends”)
MsgBox sf1Outlook
‘”Friend_1″ is a subfolder of “Friends” folder:
Set sf2Outlook = sf1Outlook.Folders(“Friend_1”)
MsgBox sf2Outlook
‘return the number of subfolders in the “Personal Folders” folder:
MsgBox fOutlook.Folders.Count
End Sub
The Folders.Add Method creates new outlook folders.
Use the Folders.Add Method to create new folders. It is necessary to specify the first argument which is the name of the new folder. Not specifying the second argument of folder type will default to the same folder type in which it is created. With this method a new folder is created in the folders collection.
The Parent Property returns the Parent folder wherein the folder or item is located.
Example 5: Use the Folders.Add Method to create new folders, and the Parent Property to return the Parent folder.
Sub AutomateOutlook5()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim fOutlook As Outlook.Folder
Dim sf1Outlook As Outlook.Folder
Dim sf2Outlook As Outlook.Folder
Dim pFolder As Outlook.Folder
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable fOutlook to the “Personal Folders” folder:
Set fOutlook = nsOutlook.Folders(“Personal Folders”)
MsgBox fOutlook
‘use the Folders.Add Method to create a new folder “New Folder 1” in the “Personal Folders” folder:
Set sf1Outlook = fOutlook.Folders.Add(“New Folder 1”)
‘use the Folders.Add Method to create a new folder “New Folder 2” in the “New Folder 1” folder:
Set sf2Outlook = sf1Outlook.Folders.Add(“New Folder 2”)
‘Use the Parent Property to return the Parent folder of the “New Folder 1” folder:
Set pFolder = sf1Outlook.Parent
MsgBox pFolder
‘Use the Parent Property to return the Parent folder of the “New Folder 2” folder:
Set pFolder = sf2Outlook.Parent
MsgBox pFolder
End Sub
Create New Outlook Items and Reference Outlook Items, in Automation
Create a new Outlook item
We explain how a new Outlook item can be created using the Items.Add Method and the Application.CreateItem Method.
Forms: Contents of an Outlook item are viewed either in an explorer window, or through a form. For each item type (Contact, Appointment, Mail Message, etc) Outlook has a standard form(s), which is the principal user interface for the item wherein its content can be viewed or edited. Customized versions of these forms can be created wherein the item display can be modified.
MessageClass: An item is linked to the form where it is based, using the MessageClass property. An item’s message class identifies the form to be used or activated for displaying it. Message class is used to identify a form for: Email messages (IPM.Note), Contacts (IPM.Contact), Documents (IPM.Document), Appointments (IPM.Appointment), Distribution lists (IPM.DistList), Resending a failed message (IPM.Resend), and so on. Message class ID is mentioned in brackets.
Items.Add Method: By using the Items.Add Method you can create a new item based on any message class – this means that items can be created using the default Outlook item types (OlItemType constants) of olAppointmentItem, olContactItem, olJournalItem, olMailItem, olNoteItem, olPostItem, and olTaskItem, or any valid message class. With the Items.Add method, you can create new items using a custom form based on the specified MessageClass.
Example 6: Use the Items.Add Method to create a new Outlook contact item in the default Contacts folder, with or without using a Custom Form.
Sub AutomateOutlook6()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim cItem As Outlook.ContactItem
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the default Contacts folder where the new item is being created:
Set cFolder = nsOutlook.GetDefaultFolder(olFolderContacts)
‘Use the Items.Add method (message class for a custom form is specified) to add a new contact item, using the custom contact form called “custForm1”. Remember that the custom form “custForm1” should already have been created.:
Set cItem = cFolder.Items.Add(“IPM.Contact.custForm1”)
‘Alternate 1: Use the Items.Add method (a default Outlook message class is specified) to add a new contact item, based on the standard/default contact form:
‘Set cItem = cFolder.Items.Add(“IPM.Contact”)
‘Alternate 2: Use the Items.Add method (OlItemType constant is specified) to add a new contact item, based on the standard/default contact Form:
‘Set cItem = cFolder.Items.Add(olContactItem)
‘Alternate 3: Use the Items.Add method (item type not specifically mentioned) to add a new contact item, based on the standard contact form. If the item type is not specified after Add, it defaults to its parent folder’s type:
‘Set cItem = cFolder.Items.Add
‘set properties of the new contact item:
With cItem
.Display
.FullName = “Alan Croft”
.Gender = olMale
.Email1Address = “alanalan1 @alan.com”
.Email1AddressType = “SMTP”
.Birthday = “March 02, 1977”
.Email2Address = “alanalan2 @alan.com”
.HomeAddressStreet = “Elm Street”
.HomeAddressCity = “Los Angeles”
.HomeAddressState = “California”
.HomeAddressCountry = “USA”
.HomeAddressPostalCode = “90001”
.Close olSave
End With
End Sub
Example 7: Use the Items.Add Method to create a new Outlook contact item in the default Contacts folder.
For live code of this example, click to download excel file.
Sub AutomateOutlook7()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim cItem As Outlook.ContactItem
Dim cItem1 As Outlook.ContactItem
Dim cItem2 As Outlook.ContactItem
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the folder where a new item is to be created:
Set cFolder = nsOutlook.GetDefaultFolder(olFolderContacts)
‘set an existing contact item, to copy some of its properties to the new item:
Set cItem1 = cFolder.Items(“Hutch Care”)
‘add a new contact item:
‘Note that it if the item type is not specified after Add [viz. Add(olContactItem)], it defaults to its parent folder’s type.
Set cItem2 = cFolder.Items.Add
‘check if contact name already exists in the contacts folder:
For Each cItem In cFolder.Items
If cItem.firstName = “Alan” And cItem.LastName = “Croft” Then
MsgBox “Name already exists”
Exit Sub
End If
Next
With cItem2
.Display
.firstName = “Alan”
.LastName = “Croft”
.CompanyName = “Alan Properties LLC”
.Email1Address = “alanalan1 @alan.com”
.HomeAddress = “Northridge, LA, CA, USA”
.BusinessAddress = “Ventura, LA, CA, USA”
.SelectedMailingAddress = olBusiness
.BusinessTelephoneNumber = “213-725-1234”
‘copy MobileTelephoneNumber from existing contact item (cItem1):
.MobileTelephoneNumber = cItem1.MobileTelephoneNumber
.Close olSave
End With
End Sub
Example 8: Use the Items.Add Method to create a new Outlook contact item in a specific Contacts folder.
Sub AutomateOutlook8()
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim subFolder1 As Outlook.Folder
Dim cItem As Outlook.ContactItem
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the folder (named “cont”) where a new item is to be created:
Set cFolder = nsOutlook.Folders(“Personal Folders”)
Set subFolder1 = cFolder.Folders(“cont”)
Set cItem = subFolder1.Items.Add(olContactItem)
With cItem
.Display
.firstName = “Alan”
.LastName = “Croft”
.CompanyName = “Alan Properties LLC”
.Email1Address = “alanalan1 @alan.com”
.HomeAddress = “Northridge, LA, CA, USA”
.BusinessAddress = “Ventura, LA, CA, USA”
.SelectedMailingAddress = olBusiness
.BusinessTelephoneNumber = “213-725-1234”
.MobileTelephoneNumber = “213-725-5678”
.Close olSave
End With
End Sub
CreateItem method: Using the CreateItem method, only default Outlook items (olAppointmentItem, olContactItem, olDistributionListItem, olJournalItem, olMailItem, olNoteItem, olPostItem, and olTaskItem) can be created. To use this method you must mention the only argument of the Outlook item type for the new item. To create a new Outlook item based on a custom form, use the Items.Add Method.
Example 9: Use the Application.CreateItem Method to create or return a new mail item in Outlook.
Sub AutomateOutlook9()
‘Note that this example opens and displays a new email message in Outlook, enters subject, body and attaches a file, but does not send a mail.
Dim applOutlook As Outlook.Application
Dim miOutlook As Outlook.MailItem
Set applOutlook = New Outlook.Application
‘create and display a new email item:
Set miOutlook = applOutlook.CreateItem(olMailItem)
‘set properties of the new mail item:
With miOutlook
.Display
.Subject = “Sending a Test Mail”
.Body = “Wish the best in learning Outlook Automation!”
‘attach a file from the specified folder:
.Attachments.Add (“C:\Users\Amit Tandon\Documents\Friends.docx”)
End With
End Sub
Example 10: Use the Application.CreateItem Method to create a new contact Outlook item in the default Contacts folder.
Sub AutomateOutlook10()
‘Use the Application.CreateItem Method to create a new contact Outlook item in the default Contacts folder.
‘Using this method a new contact item is always created in the default Contacts folder.
Dim applOutlook As Outlook.Application
Dim cItem As Outlook.ContactItem
Set applOutlook = New Outlook.Application
‘create and display a new contact form for input, using the Application.CreateItem Method:
Set cItem = applOutlook.CreateItem(olContactItem)
With cItem
.Display
.firstName = “Alan”
.LastName = “Croft”
.CompanyName = “Alan Properties LLC”
.Email1Address = “alanalan1 @alan.com”
.HomeAddress = “Northridge, LA, CA, USA”
.BusinessAddress = “Ventura, LA, CA, USA”
.SelectedMailingAddress = olBusiness
.BusinessTelephoneNumber = “213-725-1234”
.MobileTelephoneNumber = “213-725-5678”
.Close olSave
End With
End Sub
Reference Existing Items in an Outlook Folder
Items collection represents all items contained in a folder, in which a specific item can be referenced by an Index value, for example, OutlookFolder.Items(1) refers to the first item in the collection. OutlookFolder.Items.Count will return the total number of items in the Folder. Specific items in a collection are referenced using an Index, which for example can be done with the For … Next vba statement, or the Do While … Loop vba statement. You can loop through all items in the collection without using an Index, with the For Each … Next vba statement.
Example 11: Use the For … Next vba statement to loop through (and delete) all items in the “Deleted Items” folder, using item Index.
Sub AutomateOutlook11()
‘Use the For … Next vba statement to loop through (and delete) all items in the “Deleted Items” folder, using item Index.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim delFolder As Outlook.Folder
Dim delItems As Outlook.Items
Dim dItem As Object
Dim count As Long, n As Long
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set the default Deleted Items folder:
Set delFolder = nsOutlook.GetDefaultFolder(olFolderDeletedItems)
‘set the items collection:
Set delItems = delFolder.Items
‘determine number of items in the collection:
count = delItems.count
‘start deleting from the last item:
For n = count To 1 Step -1
delItems(n).Delete
Next n
‘Alternate Code: start deleting from the last item. Declare dItem as Object:
‘For n = count To 1 Step -1
‘Set dItem = delItems.Item(n)
‘dItem.Delete
‘Next n
‘Caution: Do not use the For Each … Next vba statement as below, to delete (or move) items because it cannot handle the updated collection (ie. number of items) on deletion of an item, and not all items will get deleted. It is best to use a loop which down counts, as in this example.
‘Dim dItem As Object
‘For Each dItem In delItems
‘dItem.Delete
‘Next
End Sub
Example 12: Use the Do While … Loop vba statement to loop through all items in the default Inbox folder, using item Index.
Sub AutomateOutlook12()
‘Use the Do While … Loop vba statement to loop through all items in the default Inbox folder, using item Index.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim mFolder As Outlook.Folder
Dim mItems As Outlook.Items
Dim mItem As Outlook.MailItem
Dim count As Long, i As Long
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set the default Invbox folder:
Set mFolder = nsOutlook.GetDefaultFolder(olFolderInbox)
‘set the items collection:
Set mItems = mFolder.Items
i = 1
count = mItems.count
Do While i <= count
Set mItem = mItems.Item(i)
MsgBox mItem.Subject
i = i + 1
Loop
End Sub
Example 13: Use the For Each … Next vba statement to loop through all items in the Default Contacts folder, without using their Index. Each item is then posted to the specified Excel worksheet.
Sub AutomateOutlook13()
‘Use the For Each … Next vba statement to loop through all items in the Default Contacts folder, without using their Index. Each item is then posted to the specified Excel worksheet.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim cItems As Outlook.Items
Dim cItem As Outlook.ContactItem
Dim ws As Worksheet
‘set the worksheet:
Set ws = ActiveWorkbook.Sheets(“Sheet1”)
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the default Contact Items folder:
Set cFolder = nsOutlook.GetDefaultFolder(olFolderContacts)
‘set the items collection:
Set cItems = cFolder.Items
‘post each outlook item in the items collection, to a new worksheet row:
i = 0
For Each cItem In cItems
‘start posting in worksheet from the first row:
i = i + 1
ws.Cells(i, 1) = cItem.firstName
ws.Cells(i, 2) = cItem.LastName
ws.Cells(i, 3) = cItem.Email1Address
ws.Cells(i, 4) = cItem.MobileTelephoneNumber
Next
End Sub
Example 14: Reference an existing Outlook contact item, return and update its properties.
Sub AutomateOutlook14()
‘Reference an existing Outlook contact item, return and update its properties.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim cItem As Outlook.ContactItem
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the folder where the item is located:
Set cFolder = nsOutlook.Folders(“Personal Folders”).Folders(“Contacts”)
‘returns the number of items in the folder:
MsgBox cFolder.Items.count
‘find contact name in the folder:
For Each cItem In cFolder.Items
If cItem.FullName = “Alan Croft” Then
‘return the company name and email id of the contact item:
MsgBox “Company Name is ” & cItem.CompanyName & “, and Email Id is ” & cItem.Email1Address
‘replace existing email (“alanalan1 @alan.com”) with new email id (“alanNew @alan.com”):
cItem.Email1Address = Replace(cItem.Email1Address, “alanalan1 @alan.com”, “alanNew @alan.com”)
cItem.Save
MsgBox “done”
Exit Sub
End If
Next
End Sub
Find method: To search for a specific item in the items collection (ie. a specific folder) basis one of its values, use the Find method, and use the FindNext method for searching the next item based on the same value criteria.
Example 15: Using the Find Method, search a specific item in the default Contact Items folder basis one of its values.
Sub AutomateOutlook15()
‘Using the Find Method, search a specific item in the default Contact Items folder basis one of its values.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim cItems As Outlook.Items
Dim cItem As Outlook.ContactItem
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘set reference to the default Contact Items folder:
Set cFolder = nsOutlook.GetDefaultFolder(olFolderContacts)
‘set the items collection:
Set cItems = cFolder.Items
‘find the contact item name “Alan Croft”, in the folder:
Set cItem = cItems.Find(“[FullName]=“”Alan Croft”””)
‘return properties of the contact item name “Alan Croft”:
MsgBox cItem.Email1Address
MsgBox cItem.MobileTelephoneNumber
End Sub
Example 16: Use the Find and FindNext methods to search the default Inbox folder for emails which have no subject, and return their received time, sender’s name, attachments and the total number of such mails.
For live code of this example, click to download excel file.
Sub AutomateOutlook16()
‘Use the Find and FindNext methods to search the default Inbox folder for emails which have no subject, and return their received time, sender’s name, attachments and the total number of such mails.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim eFolder As Outlook.Folder
Dim eItems As Outlook.Items
Dim eItem As Outlook.MailItem
Dim att As Outlook.Attachment
Dim atts As Outlook.Attachments
Dim i As Long
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable eFolder to the default Inbox folder:
Set eFolder = nsOutlook.GetDefaultFolder(olFolderInbox)
‘set the items collection:
Set eItems = eFolder.Items
‘search emails in Inbox folder which have no subject:
Set eItem = eItems.Find(“[Subject]=“””””)
‘loop through all emails in the folder:
For i = 1 To eItems.count
‘Check if mail item (without subject) 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(eItem) = “Nothing” Then
MsgBox “No more mail without a Subject!”
MsgBox “Total Mails found without a subject: ” & i – 1
Exit Sub
End If
‘search result no. of the mail item being referenced:
MsgBox “Search Result No: ” & i
’email received time:
MsgBox “Received Time: ” & eItem.ReceivedTime
’email sender’s name:
MsgBox “Sender’s Name: ” & eItem.SenderName
‘name of each attachment in a mail item:
Set atts = eItem.Attachments
For Each att In atts
MsgBox “Attachment: ” & att.DisplayName
Next
‘search next mail item without a subject:
Set eItem = eItems.FindNext
Next i
MsgBox “Total Mails found without a subject: ” & i – 1
End Sub
Restrict Method. Use the Find method to find a specific item in an Outlook folder. To get additional items matching the same criteris, use the FindNext method as shown above. The Restrict Method is used to return a collection of items meeting a specified criteria.
Example 17: Use the Restrict Method to return a collection of mail items meeting a specified criteria viz. whose Importance is set to High.
Sub AutomateOutlook17()
‘Use the Restrict Method to return a collection of mail items meeting a specified criteria viz. whose Importance is set to High.
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim eFolder As Outlook.Folder
Dim eItems As Outlook.Items
Dim eItem As Outlook.MailItem
Dim eResItems As Outlook.Items
Dim strCriteria As String
Set applOutlook = New Outlook.Application
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘assign the object variable eFolder to the default Inbox folder:
Set eFolder = nsOutlook.GetDefaultFolder(olFolderInbox)
‘set the items collection:
Set eItems = eFolder.Items
‘set criteria viz. Importance set to High:
strCriteria = “[Importance]=“”High”””
‘set collection of items meeting the specified criteria:
Set eResItems = eItems.Restrict(strCriteria)
If eResItems.count < 1 Then
MsgBox “No Mail Item of High Importance”
Exit Sub
End If
For Each eItem In eResItems
’email received time:
MsgBox eItem.ReceivedTime
’email sender’s name:
MsgBox eItem.SenderName
’email subject:
MsgBox eItem.Subject
Next
End Sub