From excel vba, count only original emails-CodePudding

I am just beginning to get my feet under me in Excel VBA and now I need to (from Excel) count emails in Outlook over a specific timeframe. I hardcoded that timeframe in for now. That part seems to work - at least the count is correct.

The issue is that it's counting every single email, rather than just the originals. I need the count for just new emails. I have looked at .GetConversation and then read that conversationIDs change with each email, so the original has 44 characters. I thought that would be a good way to filter, but I do not understand what is in that property because it's not working.

Now I dont know if I'm barking up the wrong tree or if I'm just around the corner from getting this. It works fine until it tries to filter by the conversation ID.

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
    
    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
    
    'Everything above this comment works

    Set inboxItems = inboxItems.Restrict("[ConversationID] < 45")
    Set doneItems = doneItems.Restrict("[ConversationID] < 45")
    
    Debug.Print "Total Inbox Count: " & inboxItems.Count
    Debug.Print "Total Done Count: " & doneItems.Count

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

CodePudding user response：

ConversationID

From what I understand ConversationID is a property that will have the same value for all the mailItems that belong to the same conversation (more here).

This means that if you reply to an email and the person replies to your reply, the second email you receive from them should have the same ConversationID.

I'm assuming that when you say that you want to count "original emails", you mean that you want to avoid counting the second email as it's part of the conversation initiated by the first (original) email.

So basically, you want to count how many unique values of ConversationID you have among your mailItems.

I haven't used .Restrict, so I'm not sure if you can use it for this purpose, but there are ways to get the total count of unique values for ConversationID by looping on the MailItems and counting the unique values.

Option 1: Using a Collection

One way to do it would be to use a collection. Since a collection can't contain two elements with the same key, we can use it to count the number of unique values.

For example:

    Dim UniqueConversations As New Collection
    
    Dim inboxItem As MailItem
    For Each inboxItem In inboxItems
        On Error Resume Next
            'This line will return an error when the key already matches an item in the collection
            'and the item won't be added to the collection.
            UniqueConversations.Add 1, inboxItem.ConversationID
        On Error GoTo 0
    Next inboxItem

    Debug.Print "Total Inbox Count: " & UniqueConversations.Count

Option 2: Using a Dictionary

The dictionary solution is a little more elegant as we don't need to use On error statements.

The reason why we don't get an error when we use a dictionary is that we'll just overwrite the stored value when the key already exists in the dictionary.

For example:

    'Make sure to include Microsoft Scripting Runtime Library or use the drop-in replacement VBA-tools/VBA-Dictionary on Mac
    Dim dict As Dictionary
    Set dict = New Dictionary
    
    Dim inboxItem As MailItem
    For Each inboxItem In inboxItems
        dict.Item(inboxItem.ConversationID) = 1
    Next inboxItem
    
    Debug.Print "Total Inbox Count: " & dict.Count

If you have a lot of emails, the dictionary approach is usually faster, but I haven't noticed a big difference for the small tests I've done.

CodePudding user response：

You cannot create a restriction on property length like [ConversationID] < 45 (you can in Extended MAPI, but it is only available from C or Delphi). Try to create a restriction on PR_SUBJECT_PREFIX MAPI property being an empty string. On replies it is "RE" and "FW" on forwards.

@SQL="http://schemas.microsoft.com/mapi/proptag/0x003D001F" = ''

in your code:

Set inboxItems = inboxItems.Restrict("@SQL=""http://schemas.microsoft.com/mapi/proptag/0x003D001F"" = ''")
Set doneItems = doneItems.Restrict("@SQL=""http://schemas.microsoft.com/mapi/proptag/0x003D001F"" = ''")