VBA Outlook object not returning anything

[gohawks222]

This is the first time I have worked with Outlook within VBA, so go easy if I have any glaring mistakes. I get a report emailed every day in my standard inbox at 06:01, and I'm attempting to locate it based on that time criteria. There are no runtime errors, but when I step through and get to the end, oOltargetEmail contains no value. I've done a lot of research about the .Restrict() method formatting, and I think it's correct, but my guess is that's where the error is. Any ideas would be appreciated.

Const olFolderInbox As Integer = 6
Const AttachmentPath As String = "C:\Users\TRBYE\Desktop\cheeseReport.xlsx"


Private Sub CommandButton1_Click()


Dim oOlAp As Object, oOlns As Object, oOlInb As Object, oOlItm As Object, oOltargetEmail As Object, oOlAtch As Object
Dim beginningDate As String, endingDate As String, todaysDateTime As String, todaysDate As String, startTime As String, endTime As String
Dim x As Integer


Set oOlAp = GetObject(, "Outlook.application")
Set oOlns = oOlAp.GetNamespace("MAPI")
Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)


startTime = " 06:00 AM"
endTime = " 06:02 AM"
todaysDateTime = Format(Now(), "ddddd hh:mm AMPM")
x = Len(todaysDateTime)
todaysDate = Left(todaysDateTime, (Len(todaysDateTime) - 9))


'set start and end time based on strings from above'
beginningDate = todaysDate & startTime
endingDate = todaysDate & endTime


'determine corrrect email'
For Each oOlItm In oOlInb.Items.Restrict("[Start] >= '" & beginningDate & "' and [End] <= '" & endingDate & "'")


    If Left(oOlItm.Subject, 4) = "DGLD"  Then
        oOltargetEmail = oOlItm
        Exit For
    End If
    
Next
'download attachment to desktop'
For Each oOlAtch In oOltargetEmail.Attachments
    oOlAtch.SaveAsFile AttachmentPath
Next
'open attachment'
Workbooks.Open (AttachmentPath)
End Sub

 

[Norie]

Since you are working with objects you should be using Set.

Set oOltargetEmail = oOlItm

Mind you the problem could be that the condition in this If statement is never satisfied.

    If Left(oOlItm.Subject, 4) = "DGLD"  Then

 

[gohawks222]

I removed the IF block for testing purposes since it's realistically the only email that will be there between 06:00 and 06:02 every morning. Now without worrying about the how that If statement evaluates, I believe this loop is where the problem is, because it doesn't even iterate once which means there is no match, and I'm looking at my inbox right now and the timestamp is 06:01, so it should evaluate all else considered.

For Each oOlItm In oOlInb.Items.Restrict("[Start] >= '" & beginningDate & "' and [End] <= '" & endingDate & "'")
    Set oOltargetEmail = oOlItm
Next

The beginningDate is currently equal to "7/25/16 06:00 AM" and the endingDate evaluates to "7/25/16 06:02 AM". Does anyone know if I assembled the string correctly in the Restrict() method?

 

[Norie]

This might be a mismatch problem - [Start] and [End] could be stored as 'real' dates which you are trying to compare with the 'text' dates in beginningDate and endingDate.

 

[gohawks222]

I reformatted the dates based on this documentation (under Date), but still no luck. Are there any Outlook settings/defaults that could affect this?

https://msdn.microsoft.com/en-us/library/office/ff869597.aspx

 

[V_Malkoti]

Have you tried using [LastModificationTime] or [ReceivedTime] instead of [Start]/[End]?