When you loop through the Items in a Folder in Outlook, the items may not be in the order you want. If you want to loop though them in a specific order, you need to create an Items Collection variable and use the Sort method on that variable.
The code at Retrieving E-mail shows how to loop through a folder without regard to the order of the items. The following code sorts the Items in the Inbox by Subject before looping through them. Note that using the Sort method on an Items Collection variable doesn’t change the sort in the Outlook user interface.
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olMail As Variant
Dim i As Long
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace(“MAPI”)
Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
Set olItms = olFldr.Items
i = 1
For Each olMail In olItms
If InStr(olMail.Body, “excel”) > 0 Or _
InStr(olMail.Subject, “excel”) > 0 Then
ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime
ActiveSheet.Cells(i, 2).Value = olMail.Subject
i = i + 1
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
I had 87 emails in my Inbox with “excel” in them. Maybe time to clean that out?