Outlook Message Counting Macro Is Slow
With the Help of a Few Posts Here on Stack Overflow, I Wrote This Macro for Outlook That Will Count the Number of Messages That Arrived This Week in a...
With the help of a few posts here on Stack Overflow, I wrote this macro for Outlook that will count the number of messages that arrived this week in a particular folder, breaking out unread messages vs the total messages for the week.
Problem is it runs really slow and there are currently less than 100 messages in the folder. Really slow = outlook pauses for a few seconds before the message box pops up with the results.
I assume there is a much more efficient method to do this. Can you point me in the right direction?
My current code:
Dim Vfolder As Outlook.Folder
Set Vfolder = Application.Session.Folders("abc").Folders("123")
Dim x As Long ' Unread messages in folder from this week
Dim y As Long ' Total messages in folder from this week
Dim i As Long ' loop
Dim objMail As Outlook.MailItem
Dim DateReceived As Date
Dim FullDateReceived As Date
Dim DateTest As Date
DateTest = Date - Weekday(Date, vbMonday) + 1
For i = 1 To Vfolder.Items.Count
If Vfolder.Items.Item(i).Class = olMail Then
Set objMail = Vfolder.Items.Item(i)
FullDateReceived = objMail.ReceivedTime
DateReceived = Year(FullDateReceived) & "-" & Month(FullDateReceived) & "-" & Day(FullDateReceived)
If DateReceived >= DateTest Then
If objMail.UnRead Then
x = x + 1
y = y + 1
Else
y = y + 1
End If
End If
End If
Next
1 Answer
As a rule of thumb, never ever loop through all items in a folder - after all, you'd never run a SQL query without a WHERE clause, would you?
Worse than that, you are using multiple dot notation (Vfolder.Items.Item(i)), which causes OOM to return a brand new Items object on each iteration of the loop.
Use or Items.Restrict. In your particular case, it will be as simple as
dt = Now-7
strDate = FormatDateTime(dt, 2)
set vfolder = Application.ActiveExplorer.CurrentFolder
y = Vfolder.Items.Restrict("[ReceivedTime] > '" & strDate & "'").Count
x = Vfolder.Items.Restrict("([Unread] = true) and ([ReceivedTime] > '" & strDate & "')").Count
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
