VBA to read Outlook email messages in Win8

[Kevin320]

I wrote VBA code to pull email messages from Outlook in order to create an alert if a message with a specific subject line is not received. I have the system coded to send the alert if the message isn't found before the system encounters a message with a SentOn date/time more than two hours old.

The code has worked great for over a year, but now my company is migrating to Win8 and I'm having issues with the code. The machine that normally runs the process uses XP and Access 2007. The code also works on another machine running Windows 7 and Access 2010.

The issue I'm having in Win8, with Access 2010, is that the code isn't pulling the most recent message first. Instead, it is selecting a message from about two weeks ago, and then cycling through the other messages from that point. I know there are ways I can code around this, but I don't want to if I don't have to.

Does anyone know why this might be happening? Is there a setting that I'm missing? I've included the basic code to pull the email messages below, and appreciate any assistance.

[SIZE=3][FONT=Calibri]   DoCmd.SetWarnings False[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Dim ol As Outlook.Application[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim ns As Namespace[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim Inbox As MAPIFolder[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim Item As Object[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim strSubject, strSentDate As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Set ol = CreateObject("Outlook.Application")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set ns = ol.GetNamespace("MAPI")[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   'Change required due to difference in XP and Win8 Outlook mailbox names[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   'Set Inbox = ns.Folders("Mailbox - Last, First").Folders("Inbox")      'XP Outlook mailbox name[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set Inbox = ns.Folders("FirstLast@domain.com").Folders("Inbox")      'Win8 Outlook mailbox name[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   For Each Item In Inbox.Items[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     strSubject = Item.Subject[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     strSentDate = Item.SentOn[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   ‘Perform tests on strSubject and strSentDate for each Item to determine age of message and if subject line matches the desired subject line[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Next Item[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   DoCmd.SetWarnings True[/FONT][/SIZE]

 

[vbaInet]

You would have received more useful responses if you had posted this question in an appropriate VBA Outlook forum.

You need to sort the Outlook items.

Dim olItems As Outlook.Items
.
.
.
etc
.
.
.

Set [COLOR="blue"]olItems [/COLOR]= Inbox.Items
[COLOR="blue"]olItems[/COLOR].Sort "[[COLOR="blue"]Received[/COLOR]]", True

For Each Item In [COLOR="Blue"]olItems[/COLOR]
...etc

I wonder if it's quicker to link to the file and use a query to sort and filter it down.

 

[Kevin320]

Thank you vbaInet. That works. Sorry about posting in the wrong forum. I didn't know there was a VBA Outlook forum. My VBA code is in Access, so I thought this would be an appropriate forum.

 

[vbaInet]

You're welcome!

I meant on any Outlook forum. It's VBA for Outlook and it's just being referenced from Access. But feel free to post here if you feel more comfortable here.