VBA Loop not combing outlook correctly

[hellfire45]

I have a VBA script in Excel that is combing my inbox in outlook for emails with specific subject line text and then it does stuff.
The issue is that for whatever reason it's skipping the first 5 days of emails and jumps straight to 7/6/2022 and then goes one email at a time with the receipt date in descending order as it is supposed to. Today is 7/11/2022 and I have emails from each day 7/6 through 7/11. So this thing should be going from 7/11's most current email. Below is the code. The heck is goin on here?

Sub get_coupadata()

Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim fldr As Outlook.MAPIFolder
Dim myItems As Outlook.Items

Dim olNameSpace As Object
Dim olFolder As Object
Dim sh_zip As Object

Dim strName_coupa_zip As String
Dim sFound As String

Dim i As Long
Dim last_row As Long
Dim countdata As Long
Dim countformulas As Long

Dim j As Integer
Dim save_count As Integer
Dim first_Col As Integer
Dim last_col As Integer
Dim startrow As Integer
Dim lastcol As Integer
Dim firstformcol As Integer
Dim target_first_col As Integer

Dim received_date As Date
Dim date_cutoff As Date

Dim answer As Variant
Dim coupa_zip_Folder As Variant
Dim localZipFile As Variant

Dim wscs As Worksheet
'END OF DECLARING VARIABLES VARIABLES--------------------

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set olNameSpace = olApp.GetNamespace("MAPI")
Set olFolder = olNameSpace.Folders("name@airmethods.com").Folders("Inbox")
Set myItems = olFolder.Items
Set fldr = olFolder.Folders("Coupa Data Files")

If (olFolder = "") Then
    Set olFolder = olNameSpace.Folders("name@airmethods.com").Folders("Inbox")
End If

coupa_zip_Folder = "folder link hidden"
strName_coupa_zip = "PastDue_Invoice_Data_" & Format(Date, "mm-dd-yyyy")
save_count = 0

myItems.Sort "[ReceivedTime]"

'coupa EMAIL RETRIEVAL
For i = 1 To olFolder.Items.Count
            'If i < 1 Then Exit For
            If olFolder.Items(i).Class <> olMail Then
            Else
                        Set olMailItem = olFolder.Items(i)
                        received_date = olMailItem.ReceivedTime
                        date_cutoff = Date - 22
                        If received_date >= date_cutoff Then
                                    If InStr(1, olMailItem.Subject, "Report: JJ AGED") > 0 Then
                                                With olMailItem
                                                            For j = 1 To .Attachments.Count
                                                                        .Attachments(j).SaveAsFile coupa_zip_Folder & "\" & strName_coupa_zip & ".zip"
                                                                        
                                                                        .Move fldr
                                                                        
                                                                        save_count = save_count + 1
                                                                        Exit For
                                                            Next j
                                                End With
                                    End If
                        End If
            End If
            If save_count > 0 Then Exit For
Next i

 

[John_w]

In this part of the code:

myItems.Sort "[ReceivedTime]" 
'coupa EMAIL RETRIEVAL 

For i = 1 To olFolder.Items.Count

You have the myItems collection of emails in ascending order of Received Time (ascending order is the default, unless the Sort method's 2nd argument, Descending:=True, is specified), but loop through the 'raw' olFolder.Items collection (I don't know in what order the Items are returned).

Try changing the code to:

myItems.Sort "[ReceivedTime]", Descending:=True
'coupa EMAIL RETRIEVAL 

For i = 1 To myItems.Count

Also, if you want to match the partial subject string regardless of upper/lower case then change this line:

If InStr(1, olMailItem.Subject, "Report: JJ AGED") > 0 Then

to:

If InStr(1, olMailItem.Subject, "Report: JJ AGED", vbTextCompare) > 0 Then

 

[hellfire45]

In this part of the code:

myItems.Sort "[ReceivedTime]"
'coupa EMAIL RETRIEVAL

For i = 1 To olFolder.Items.Count

You have the myItems collection of emails in ascending order of Received Time (ascending order is the default, unless the Sort method's 2nd argument, Descending:=True, is specified), but loop through the 'raw' olFolder.Items collection (I don't know in what order the Items are returned).

Try changing the code to:

myItems.Sort "[ReceivedTime]", Descending:=True
'coupa EMAIL RETRIEVAL

For i = 1 To myItems.Count

Also, if you want to match the partial subject string regardless of upper/lower case then change this line:

If InStr(1, olMailItem.Subject, "Report: JJ AGED") > 0 Then

to:

If InStr(1, olMailItem.Subject, "Report: JJ AGED", vbTextCompare) > 0 Then

Hi John,

Thank you for trying. It seems that the suggestion of sorting descending has not accomplished the job. It's on pulling emails starting on 7/6/2022. Very baffling. It doesn't even start at the beginning or end of the day, just pulls from the middle at 8:35am. Any other ideas?

 

[John_w]

I think more changes are needed inside the For loop to use myItems:

For i = 1 To myItems.Count
            If myItems(i).Class = olMail Then
                Set olMailItem = myItems(i)

 

[hellfire45]

I think more changes are needed inside the For loop to use myItems:

For i = 1 To myItems.Count
            If myItems(i).Class = olMail Then
                Set olMailItem = myItems(i)

You fixed it and I love you. Thank you.