VBA in Outlook to pull specific data into excel

Question

I need some VBA code in outlook to save specific lines of data from emails as per below into an excel folder - see sample excel table below. I need the code to search all emails in my personal inbox only and save into the excel file in a specific location. Any ideas?

EMAIL:

From: [email protected]
to: [email protected]

Subject: Client Orders
Product: Trunk1
Units: 463

Thanks

Excel file saved in a specific location:

Client Sales    Product       Units
xx                   xx        xx

Answer 1

You can use the Restrict or Find/FindNext methods of the Items class to find items in the folder that correspond to your conditions/search criteria. If you need to search for items in multiple folders you may consider using the AdvancedSearch method of the Application class. Read more about them in the following articles:

• How To: Use Find and FindNext methods to retrieve Outlook mail items from a folder (C#, VB.NET)
• How To: Use Restrict method to retrieve Outlook mail items from a folder
• Advanced search in Outlook programmatically: C#, VB.NET

For example, the following code searches for items from a specified sender and then calls a method to export the data from an item found to an Excel workbook:

Sub ExportItemsFromSender() 
 Dim myNameSpace As Outlook.NameSpace 
 Dim myInbox As Outlook.Folder 
 Dim myDestFolder As Outlook.Folder 
 Dim myItems As Outlook.Items 
 Dim myItem As Object 
 
 Set myNameSpace = Application.GetNamespace("MAPI") 
 Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox) 
 Set myItems = myInbox.Items 
 
 Set myItem = myItems.Find("[SenderName] = 'Eugene Astafiev'") 
 
 While TypeName(myItem) <> "Nothing" 
   ExportItemToExcelWorkbook myItem 
   Set myItem = myItems.FindNext 
 Wend 
End Sub

The How to automate Microsoft Excel from Visual Basic article explains how to automate Excel from another application (Outlook VBA in your case).