'myItems.sort works for one Outlook subfolder but doesn't on others

The below Excel VBA code works for one subfolder (extracts latest attachment) but when applied to another subfolder it extracts information from the oldest email, not the newest.

Is myFolder.Items.sort the right method?

Sub SaveAttachments_RsConfirmation()
Dim myOlapp As Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myFolder As Outlook.MAPIFolder
Dim myItem As Outlook.MailItem
Dim myAttachment As Outlook.Attachment
Dim I As Long

Set myOlapp = CreateObject("Outlook.Application")
Set myNameSpace = myOlapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myFolder = myFolder.Folders("Rs.Confirmation")

myFolder.Items.Sort "[ReceivedTime]", True

For Each myItem In myFolder.Items
    myFolder.Items.Sort "[ReceivedTime]", True
    If myItem.Attachments.Count <> 0 Then
        For Each myAttachment In myItem.Attachments
            
            I = 1
            myAttachment.SaveAsFile "C:\Del.Gen.v1\Confirmation.Email\" & I & ".txt"
            eSender = myItem.SenderEmailAddress
            dtRecvd = myItem.ReceivedTime
            dtSent = myItem.CreationTime
            sSubj = myItem.Subject
            sMsg = myItem.Body
            
            Exit For
        Next
    End If
Next

Workbooks("Del.Gen.v1.xlsm").Worksheets("Sheet4").Range("A1").Value = eSender
Workbooks("Del.Gen.v1.xlsm").Worksheets("Sheet4").Range("A2").Value = dtRecvd
Workbooks("Del.Gen.v1.xlsm").Worksheets("Sheet4").Range("A3").Value = dtSent
Workbooks("Del.Gen.v1.xlsm").Worksheets("Sheet4").Range("A4").Value = sSubj
Workbooks("Del.Gen.v1.xlsm").Worksheets("Sheet4").Range("A5").Value = sMsg

Debug.Print eSender
Debug.Print dtRecvd
Debug.Print dtSent
Debug.Print sSubj
Debug.Print sMsg

End Sub

excel vba outlook

Solution 1:^[1]

You are sorting one Items collection, but end up using a completely different object - every time you call MAPIFolder.Items, you get back a brand new COM object that has no knowledge of any other instances. Read the items collection once, store it in a variable, then loop through its items:

set myItems = myFolder.Items
myItems.Sort "[ReceivedTime]", True
For Each myItem In myItems 
  ...

Solution 2:^[2]

myItems.Sort would be fine.

myFolder.Items.Sort despite documentation you may have seen is not valid.

Sub SaveAttachments_RsConfirmation()

Dim myOlapp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myFolder As Outlook.MAPIFolder

Dim myItems As Outlook.items

Set myOlapp = CreateObject("Outlook.Application")
Set myNameSpace = myOlapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)

Set myFolder = myFolder.folders("Rs.Confirmation")

' Attempt to sort items in the folder
myFolder.items.Sort "[ReceivedTime]", True
Debug.Print myFolder.items(1).Subject

' False should be no different from True
myFolder.items.Sort "[ReceivedTime]", False
Debug.Print myFolder.items(1).Subject

' Create a collection of items
Set myItems = myFolder.items
myItems.Sort "[ReceivedTime]", True
Debug.Print myItems(1).Subject

' False should sort opposite to True
myItems.Sort "[ReceivedTime]", False
Debug.Print myItems(1).Subject

ExitRoutine:
    Set myOlapp = Nothing
    Set myNameSpace = Nothing
    Set myFolder = Nothing
    Set myItems = Nothing

End Sub

Solution 3:^[3]

I see you sort the folder, take each item and sort the whole folder again. This is probably not what you mean.

myFolder.Items.Sort "[ReceivedTime]", True           ' sort folder

For Each myItem In myFolder.Items                    ' process each item
    myFolder.Items.Sort "[ReceivedTime]", True       ' sort folder again: remove this line
    If myItem.Attachments.Count <> 0 Then
        For Each myAttachment In myItem.Attachments
            I = 1                                     ' shouldn't this be incremented?

Note that if I is not incremented, all attachements will be placed (replaced)in the same file, so you see only one attachment, which will be the one from the last email in the folder.

I am not sure what your problem/question is. First, I would remove the 2nd sort command. Re-sorting could mess-up the order in whichmyItem is obtained form the collection.

But if you want to go into each sub folder, and sub folder, etcetera, you must develop a recursive procedure that sorts a folder, process each item, if item is a folder, descent into sub folder, sort folderr, etc.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	niton
Solution 3	Paul Ogilvie

'myItems.sort works for one Outlook subfolder but doesn't on others

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]