EXCEL VBA - Problem to search an email in Outlook folder

[kumthekarg]

Hi All,

I have a code for EXCEL VBA that searches for email messages in certain folder of Outlook ( both 2007 version). However, the comparison starts from the lastest email in the folder and not from the first email. ( in Outlook, I prefer to keep the email messages sorted as Newest first and would like to get the first email that matches the subject line provided.

Can you please help me reverse the order in which the EXCEL VBA is searching for the email? Below is the code:


Sub Otsearch()

Dim myOlApp As Outlook.Application

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myfolders = myNameSpace.Folders

n = 1
Do Until myfolders.Item = "Personal Folders"
n = n + 1
Loop

Set myfolder = myfolders.Item
Set myfolder2 = myfolder.Folders("Me")
Range("C23") = 0
c = 1
n = 1
num = myfolder2.items.Count


For Each Item In myfolder2.items
itsj = Item.Subject
If itsj <> "Open Cases - Oct_10_2_AM.xls" Then GoTo 10
itsn = Item.SenderName
Range("B23") = "Match FOund"
Range("C23") = Range("C23") + 1
10
n = n + 1
Range("A23") = n - 1
Cells(23 + n - 1, 1) = itsj

Next Item

End Sub



Thanks in advance for help.


Regards,
Ganesh K

 

[s.ridd]

Scrolling through an Items collection with "For Each Item" will always start at number 1 and work its way to the end. So, in order to go the other way try something like this

For i = myfolder2.Items.Count To 1 Step -1
    Set myItem = myfolder2.Items(i)
    itsj = myItem.Subject
    'rest of your code here
Next i

Hope this helps

Simon