Count emails in Shared Mailbox by Subfolder & Month Sent

Question

I want to count the emails in a shared mailbox/subfolders for each month.

This code shows a count for each month for one folder only, and the order of the months is out.

How can I show by month (in the correct order), and subfolder?

Example of the output I would like:

Subfolder
2019-12 - number of emails
2020-1 - number of emails

Subfolder 2
2019-11 - number of emails
2019-12 - number of emails
2020-1 - number of emails

etc.

Sub HowManyEmails()

    Dim objOutlook As Object, objnSpace As Object, objFolder As MAPIFolder
    Dim EmailCount As Integer
    Set objOutlook = CreateObject("Outlook.Application")
    Set objnSpace = objOutlook.GetNamespace("MAPI")

    On Error Resume Next
    Set objFolder = Application.Session.PickFolder
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "No such folder."
        Exit Sub
    End If

    EmailCount = objFolder.Items.Count

    MsgBox "Number of emails in the folder: " & EmailCount, , "email count"

    Dim dateStr As String
    Dim myItems As Outlook.Items
    Dim dict As Object
    Dim msg As String
    Set dict = CreateObject("Scripting.Dictionary")
    Set myItems = objFolder.Items
    myItems.SetColumns ("SentOn")
    ' Determine date of each message:
    For Each myItem In myItems
        dateStr = GetDate(myItem.SentOn)
        If Not dict.Exists(dateStr) Then
            dict(dateStr) = 0
        End If
        dict(dateStr) = CLng(dict(dateStr)) + 1
    Next myItem

    ' Output counts per day:
    msg = ""
    For Each o In dict.Keys
        msg = msg & o & ": " & dict(o) & " items" & vbCrLf
    Next
    MsgBox msg

    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
End Sub

Function GetDate(dt As Date) As String
    GetDate = Year(dt) & "-" & Month(dt) & "-"
End Function

Possible duplicate of [Can I iterate through all Outlook emails in a folder including sub-folders?](https://stackoverflow.com/questions/2272361/can-i-iterate-through-all-outlook-emails-in-a-folder-including-sub-folders) — niton, Aug 28 '20 at 02:23

score 1 · Answer 1 · answered Mar 19 '21 at 17:26

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Private Sub HowManyEmails_With_Subfolders()
    
    Dim objFolder As Folder
    
    Set objFolder = Session.PickFolder

    If objFolder Is Nothing Then
        Exit Sub
    End If
    
    processFolderSorted objFolder

End Sub

Function GetDate(dt As Date) As String
    GetDate = Year(dt) & "-" & Month(dt)
End Function

Private Sub processFolderSorted(ByVal objFolder As Folder)

' https://stackoverflow.com/questions/2272361/can-i-iterate-through-all-outlook-emails-in-a-folder-including-sub-folders

    Dim EmailCount As Long
    
    Dim dateStr As String
    
    Dim myItem As Object
    Dim myItems As items
    
    Dim dict As Object
    Dim o
    
    Dim msgCount As String
    Dim msg As String
    
    Dim oFolder As Folder
    
    Debug.Print "objFolder: " & objFolder
    
    EmailCount = objFolder.items.count
    'Debug.Print "EmailCount: " & EmailCount
    
    If EmailCount > 0 Then
    
        msgCount = "Number of emails in " & objFolder & ": " & EmailCount & vbCr
        'Debug.Print msgCount
        
        Set dict = CreateObject("Scripting.Dictionary")
        
        Set myItems = objFolder.items
        
        myItems.Sort "[SentOn]", False
        
        myItems.SetColumns ("SentOn")
            
        ' Determine date of each message
        For Each myItem In myItems
                
            ' Some item types / item classes
            '  will not have an expected mailitem property
            If myItem.Class = olMail Then
            
                dateStr = GetDate(myItem.SentOn)
            
                If Not dict.Exists(dateStr) Then
                    dict(dateStr) = 0
                End If
                
                dict(dateStr) = CLng(dict(dateStr)) + 1
                
            Else
            
                Debug.Print "item bypassed"
            
            End If
            
        Next
    
        ' Output counts per day:
        For Each o In dict.Keys
            msg = msg & o & ": " & dict(o) & " items" & vbCrLf
        Next
        
        Debug.Print msgCount & msg
        'MsgBox msgCount & msg
    
        Set dict = Nothing
        
    End If
    
    If (objFolder.folders.count > 0) Then
        For Each oFolder In objFolder.folders
            processFolderSorted oFolder
        Next
    End If
        
End Sub

Count emails in Shared Mailbox by Subfolder & Month Sent

1 Answers1