I have managed to get access to the items in 2 folders in Outlook from Excel by using VBA, but now I want to search for the email address [email protected] in both aI know how to search each one individually, but once, and sort for the most recent one. The piece I am stuck on is how to look through both folders at once.

I am using Microsoft Office 2016

Obviously, this dummy line does not do the trick: Set olJoinedFldr = olCleanUp olFldr

Private Sub CommandButton2_Click()

Dim olApp As Outlook.Application 'set app
Dim olNs As Object 'get namespace
Dim olFldr As Outlook.Folder 'to be the inbox
Dim olArchive As Outlook.Folder 'the archive folder
Dim olCleanUp As Outlook.Folder ' the archive subfolder we need
Dim olJoinedFldr As Object  'the to be made joined object to filter....
Dim olItems As Object 'filtered items based on search criteria
Dim olItemReply As Object 'the reply mail
Dim i As Long
Dim emailStr As String
Dim filter As String

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))) 'find email of current user
Set olCleanUp = olArchive.Folders("Archive").Folders("Cleanup") ' get the archive sub folder

Set olJoinedFldr = olCleanUp   olFldr

Set emailStr = "[email protected]"
filter = "[SenderEmailAddress] = """ & emailStr & """"  'this is the email from person x we are searching for in the 2 folders

' from here on it is currently searching just 1 folder
Set olItems = olFldr.Items.Restrict(filter) 'filter the items

olItems.Sort "[ReceivedTime]", True 'sort by date

If olItems.Count > 0 Then
    For i = 1 To olItems.Count
        If olItems(i).Class = 43 Then
            Set olItemReply = olItems(i).ReplyAll
            With olItemReply
                .HTMLBody = "<p Dear someone, <br><br></p>" & .HTMLBody
                .Display
            End With
        
        Exit For
    End If

Next
Else
' have code here to make a brand new email already
End If

Set olApp = Nothing
Set olNs = Nothing
Set olFldr = Nothing
Set olArchive = Nothing
Set olCleanUp = Nothing
Set olJoinedFldr = Nothing
Set olItems = Nothing
Set olItemReply = Nothing
Set i = Nothing
Set emailStr = Nothing
Set filter = Nothing


End Sub

CodePudding user response：

You cannot search through two (or more) folders unless you create a Search object using Application.AdvancedSearch. Even then, it is a PITA to work with - the search is asynchronous, and you would need to use events to figure out when the search is completed.

You'd be better off searching one folder at a time and combining the results (if necessary) in your code.

CodePudding user response：

You need to use the AdvancedSearch method of the Application class. 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). You can read more about this in the Filtering article in MSDN. 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 this method in the Advanced search in Outlook programmatically: C#, VB.NET article.