For Each Loop over folders that moves folders is skipping some folders

Question

I am trying to loop through all the folders within a sub-folder in Outlook to see if the string is inside an Excel sheet.

The loop is skipping some folders and so I have to rerun the macro. While debugging The loop skips three items in a test case.

Dim outlookFolder As Variant
Dim numOutlookFolders As Integer: numOutlookFolders = 0
Debug.Print POFolder.Folders.Count
For Each outlookFolder In POFolder.Folders
    Found = False
    For Each PO In tbl.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
        If InStr(outlookFolder.Name, PO) Then
            Found = True
            'Exit For
        End If
    Next PO
    
    If Found = False Then
        If InStr(outlookFolder.Name, "PO") Then
            outlookFolder.MoveTo CompletedFolder
            Dim folderName As String: folderName = outlookFolder.Name
            fileStream.WriteLine folderName
            Count = Count + 1
        End If
    End If
    numOutlookFolders = numOutlookFolders + 1
Next outlookFolder
Debug.Print numOutlookFolders

Debug Window Output:

49
46

If InStr(outlookFolder.Name, PO) Then doesn;t have quotes around "PO" - is it a variable? — dbmitch, Aug 21 '21 at 05:05
You're renaming a folder name while still in a loop - you'll have to change that so you do the move outside the loop. Store the folder names in an array, Then process after done finding the matches. Otherwise your list of folder items gets mixed up — dbmitch, Aug 21 '21 at 05:13
You may find the following code to recurse folder structures handy to create the search data instead: https://stackoverflow.com/questions/64007790/how-can-one-iterate-through-the-subfolders-of-a-subfolder-of-a-shared-mail-inbox — Tragamor, Aug 24 '21 at 16:42

score 0 · Answer 1 · answered Aug 21 '21 at 11:09

To make sure the loop is working or not correctly you need to remove all the code inside:

Dim outlookFolder As Variant
Dim numOutlookFolders As Integer: numOutlookFolders = 0

Debug.Print POFolder.Folders.Count

For Each outlookFolder In POFolder.Folders
    numOutlookFolders = numOutlookFolders + 1
Next outlookFolder

Debug.Print numOutlookFolders

If you get the same numbers in the output you may start adding the code line-by-line, so you will be able to understand what line of code causes the issue.

If you don't get the same numbers in the output you may try to find which folder exactly causes the issue.

score 0 · Accepted Answer · answered Aug 22 '21 at 23:33

0

You should not use for each for a loop that changes the collection (by calling outlookFolder.Move) you are iterating over.

Use a down for loop (from count down to 1).

answered Aug 22 '21 at 23:33

Dmitry Streblechenko

62,942
4
53
78

For Each Loop over folders that moves folders is skipping some folders

2 Answers2