Sorting Items in Outlook

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.

Sub GetFromInbox()

    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
    olItms.Sort “Subject”
    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
        End If
    Next olMail

    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub

I had 87 emails in my Inbox with “excel” in them. Maybe time to clean that out?

Posted in Uncategorized

8 thoughts on “Sorting Items in Outlook

  1. Hi Dick,

    Thanks for posting this code above and also what is on your website. I’ve been trying to learn more about the Outlook object model since trying to implement the Getting Things Done system, and this has been very helpful.

    In the last couple of days, I’ve created two macros. The first creates a note and lists all my current projects in it. The second macro uses a userform to let me place the project title (using a listbox populated by project folders) on the subject line of a task, which will allow me to easily create a project and next action tracking system in Excel.

    Thanks again,


  2. I’ve found a situation where I think I’ve sorted the collection, but it doesn’t traverse the list in order. I wanted to only search back a certain number of days, in case the number of messages gets too big. Previously it would exit the sub as soon as the date fell out of range. This seemed to miss some emails. I tried ‘for each’ and using and index, but with no luck. Any ideas?

    Set flr = f.Folders(“Inbox”)
    flr.Items.Sort “Receivedtime”

    With flr.Items
    ‘ For i = 1 To .count
    For Each itm2 In flr.Items
    ‘set itm2=.item(i)
    If TypeName(itm2) = “MailItem” Then
    ‘ Set itm2 = .item(i)
    dt = itm2.ReceivedTime
    If DateDiff(“d”, dt, Date) >= cboDays.Text Then GoTo skippy ‘ previously would exit here

    bd = itm2.body

    sb = itm2.subject
    from = itm2.SenderName
    sentto = itm2.To
    isauto = InStr(bd, AUTOIDXTXT) > 0
    If cbAutoIndexOnly = 0 Or isauto Then Addemail from, dt, sb, bd, sentto, itm2.entryid, itm2.htmlbody
    End If

  3. Hi there,

    I think there was a MSFT security update that changed the way VBA accesses Outlook. It is no longer possible to get SenderName. I may be wrong, but I can’t seem to get this to work in Excel on my computer.


  4. Any ideas on how to create a threading structure in outlook to organize messages by thread, like a messageboard? This would be extremely helpful in organizing messages – also to remove duplicate content in the replies?

  5. If InStr(olMail.Body, “excel”) > 0
    What does this mean?
    I’m writing a macro to retrive the body of an outlook 2007 messages into excel.

  6. Angel: Instr means one string is inside of a another. If “excel” is in the body of the email, Instr will return its starting position (an integer). If it can’t find “excel” anywhere in the body, it will return 0.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.