How to test Mailitem.ReceivedTime against cell value?

Question

This code is meant to download an Outlook email attachment based on a check if the mailitem was received past the date specified by the user in cell C6.

Stepping through the code reveals the code reads the following line of code as if it is checking the mail item against itself (i.e. mailitem received on 01/01/01 code says if mailitem=01/01/01 then) the mailitem always meets this check.

If MailItem.ReceivedTime >ThisWorkbook.Worksheets("Email_Info").Range("C6").Value Then

Option Explicit

Sub Download_Outlook_Attachemtns()

Dim olAPP As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olItem As Object
Dim MailItem As Outlook.MailItem
Dim olAtt As Outlook.Attachment

Set olAPP = New Outlook.Application
Set olNS = olAPP.getnamespace("MAPI")

'single folder link to hidden sheet folders([admin].[Mailbox].text)
Set olFolder = olNS.Folders("John@work.com")
Set olFolder = olFolder.Folders("Inbox")
Set olFolder = olFolder.Folders("Work Requests")

For Each olItem In olFolder.Items
    If olItem.Class = olMail Then
        Set MailItem = olItem

        For Each olAtt In MailItem.Attachments
            If MailItem.ReceivedTime > ThisWorkbook.Worksheets("Email_Info").Range("C6").Value Then
                olAtt.SaveAsFile ("C:\Users\John Smith\Desktop\WOR Email Download") & olAtt.Filename
            End If
        Next olAtt

    End If
Next olItem

End Sub

Go to the line with the `If` condition and press F9 to toggle a breakpoint, then run the code. When the breakpoint hits, press Ctrl+G to bring up the _immediate_ toolwindow then type `?mailitem.receivedtime, ThisWorkbook.Worksheets("Email_Info").Range("C6").Value` to output the two values being compared. Does it print what you expect? — Mathieu Guindon, Sep 27 '22 at 13:43
Thank you for this advice. So, For the Date is C6 VBA is reading the correct date. For some reason the Mail Item was starting on a random Email. I was under the impression this script would start at the latest email and go in order. However when I established a break at the "Set MailItem=olItem" the MailItem object seems to be looping in a random order. — NotACoder, Sep 27 '22 at 16:29
You need to compare two date objects, but it seems in Excel you got a string instead. — Eugene Astafiev, Sep 27 '22 at 21:22

score 0 · Answer 1 · answered Sep 27 '22 at 21:21

The MailItem.ReceivedTime property returns a Date indicating the date and time at which the item was received. But the following sequence of property and method calls is evaluated to a string most probably:

ThisWorkbook.Worksheets("Email_Info").Range("C6").Value

So, you are trying to compare the date instance with a string. To avoid such cases you need to instantiate a date to compare to. Use the CDate function available in VBA to convert the text into a date object. See Convert Text to Date? for more information.

score 0 · Answer 2 · answered Mar 27 '23 at 13:50

Comment: "... when I established a break at the "Set MailItem=olItem" the MailItem object seems to be looping in a random order."

Sort the items so they are in a reliable order.

Option Explicit


Sub Download_Outlook_Attachemtns()

Dim olApp As Outlook.Application

Dim olFolder As Outlook.Folder
Dim olFolderItems As Items

Dim olMailItem As Outlook.MailItem
Dim olAtt As Outlook.Attachment

Set olApp = New Outlook.Application

Set olFolder = Session.Folders("John@work.com")
Set olFolder = olFolder.Folders("Inbox")
Set olFolder = olFolder.Folders("Work Requests")

Set olFolderItems = olFolder.Items
olFolderItems.Sort "[ReceivedTime]", True

Debug.Print "C6: " & ThisWorkbook.Worksheets("Email_Info").Range("C6").Value

Dim i As Long
For i = 1 To olFolderItems.Count

    'Debug.Print "olFolderItems(i).ReceivedTime: " & olFolderItems(i).ReceivedTime
    
    If olFolderItems(i).Class = olMail Then
    
        Set olMailItem = olFolderItems(i)
        
        Debug.Print "olMailItem.ReceivedTime: " & olMailItem.ReceivedTime
        
        For Each olAtt In olMailItem.Attachments
            If olMailItem.ReceivedTime > ThisWorkbook.Worksheets("Email_Info").Range("C6").Value Then
                Debug.Print " Found: " & olMailItem.Subject
                olAtt.SaveAsFile ("C:\Users\John Smith\Desktop\WOR Email Download") & olAtt.Filename
            Else
                Exit For
            End If
        Next olAtt

    End If
Next

End Sub

How to test Mailitem.ReceivedTime against cell value?

2 Answers2