email - Using ReceivedTime in Outlook VBA to count yesterday's mail

I want to flash up a message box with the amount of mail received yesterday.

The code I have at the moment is:

Public Sub YesterdayCount()

Set ns = Application.GetNamespace("MAPI")
Set outApp = CreateObject("Outlook.Application")
Set outNS = outApp.GetNamespace("MAPI")

Dim Items As Outlook.Items
Dim MailItem As Object

Dim yestcount As Integer
yestcount = 0

Set Folder = outNS.Folders("Correspondence Debt Advisor Queries").Folders("Inbox")
Set Items = Folder.Items

For Each Item In Items
    If MailItem.ReceivedTime = (Date - 1) Then
        yestcount = yestcount + 1
    End If
Next

MsgBox yestcount

End Sub

The problem is with the line:

If MailItem.ReceivedTime = (Date - 1) Then

The error says that an object variable is not set, but I can't fathom this after researching.

Answer

You almost got it. You basically never set the MailItem nor qualified it to the Item, and since ReceivedTime is Date / Time format, it will never equal a straight Date.

See the code below. I added some features to sort by ReceivedTime, then Exit the loop once it passes yesterday's date. I also cleaned up some of the variable naming so it will not be confused with inherent Outlook Object naming conventions.

Public Sub YesterdayCount()

Dim outNS As Outlook.NameSpace
Set outNS = Application.GetNamespace("MAPI")

Dim olFolder As Outlook.Folder
Set olFolder = outNS.Folders("Correspondence Debt Advisor Queries").Folders("Inbox")

Dim olItems As Outlook.Items
Set olItems = olFolder.Items

olItems.Sort "[ReceivedTime]", True

Dim yestcount As Integer
yestcount = 0

Dim item As Object

For Each item In olItems

    'commented code works for MailItems
    'Dim olMail As Outlook.MailItem
    'Set olMail = item

    Dim dRT As Date
    'dRT = olMail.ReceivedTime
    dRT = item.ReceivedTime

    If dRT < Date And dRT > Date - 2 Then
        If dRT < Date - 1 Then Exit For
        yestcount = yestcount + 1
    End If


Next

MsgBox yestcount

End Sub