如何通过Excel统计Outlook指定时间段内的原始邮件数量

问题原因 #

1. 原代码中[ConversationID] < 45的过滤逻辑完全错误：ConversationID是字符串类型，直接和数字45比较会按字典序匹配，而非判断字符串长度，根本无法筛选出长度为44位的原始邮件。
2. Outlook的Restrict方法不支持在过滤器中直接判断字符串长度，这类复杂条件无法通过Restrict语法实现。

解决方案 #

遍历已按时间过滤后的邮件集合，逐个检查ConversationID的长度是否为44位，收集符合条件的原始邮件。修改后的完整代码如下：

Sub cntEmail()
'I WILL NEVER COUNT EMAILS AGAIN, dangit
    Dim ns As Namespace: Set ns = GetNamespace(Type:="MAPI")
    Dim fldr As Folder, fldrDone As Outlook.Folder
    Dim inboxItems As Items, doneItems As Items, sFilter As String
    Dim filteredInbox As New Collection, filteredDone As New Collection
    Dim item As Object
    
    Set fldr = ns.Folders("Call Center").Folders("Inbox")
    Set fldrDone = ns.Folders("Call Center").Folders("DONE")
    Set inboxItems = fldr.Items
    Set doneItems = fldrDone.Items
    sFilter = "[LastModificationTime] > '" & Format("1/13/2023 17:00", "ddddd h:mm AMPM") & "' AND [LastModificationTime] < '" & Format("1/20/2023 16:59", "ddddd h:mm AMPM") & "'"
    Set inboxItems = inboxItems.Restrict(sFilter)
    Set doneItems = doneItems.Restrict(sFilter)
    
    Debug.Print "Total Inbox Count: " & inboxItems.Count
    Debug.Print "Total Done Count: " & doneItems.Count
    
    ' 筛选原始邮件（ConversationID长度为44位）
    ' 处理收件箱
    For Each item In inboxItems
        If TypeName(item) = "MailItem" Then
            If Len(item.ConversationID) = 44 Then
                filteredInbox.Add item
            End If
        End If
    Next item
    
    ' 处理DONE文件夹
    For Each item In doneItems
        If TypeName(item) = "MailItem" Then
            If Len(item.ConversationID) = 44 Then
                filteredDone.Add item
            End If
        End If
    Next item
    
    Debug.Print "Original Inbox Count: " & filteredInbox.Count
    Debug.Print "Original Done Count: " & filteredDone.Count

    Set fldr = Nothing
    Set fldrDone = Nothing
    Set ns = Nothing
    Set filteredInbox = Nothing
    Set filteredDone = Nothing
End Sub

代码说明 #

• 添加TypeName(item) = "MailItem"判断，避免文件夹中非邮件项（如会议邀请、任务）引发错误。
• 通过Len(item.ConversationID) = 44直接匹配原始邮件的标识特征，确保统计准确性。
• 使用Collection存储筛选后的邮件，方便后续扩展统计或其他操作。

内容的提问来源于stack exchange，提问作者Iakona