Collection Key Problem (Outlook 2000 +)

Topic

New Reply

I can’t figure why this won’t work. This code successfully gets the chosen Outlook folder and all subfolders into a collection, but if I activate the commented Key (in red) Collection field, I get a key duplication error, which I don’t see in the created Note as the code is now written. Can anybody see my problem?

Public colFolders As Collection

Public Function AllFoldersCollection(ByRef objRootFolder As MAPIFolder)
Set colFolders = New Collection
Call GetFoldersCollection(objRootFolder)
End Function

Private Function GetFoldersCollection(ByRef objStartFolder As MAPIFolder)
Dim objFolder As MAPIFolder
For Each objFolder In objStartFolder.Folders
colFolders.Add Item:=objFolder ‘, Key:=foldertree(objFolder)
If objFolder.Folders.Count > 0 Then _
Call GetFoldersCollection(objFolder) ‘recurse this function to subfolders
Next objFolder
Set objFolder = Nothing
End Function

Public Function foldertree(objFolder As MAPIFolder) As String
Do
foldertree = “” & objFolder.Name & foldertree
If UCase(objFolder.Parent) = “MAPI” Then Exit Do
Set objFolder = objFolder.Parent
Loop
End Function

From here down is code to test the collection:

Sub testfolderscol()
Dim nsNS As NameSpace
Dim fldrSel As MAPIFolder
Dim allSubFolders As New Collection
Dim itmNote As NoteItem
Dim lngC As Long
Dim strText As String

Set nsNS = Application.GetNamespace(“MAPI”)
Set fldrSel = nsNS.PickFolder
Call AllFoldersCollection(fldrSel)
Set allSubFolders = colFolders

If Not allSubFolders Is Nothing Then
Set itmNote = OpenNewNoteItem()
For lngC = 1 To allSubFolders.Count
‘What I want to do is the next commented line …
‘ strText = strText & allSubFolders(lngC).Key & vbLf ‘.Name & vbLf
‘but I have to do this …
strText = strText & foldertree(allSubFolders(lngC)) & vbLf
Next lngC
itmNote.Body = “Folder List” & vbLf & vbLf & strText
itmNote.Display
End If

Set allSubFolders = Nothing
Set fldrSel = Nothing
Set nsNS = Nothing
End Sub

Private Function OpenNewNoteItem() As NoteItem
Dim objNoteitem As NoteItem
Set objNoteitem = Outlook.Application.CreateItem(OLNoteItem)
Set OpenNewNoteItem = objNoteitem
End Function

Reply | Quote

Replies

There are two major problems:

1. The argument objFolder of the function foldertree is by default a ByRef argument. You keep on setting it back to its parent, so you get duplicates. You can solve this by making it into a ByVal argument.
2. Key is not a property of a member of a collection.

I don’t know what you want to use this for. If you only need the paths, use a collection of strings (folder paths) instead of a collection of folders. If you do need the folders, use two parallel collections, or use arrays as collection members:

Private Function GetFoldersCollection(ByRef objStartFolder As MAPIFolder)
Dim objFolder As MAPIFolder
For Each objFolder In objStartFolder.Folders
colFolders.Add Item:=Array(objFolder, foldertree(objFolder))
If objFolder.Folders.Count > 0 Then _
Call GetFoldersCollection(objFolder) ‘recurse this function to subfolders
Next objFolder
Set objFolder = Nothing
End Function

You can now use colFolders(lngC)(0) to get the folder object, and colFolders(lngC)(1) to get the folder path.

Reply | Quote

Thanks, Hans, you have fixed it. Objective is to supply a complete collection of Outlook folders in all ‘downstream paths’ so I can then read any folder and its item information. objFolder.Folders only gives me the parent and immediate children. Is there a better way to do this?

I don’t understand the need for your

colFolders.Add Item:=Array(objFolder, foldertree(objFolder))

syntax because the objCollection.Add Method is documented as:

object.Add item[, key, before, after]
key Optional. A unique string expression that specifies a key string that can be used, instead of a positional index, to access a member of the collection

… if I can get the folder name and path in the Key I can use the path string to address that particular folder rather than looping through them all. But I think I see that I was misusing Key to address the Collection: the Key is used as a substitute for the Member Item number, so after the function sets the collection something hypothetically like:

set objMyFolder = colFolders(“pathtojustthisfolder”)

Am I on the right track, or derailed again?

Reply | Quote

Your method should work, and is probably easier.

Reply | Quote

That was my theory. But on the 12th item, I get a duplicate key error; the first 36 characters of the folder paths for Items 11 & 12 are identical, but the entire folder paths for items 11 and 12 are not. I don’t see documented where the Key has a character limit, so I have some other error I’m not seeing, after a lot of messing about with MsgBox. I’ll have to write more detailed error trapping to see what’s happening. I’m not proficient with VBE debugging technique, guess it’s time to learn.

Reply | Quote

I don’t think there is a limit to the length of a key. As the help indicates, it is a string, no more and no less. I am able to create collection items with very long keys that are identical in all but the last character.

Try single-stepping (F8) through your code to see what is happening.

Reply | Quote

I got the second function (and the whole shebang) above working correctly when I re-code it as:

Private Function GetFoldersCollection(ByVal objStartFolder As MAPIFolder)
Dim lngC As Long
For lngC = 1 To objStartFolder.Folders.Count
colFolders.Add Item:=objStartFolder.Folders(lngC), _
Key:=foldertree(objStartFolder.Folders(lngC))
If objStartFolder.Folders(lngC).Folders.Count > 0 Then _
Call GetFoldersCollection(objStartFolder.Folders(lngC)) ‘recurse this function to subfolders
Next lngC
End Function

In the old code the Collection was being filled correctly, but
foldertree(objFolder)
was not. I don’t understand why. Could this be because in objMAPIFolder.Folders, Folders is a Collection (per the OL VBA Help), and can only be addressed by Index number?

Reply | Quote

Hmm, I don’t think so – you should be able to enumerate the elements of a collection both by For Each and by For lngC = 1 To …. But (and I’m just guessing here) perhaps it has to do with the recursion. Anyway, glad you got it to work.

Reply | Quote

I know you’re supposed to be able to enum a collection using For Each, but I’ve never had any luck trying it in VBA. I always fall back to a counter instead.

Reply | Quote

What further confused me is that if I used a Counter and then explicitly Set the object (which a For Each Object In … loop implicitly does):

Private Function GetFoldersCollection(ByVal objStartFolder As MAPIFolder)
Dim objFolder As Object
Dim lngC As Long
For lngC = 1 To objStartFolder.Folders.Count
Set objFolder = objStartFolder.Folders(lngC)
colFolders.Add Item:=objFolder, Key:=foldertree(objFolder)
… etc.

it still didn’t work.

Reply | Quote

What further confused me is that if I used a Counter and then explicitly Set the object (which a For Each Object In … loop implicitly does):

Private Function GetFoldersCollection(ByVal objStartFolder As MAPIFolder)
Dim objFolder As Object
Dim lngC As Long
For lngC = 1 To objStartFolder.Folders.Count
Set objFolder = objStartFolder.Folders(lngC)
colFolders.Add Item:=objFolder, Key:=foldertree(objFolder)
… etc.

it still didn’t work.

Reply | Quote

I know you’re supposed to be able to enum a collection using For Each, but I’ve never had any luck trying it in VBA. I always fall back to a counter instead.

Reply | Quote

Hmm, I don’t think so – you should be able to enumerate the elements of a collection both by For Each and by For lngC = 1 To …. But (and I’m just guessing here) perhaps it has to do with the recursion. Anyway, glad you got it to work.

Reply | Quote

I got the second function (and the whole shebang) above working correctly when I re-code it as:

Private Function GetFoldersCollection(ByVal objStartFolder As MAPIFolder)
Dim lngC As Long
For lngC = 1 To objStartFolder.Folders.Count
colFolders.Add Item:=objStartFolder.Folders(lngC), _
Key:=foldertree(objStartFolder.Folders(lngC))
If objStartFolder.Folders(lngC).Folders.Count > 0 Then _
Call GetFoldersCollection(objStartFolder.Folders(lngC)) ‘recurse this function to subfolders
Next lngC
End Function

In the old code the Collection was being filled correctly, but
foldertree(objFolder)
was not. I don’t understand why. Could this be because in objMAPIFolder.Folders, Folders is a Collection (per the OL VBA Help), and can only be addressed by Index number?

Reply | Quote

I don’t think there is a limit to the length of a key. As the help indicates, it is a string, no more and no less. I am able to create collection items with very long keys that are identical in all but the last character.

Try single-stepping (F8) through your code to see what is happening.

Reply | Quote

That was my theory. But on the 12th item, I get a duplicate key error; the first 36 characters of the folder paths for Items 11 & 12 are identical, but the entire folder paths for items 11 and 12 are not. I don’t see documented where the Key has a character limit, so I have some other error I’m not seeing, after a lot of messing about with MsgBox. I’ll have to write more detailed error trapping to see what’s happening. I’m not proficient with VBE debugging technique, guess it’s time to learn.

Reply | Quote

Your method should work, and is probably easier.

Reply | Quote

Thanks, Hans, you have fixed it. Objective is to supply a complete collection of Outlook folders in all ‘downstream paths’ so I can then read any folder and its item information. objFolder.Folders only gives me the parent and immediate children. Is there a better way to do this?

I don’t understand the need for your

colFolders.Add Item:=Array(objFolder, foldertree(objFolder))

syntax because the objCollection.Add Method is documented as:

object.Add item[, key, before, after]
key Optional. A unique string expression that specifies a key string that can be used, instead of a positional index, to access a member of the collection

… if I can get the folder name and path in the Key I can use the path string to address that particular folder rather than looping through them all. But I think I see that I was misusing Key to address the Collection: the Key is used as a substitute for the Member Item number, so after the function sets the collection something hypothetically like:

set objMyFolder = colFolders(“pathtojustthisfolder”)

Am I on the right track, or derailed again?

Reply | Quote

There are two major problems:

1. The argument objFolder of the function foldertree is by default a ByRef argument. You keep on setting it back to its parent, so you get duplicates. You can solve this by making it into a ByVal argument.
2. Key is not a property of a member of a collection.

I don’t know what you want to use this for. If you only need the paths, use a collection of strings (folder paths) instead of a collection of folders. If you do need the folders, use two parallel collections, or use arrays as collection members:

Private Function GetFoldersCollection(ByRef objStartFolder As MAPIFolder)
Dim objFolder As MAPIFolder
For Each objFolder In objStartFolder.Folders
colFolders.Add Item:=Array(objFolder, foldertree(objFolder))
If objFolder.Folders.Count > 0 Then _
Call GetFoldersCollection(objFolder) ‘recurse this function to subfolders
Next objFolder
Set objFolder = Nothing
End Function

You can now use colFolders(lngC)(0) to get the folder object, and colFolders(lngC)(1) to get the folder path.

Reply | Quote