Excel Macro for Counting Items in Outlook Shared inbox by Date

Question

I'm trying to write a Macro in Excel for one of our teams to count the number of items in specific shared Outlook mail folders by date and present the counts in Excel.

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

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

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

Any direction on where to look for a reference or help with the code would be greatly appreciated.

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

PKatona PKatona · Accepted Answer · 2017-10-25T23:40:25

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

Excel Macro for Counting Items in Outlook Shared inbox by Date

2 Answers