CODEWITHSUNDEEP
excelvbaoutlook
morelater
morelater

Reputation: 1

How to return most recently received email?

I'm trying to search an Inbox for the latest email that includes an attachment with name 'TAX' and save that attachment to a folder.

It's a large inbox, so I restricted the search by subject line, and then sort the selection by ReceivedTime.

The code does not always find the most recent email. For example, it will grab an attachment from three days ago or a month ago even though there is email from yesterday.

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set sharedemail = olNS.CreateRecipient("Company@Email.com")
Set olfldr = olNS.GetSharedDefaultFolder(sharedemail, olFolderInbox)
Set folder = olfldr

Set myTasks = folder.Items.Restrict("[Subject]='Payables'")

myTasks.Sort "[ReceivedTime]", True

'Search folder for 'TAX' or 'tax' attachments, save into prior day folder

For Each olMail In myTasks
    If olMail.Attachments.Count > 0 Then
        For Each objAtt In olMail.Attachments
            If InStr(objAtt.Filename, "TAX") Or InStr(objAtt.Filename, "tax") Then
                objAtt.SaveAsFile priorSaveFolder & "TAX.html"
                
                foundFlag = True
             
                Exit For
            End If
        Next objAtt
    End If
    
    If foundFlag = True Then Exit For
Next olMail

Instead of restricting by subject line, I tried restricting to a certain timeframe (e.g. last 7 days).

daysAgo = 7
Set myTasks = folder.Items.Restrict("[ReceivedTime]>'" & Format(Date - daysAgo, "DDDDD HH:NN") & "'")

myTasks.Sort "[ReceivedTime]", True

For Each olMail In myTasks
    If olMail.Attachments.Count > 0 Then

On the line If olMail.Attachments.Count > 0 Then (rest of code is same).

I get an error

Class does not support Automation or does not support expected interface.

If I delete the line myTasks.Sort "[ReceivedTime]", True the code returns an email that is not the most recent in the given time frame.

How can I sort emails to pull the most recent from the set with a specific attachment name?

Upvotes: 0

Views: 246

Answers (2)

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

First of all, I've noticed the following lines of code in your post:

Set myTasks = folder.Items.Restrict("[Subject]='Payables'")

myTasks.Sort "[ReceivedTime]", True

'Search folder for 'TAX' or 'tax' attachments, save into prior day folder

For Each olMail In myTasks
    If olMail.Attachments.Count > 0 Then

Iterating over all items with a specified subject is not really a good idea. I'd recommend checking in the search criteria whether an item has attachments too. So, you could simply iterate over items with attachments removing an extra condition from the code, for example, the following condition check the subject line and attachments:

Filter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & _
                   Chr(34) & " Like '%training%' AND " & _
                   Chr(34) & "urn:schemas:httpmail:hasattachment" & _
                   Chr(34) & "=1"

Pay attention to the fact that the subject line may contain other symbols in addition to the specified in the search string, so all items will be picked up.

Finally, keep in mind that a folder in Outlook may contain different kind of items - appointments, mails, documents, notes and etc. To prevent using non-existing properties and methods I'd suggest checking the message class first.

Upvotes: 1

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66306

That error means you are dealing with an object other than MailItem - you can also have MeetingItem and ReportItem objects in the Inbox folder. Try to declare olMail as a generic Object and check that olMail.Class = 43.

As for the sort, are you saying the right item is not even in the restricted collection? Or that its order is off? In the former case, is it possible the shared inbox hasn't been synchronized yet (if your delegates are cached)?

Upvotes: 0

Related Questions