Counting Items in Outlook Shared inbox by Date using Excel VBA

Question

I'm trying to count the number of items in shared Outlook mail folders by date and present the counts in Excel.

I've been able to get a count to work on my personal inbox (different code for retrieving the mailbox).
I've been able access the shared mailbox folders to get a total count of items available (retrieving mailboxes using the code below).

I get

Run-time error '438': Object doesn't support this property or method.

I tracked it down to the array section of the code that uses iCount.

Sub HowManyDatedEmails()

' Set Variables
Dim EmailCount As Integer, DateCount As Integer, iCount As Integer
Dim myDate As Date
Dim arrEmailDates()

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItem As Object
Dim olMailItem As Outlook.MailItem

'Call Folder
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFldr = olNS.Folders("delivery quality team")
Set olFldr = olFldr.Folders("Inbox")

' Put ReceivedTimes in array
EmailCount = olFldr.Items.Count
For iCount = 1 To EmailCount
    With olFldr.Items(iCount)
        ReDim Preserve arrEmailDates(iCount - 1)
        arrEmailDates(iCount - 1) = DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime))
    End With
Next iCount

' Clear Outlook objects
Set olFldr = Nothing
Set olNS = Nothing
Set olApp = Nothing

' Count the emails dates equal to active cell
Sheets("Count_Data").Range("A2").Select
Do Until IsEmpty(ActiveCell)
    DateCount = 0
    myDate = ActiveCell.Value
    
    For i = 0 To UBound(arrEmailDates) - 1
        If arrEmailDates(i) = myDate Then DateCount = DateCount + 1
    Next i
     
    Selection.Offset(0, 1).Activate
    ActiveCell.Value = DateCount
    Selection.Offset(1, -1).Activate
Loop
End Sub

score 0 · Accepted Answer · answered Oct 25 '17 at 23:40

Do the following:

Dim inItem As Outlook.MailItem
iCount = 0
For Each inItem In olFldr.Items
    iCount = iCount + 1
    With inItem
        ReDim Preserve arrEmailDates(iCount - 1)
        arrEmailDates(iCount - 1) = DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime))
    End With
Next inItem

score 0 · Answer 2 · answered Oct 26 '17 at 01:02

In general, I'd recommend using the Find/FindNext or Restrict methods instead of iterating over all items in the folder. Read more about these methods in the following articles:

Now my problem is I get Run-time error '438': Object doesn't support this property or method.

The fact is that Outlook folder may contain different item types - mails, notes, calendar items and etc. So, most probably you are dealing with an item type which doesn't have properties specified in the code. In the loop I'd suggest the item type first and then deal with its properties. See When is a MailItem not a MailItem? fore more information.

This is all great information! I'll use the new methods to rewrite this later to be more efficient. Thank you. — Cgitlin, Oct 26 '17 at 16:38

Counting Items in Outlook Shared inbox by Date using Excel VBA

2 Answers2