Outlook VBA macro to find (Restrict) specific email using LastModificationTime

Question

Not coding expert here... but: I have a macro triggered by an Outlook rule that process specific incoming email content and export relevant data to an Excel spreadsheet. After the email is processed, I would like to change its cattegory and move to another folder. At the piece of code below, the object "item" is the email that is given to the macro. So.. my idea is to find the original Outlook.MailItem using the LastModificationTime property as "item.LastModificationTime" and perform the operations. It seems the code stops at the "Set myItem = myItems.Restrict(aux2).item(1)" with either "aux1" or "aux2" filters. I dont have any error popping out, but any off the commands after that line are not executed.

Sub ExportToExcel(item As Outlook.MailItem)
Dim aux As String
Dim aux2 As String
Dim myInbox As Outlook.Folder
Dim myDestFolder As Outlook.Folder
Dim myItems As Outlook.Items
Dim myItem As Outlook.MailItem

'(irrelevant code that does email processing and excel spreadsheet writing...)

  Set myInbox = Session.GetDefaultFolder(olFolderInbox)
    Set myItems = myInbox.Folders("BACKUP").Items
    MsgBox (myItems.item(1).Subject) 'Runs OK
    aux = "[LastModificationTime] = """ & Format(item.LastModificationTime, "ddddd hh:nn") & """" 'just another try
    aux2 = "[LastModificationTime] = """ & item.LastModificationTime & """"
    MsgBox (aux) 'Runs OK
    MsgBox (aux2) 'Runs OK
    Set myItem = myItems.Restrict(aux2).item(1) 'Seems the code stops here with no error
    MsgBox ("Here") 'Does NOT run from here on
    MsgBox (myItem.Subject)
    myItem.Categories = "Categoria Laranja"
    Set myDestFolder = myInbox.Folders("BACKUP_Processed")
    myItem.Move (myDestFolder)

End Sub

EDIT: I realized that I don't need "myItem"... and I could implement my tasks directly at the incoming "item"... So that worked!

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2020-10-05T16:38:10

Firstly, remove all "on error resume next" statements to that you can see the actual error.

Secondly, you are assuming Restrict() returns at least one item.

Thirdly, never use "= " with DateTime value - the condition will never will satisfied due to round-off errors. Always use a range - ([LastModificationTime] > 'value1') AND ([LastModificationTime] < 'value2')

Outlook VBA macro to find (Restrict) specific email using LastModificationTime

1 Answers