Outlook folder structure

[lapta301]

I am currently running Outlook 2010 and a very old version of Excel - 2002 I think which is due to be upgraded to 2010 in due course.

The question is on the surface quite basic - Is there a way to bring into Excel the folder structure and their sizes from Outlook

As ever many thanks for any help you can provide

 

[John_w]

Try this. You must set a reference to MS Outlook library in the VBA project.

Public Sub Get_Outlook_Folders()

    Dim outNameSpace As Namespace
    Dim outStartFolder As MAPIFolder
    Dim headings As Variant
    
    headings = Array("Folder Path", "Size", "Items", "Unread", "Structure")
        
    Set outNameSpace = Outlook.Application.GetNamespace("MAPI")   
    Set outStartFolder = outNameSpace.Folders("Personal Folders")
    
    With Range("A1")
        .Parent.Cells.Clear
        .Resize(1, UBound(headings) + 1).Value = headings
    End With
    
    Process_Folder2 outStartFolder, Range("A2")

End Sub

Private Function Process_Folder2(outFolder As Outlook.MAPIFolder, destCell As Range) As Integer
    
    Dim n As Integer
    Dim outSubfolder As MAPIFolder
    Dim outItem As Object
    Dim folderSize As Long
    
    'Calculate size in bytes of all items in this folder
    
    folderSize = 0
    For Each outItem In outFolder.Items
        folderSize = folderSize + outItem.Size
    Next

    With destCell.Parent.Cells(destCell.Row, 1)     'always start in column A
        .Offset(0, 0).Value = outFolder.folderPath
        .Offset(0, 1).Value = Round(folderSize / 1024) * 1024 \ 1024 & " KB"    'round to nearest 1024 and convert to KB
        .Offset(0, 2).Value = outFolder.Items.Count
        .Offset(0, 3).Value = outFolder.UnReadItemCount
    End With
    
    destCell.Offset(0, 4).Value = outFolder.Name
    
    n = 0
    For Each outSubfolder In outFolder.Folders
        n = n + 1
        n = n + Process_Folder2(outSubfolder, destCell.Offset(n, 1))
    Next
    
    Process_Folder2 = n
    
End Function

 

[lapta301]

John
Thank you so much for taking the time.

Sorry to sound dopey but how do I set a reference to the Outlook library?

 

[John_w]

In the Excel VBA editor, click the menu Tools then References. Scroll down the list and find Microsoft Outlook nn.0 Object Library (where nn is the Outlook version, for example 11) and tick the box next to it. Then click OK to close the dialogue.

 

[lapta301]

John

My apologies for delay in getting back to you as I was away for a few days.

As suggested I have found the reference entry Outlook 14.0 Object Library and checked it but after running the macro I get:Run time error Object cannot be found.

 

[lapta301]

John

I should have added.

The error refers to this line:

Set outStartFolder = outNameSpace.Folders("Personal Folders")

As the Outlook file is on a server somewhere is it the full path that is needed. If so that might be a problem.

Many thanks

Steve

 

[John_w]

Try replacing that line with:

    Set outStartFolder = outNameSpace.GetDefaultFolder(olFolderInbox).Parent

I can't really help further because I can't test it on a server (Exchange server), so you might have to search for answers if the above change doesn't work.

 

[lapta301]

John

My apologies for long delasy in getting back to you again.

It seems to be working fine. So many thanks for your help.

Much appreciated.