VBA Loop not combing outlook correctly

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
460
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?

VBA Code:
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,536
In this part of the code:
VBA 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:
VBA Code:
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:
VBA Code:
If InStr(1, olMailItem.Subject, "Report: JJ AGED") > 0 Then
to:
VBA Code:
If InStr(1, olMailItem.Subject, "Report: JJ AGED", vbTextCompare) > 0 Then
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
460
In this part of the code:
VBA 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:
VBA Code:
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:
VBA Code:
If InStr(1, olMailItem.Subject, "Report: JJ AGED") > 0 Then
to:
VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,536
I think more changes are needed inside the For loop to use myItems:
VBA Code:
For i = 1 To myItems.Count
            If myItems(i).Class = olMail Then
                Set olMailItem = myItems(i)
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
460
I think more changes are needed inside the For loop to use myItems:
VBA Code:
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.
 

Forum statistics

Threads
1,175,530
Messages
5,897,952
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top