Solved: Export Folder Names to Excel

[Jomili]

Hi.

I have literally hundreds of folders within folders within folders, and would like to generate a list (into Excel) of the folderpaths of all the folders within my Outlook. I've been looking on the web, and have only found one possible solution, the code below. It was posted at Ozgrid, but I'm not permitted to post links yet, so can't point directly to it. The guy who posted it never had anyone reply to his thread, so I don't know how good it is.

I've never done macros in Outlook, though I have in Excel, and I can't get his code to work (I'm not even sure how to initiate it). I'd appreciate someone either telling me how to get this code working, or pointing me to some alternative code to achieve the same results. Thanks in advance for your help.


Option Explicit

'Variable to keep an overall row number
Public RowNo As Integer

Private Sub CommandButton1_Click()
'Used to keep track of the number of folders
Dim lCountOfFound As Long
'The output range
Dim rngPath As Range
'Starting the row number off
RowNo = 1
'Call this sub procedure
WalkFolders
End Sub

Sub WalkFolders()
Dim olApp As Outlook.Application
Dim olSession As Outlook.Namespace
Dim olStartFolder As Outlook.MAPIFolder
lCountOfFound = 0
Set olApp = New Outlook.Application
Set olSession = olApp.GetNamespace("MAPI")
' Allow the user to pick the folder in which to start the search.
'or use GetDefaultFolder(olFolderInbox).Parent instead of the PickFolder
Set olStartFolder = olSession.PickFolder
' Check to make sure user didn't cancel PickFolder dialog.
If Not (olStartFolder Is Nothing) Then
' Start the search process.
ProcessFolder olStartFolder
End If
End Sub

Sub ProcessFolder(CurrentFolder As Outlook.MAPIFolder)
Set rngPath = Cells(RowNo, 1)
Dim i As LongDim
olNewFolder As Outlook.MAPIFolder
' late bind this object variable, since it could be various item types
Dim olTempFolder As Outlook.MAPIFolder
Dim olTempFolderPath As String
' Loop through the items in the current folder.
' Looping through backwards in case items are to be deleted,
' as this is the proper way to delete items in a collection.
For i = CurrentFolder.Folders.Count To 1 Step -1
Set olTempFolder = CurrentFolder.Folders(i)
'Puts File Path into Excel here
olTempFolderPath = olTempFolder.FolderPath
Cells(RowNo, 1) = olTempFolderPath
RowNo = RowNo + 1
lCountOfFound = lCountOfFound + 1
Next
' Loop through and search each subfolder of the current folder.
For Each olNewFolder In CurrentFolder.Folders
'Don't need to process the Deleted Items folder
If olNewFolder.Name <> "Deleted Items" Then
ProcessFolder olNewFolder
End If
Next
End Sub

Cheers,

[Crocus Crow]

The code goes in an Excel sheet module. Run it by running CommandButton1_Click from the Excel VB editor, or put a command button on the sheet and click it. Note that you need a reference to the Outlook object library for the code to compile.

[Jomili]

Thanks for that bit of direction. Now, how do I "reference the Outlook object library"?

[Crocus Crow]

To reference the library, click Tools - References in the VB Editor for your project. Scroll to the highest numbered Microsoft Outlook Object Library (11.0 for Outlook 2003), tick it and click OK.

Try Googling first if you have any more questions or problems with this code.

[Jomili]

Got it! Your last bit of direction did the trick. The macro errored a few times, due to a lack of Dim statements, but I corrected all of the those, and it now works beautifully. The corrected code is below. Option Explicit

'Variable to keep an overall row number
Public RowNo As Integer

Private Sub CommandButton1_Click()
'Used to keep track of the number of folders
Dim lCountOfFound As Long
'The output range
Dim rngPath As Range
'Starting the row number off
RowNo = 1
'Call this sub procedure
WalkFolders
End Sub

Sub WalkFolders()
Dim olApp As Outlook.Application
Dim olSession As Outlook.Namespace
Dim olStartFolder As Outlook.MAPIFolder
Dim lCountOfFound As Variant
lCountOfFound = 0
Set olApp = New Outlook.Application
Set olSession = olApp.GetNamespace("MAPI")
' Allow the user to pick the folder in which to start the search.
'or use GetDefaultFolder(olFolderInbox).Parent instead of the PickFolder
Set olStartFolder = olSession.PickFolder
' Check to make sure user didn't cancel PickFolder dialog.
If Not (olStartFolder Is Nothing) Then
' Start the search process.
ProcessFolder olStartFolder
End If
End Sub

Sub ProcessFolder(CurrentFolder As Outlook.MAPIFolder)
Dim rngPath As Range
Set rngPath = Cells(RowNo, 1)
Dim i As Long
Dim olNewFolder As Outlook.MAPIFolder
' late bind this object variable, since it could be various item types
Dim olTempFolder As Outlook.MAPIFolder
Dim olTempFolderPath As String
Dim lCountOfFound As Variant
' Loop through the items in the current folder.
' Looping through backwards in case items are to be deleted,
' as this is the proper way to delete items in a collection.
For i = CurrentFolder.Folders.Count To 1 Step -1
Set olTempFolder = CurrentFolder.Folders(i)
'Puts File Path into Excel here
olTempFolderPath = olTempFolder.FolderPath
Cells(RowNo, 1) = olTempFolderPath
RowNo = RowNo + 1
lCountOfFound = lCountOfFound + 1
Next
' Loop through and search each subfolder of the current folder.
For Each olNewFolder In CurrentFolder.Folders
'Don't need to process the Deleted Items folder
If olNewFolder.Name <> "Deleted Items" Then
ProcessFolder olNewFolder
End If
Next
End Sub

[stuartjohnwo]

Guys,

I just can't get this to work; keep getting a run-time error.

Any ideas?

SJW

[Jomili]

What's the specific error you're getting, and what line of code is being highlighted?

[stuartjohnwo]

Hi,


Run-time error '1004':

Application-defined or object-defined error.

When I debug, the following is highlighted:


Set rngPath = Cells(RowNo, 1)

[Jomili]

Hmmm, the code looks right, and works well for me. That said, I'm running Excel 2003, and Outlook 2003. How about you?

Did you reference the Outlook object library?

I'm attaching a working copy for you to try. Let's see if it works for you. If so, the code is unlocked, you can go through and see what differs.

[stuartjohnwo]

Ok, that seems to work fine.

Not sure what was up then. I copied the code from above. Thanks very much for that.

Now I need to find a way to get the content of the folders to come across as well (To, Sender Email Address, Subject, Sent On and Received Time fields as well as the Outlook Folder name).

Any ideas?

Cheers again

SJW

[Jomili]

Not from me. What I don't know about working with Outlook would fill volumes.

[Charlize]

Now I need to find a way to get the content of the folders to come across as well (To, Sender Email Address, Subject, Sent On and Received Time fields as well as the Outlook Folder name).
SJW- Inside the loop of the folders you need to create another loop.
- This loop goes through the collection of items in the folder.
- Each item is checked if you have a mailitem (message folders can have lots of things besides mail items)
- If it's a mailitem, you store it in a variable declared as mailitem
- with that object, you can acces the required info

Charlize