Join n outlook items into one new object with excel vba

Question

I am looking for emails from person x in n outlook folders in Excel with VBA. What I want to do is find the most recent item of the n results (or of more folders).

I considered merging the n objects, sort by ReceivedTime and then get the top item, but I can't manage merging them, or find the most recent of the n objects.

Example is for 2 folders, 2 items:

    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.Folder 'to be the inbox
    Dim olArchive As Outlook.Folder 'my archive folder
    Dim olItems As Outlook.Items
    Dim olArchiveItems As Outlook.Items
    Dim i As Long
    Dim emailStr As String
    Dim filter As String
    Dim olSentFldr as Outlook.Folder

    Set olApp = CreateObject("Outlook.Application")
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFldr = olNs.GetDefaultFolder(6) ' olFolderInbox
    Set olArchive = olNs.Folders(CStr(olNs.Accounts.Item(1)))
    
   Set olSentFldr = olNs.GetDefaultFolder(olFolderSentMail)

       emailStr = "[email protected]"
         
        filter = "[SenderEmailAddress] = """ & emailStr & """"
        Set olItems = olFldr.Items.Restrict(filter)
        Set olArchiveItems = olArchive.Items.Restrict(filter)
    
        olItems.Sort "[ReceivedTime]", True
        olArchiveItems.Sort "[ReceivedTime]", True
        olSentFldr.Sort "[ReceivedTime]", True


    Dim olNew as Object 
   
` below hypothetical solution that does not work yet--------------
    olNew = merge(olItems(1), olArchiveItems(1))
    olNew.Sort "[ReceivedTime]", True
    myOutcome = olNew(1)

Answer 1

You can compare search results.

Option Explicit

Private Sub mostRecentItem_MultipleSearches()

    ' Early Binding - requires reference to Microsoft Outlook XX.X Object Library
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.namespace
    
    Dim olFldr As Outlook.Folder 'to be the inbox
    Dim olSentFldr As Outlook.Folder
    
    Dim olFldrItems As Outlook.Items
    Dim olSentFldrItems As Outlook.Items
    
    Dim olItemRecent As Object
    
    Dim i As Long
    Dim emailStr As String
    Dim filter As String
    
    Set olApp = CreateObject("Outlook.Application")
    Set olNs = olApp.GetNamespace("MAPI")
    
    ' valid with early binding
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox)   ' 6 if late binding
    Set olFldrItems = olFldr.Items
    Debug.Print "olFldrItems.count: " & olFldrItems.count
    
    emailStr = "[email protected]"
    filter = "[SenderEmailAddress] = """ & emailStr & """"
    
    olFldrItems.Sort "[ReceivedTime]", True
    Set olFldrItems = olFldrItems.Restrict(filter)
    Debug.Print "olFldrItems.count: " & olFldrItems.count
    
    Set olItemRecent = olFldrItems(1)
    'olItemRecent.Display
    
    Set olSentFldr = olNs.GetDefaultFolder(olFolderSentMail)
    Set olSentFldrItems = olSentFldr.Items
    olSentFldrItems.Sort "[SentOn]", True
    
    Debug.Print "olSentFldrItems.count: " & olSentFldrItems.count
    
    Debug.Print olItemRecent.ReceivedTime
    Debug.Print olSentFldrItems(1).SentOn
    
    If olItemRecent.ReceivedTime < olSentFldrItems(1).SentOn Then
         Set olItemRecent = olSentFldrItems(1)
    End If
    
    olItemRecent.Display

End Sub

Answer 2

First of all, you need to Sort collection before running the Restrict of Find/FindNext methods if you want to get items ordered.

olItems.Sort "[ReceivedTime]", True
olArchiveItems.Sort "[ReceivedTime]", True
olSentFldr.Sort "[ReceivedTime]", True

filter = "[SenderEmailAddress] = """ & emailStr & """"
Set olItems = olItems.Restrict(filter)
Set olArchiveItems = olArchiveItems.Restrict(filter)

Try using not a straight comparison in the search string:

filter = Chr(34) & "[SenderEmailAddress]" & Chr(34) & " like '%" & emailStr &"'"`

It looks like you need to use the AdvancedSearch method of the Application class which performs a search based on a specified DAV Searching and Locating (DASL) search string. You can run the search in multiple folders at once. So, there is no need to run the search separately for each folder:

Set olItems = olFldr.Items.Restrict(filter)
Set olArchiveItems = olArchive.Items.Restrict(filter)

You can run it once for all folders and the search is performed in the background. The key benefits of using the AdvancedSearch method in Outlook are:

• The search is performed in another thread. You don’t need to run another thread manually since the AdvancedSearch method runs it automatically in the background.
• Possibility to search for any item types: mail, appointment, calendar, notes etc. in any location, i.e. beyond the scope of a certain folder. The Restrict and Find/FindNext methods can be applied to a particular Items collection (see the Items property of the Folder class in Outlook).
• Full support for DASL queries (custom properties can be used for searching too). To improve the search performance, Instant Search keywords can be used if Instant Search is enabled for the store (see the IsInstantSearchEnabled property of the Store class).
• You can stop the search process at any moment using the Stop method of the Search class.

Read more about the AdvancedSearch method in the Advanced search in Outlook programmatically: C#, VB.NET article.