VBA how to loop through emails in outlook starting on the most recent one-CodePudding

I'd like to create a program that loops through all the emails on my inbox but stops as soon as it gets a hit (to make the program lighter). This one already works but the hit I get is the oldest one. I'd like to start my loop on the most recent one. Here's the code

Dim objNS As Outlook.Namespace: Set objNS = GetNamespace("MAPI")
Dim olFolder As Outlook.MAPIFolder
Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
Dim Item As Object

For Each Item In olFolder.Items
    If TypeOf Item Is Outlook.MailItem Then
        Dim oMail As Outlook.MailItem: Set oMail = Item
        If InStr(oMail.Subject, "Whatever") > 0 Then
            'Do something
            Exit For
        End If
    End If
Next

CodePudding user response：

You can use the Items.Sort method of the MAPIFolder object.

Dim objNS As Outlook.Namespace: Set objNS = GetNamespace("MAPI")
Dim olFolder As Outlook.MAPIFolder
Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
olFolder.Items.Sort "ReceivedTime", True  ' Sort items by received date in descending order
Dim Item As Object

For Each Item In olFolder.Items
    If TypeOf Item Is Outlook.MailItem Then
        Dim oMail As Outlook.MailItem: Set oMail = Item
        If InStr(oMail.Subject, "Whatever") > 0 Then
            'Do something
            oMail.ReplyAll 
            oMail.Display 'This will open the reply window
            Exit For
        End If
    End If
Next

CodePudding user response：

Never loop through all items in a folder, always use Items.Find/FindNext (if you want to find one or more items) or Items.Restrict (if you want to find all matches).

If you want the items in a particular order, sort them first using Items.Sort, e.g., Items.Sort "ReceivedTime", true

In you participial case, use a query like the one below. Note that it is on the PR_NORMALIZED_SUBJECT MAPI property since it is indexed (unlike PR_SUBJECT).

@SQL="http://schemas.microsoft.com/mapi/proptag/0x0E1D001F" LIKE '%whatever%'

The following will do the job:

set oItems = olFolder.Items
oItems.Sort "ReceivedTime", true
set oMail = oItems.Find "@SQL=""http://schemas.microsoft.com/mapi/proptag/0x0E1D001F"" LIKE '%whatever%'"
if not (oMail is Nothing) Then
  'Do something
End If