CODEWITHSUNDEEP
excelvbasearchoutlook
Mark
Mark

Reputation: 2889

Look for emails from person x in n Outlook folders using Excel VBA

I am looking for emails from person x in n Outlook folders using Excel VBA.

I want 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 two folders, two 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)

Upvotes: 0

Views: 302

Answers (2)

niton
niton

Reputation: 9179

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

Upvotes: 2

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

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.

Upvotes: 0

Related Questions