When you loop through the Items in a Folder in Outlook, the items may not be in the order you want. If you want to loop though them in a specific order, you need to create an Items Collection variable and use the Sort method on that variable.
The code at Retrieving E-mail shows how to loop through a folder without regard to the order of the items. The following code sorts the Items in the Inbox by Subject before looping through them. Note that using the Sort method on an Items Collection variable doesn’t change the sort in the Outlook user interface.
Sub GetFromInbox()
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olMail As Variant
Dim i As Long
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace(“MAPI”)
Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
Set olItms = olFldr.Items
olItms.Sort “Subject”
i = 1
For Each olMail In olItms
If InStr(olMail.Body, “excel”) > 0 Or _
InStr(olMail.Subject, “excel”) > 0 Then
ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime
ActiveSheet.Cells(i, 2).Value = olMail.Subject
i = i + 1
End If
Next olMail
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
End Sub
I had 87 emails in my Inbox with “excel” in them. Maybe time to clean that out?
Hi Dick,
Thanks for posting this code above and also what is on your website. I’ve been trying to learn more about the Outlook object model since trying to implement the Getting Things Done system, and this has been very helpful.
In the last couple of days, I’ve created two macros. The first creates a note and lists all my current projects in it. The second macro uses a userform to let me place the project title (using a listbox populated by project folders) on the subject line of a task, which will allow me to easily create a project and next action tracking system in Excel.
Thanks again,
Vince
I’ve found a situation where I think I’ve sorted the collection, but it doesn’t traverse the list in order. I wanted to only search back a certain number of days, in case the number of messages gets too big. Previously it would exit the sub as soon as the date fell out of range. This seemed to miss some emails. I tried ‘for each’ and using and index, but with no luck. Any ideas?
Set flr = f.Folders(“Inbox”)
flr.Items.Sort “Receivedtime”
With flr.Items
‘ For i = 1 To .count
For Each itm2 In flr.Items
‘set itm2=.item(i)
If TypeName(itm2) = “MailItem” Then
‘ Set itm2 = .item(i)
dt = itm2.ReceivedTime
If DateDiff(“d”, dt, Date) >= cboDays.Text Then GoTo skippy ‘ previously would exit here
bd = itm2.body
sb = itm2.subject
from = itm2.SenderName
sentto = itm2.To
isauto = InStr(bd, AUTOIDXTXT) > 0
If cbAutoIndexOnly = 0 Or isauto Then Addemail from, dt, sb, bd, sentto, itm2.entryid, itm2.htmlbody
skippy:
End If
Hi there,
I think there was a MSFT security update that changed the way VBA accesses Outlook. It is no longer possible to get SenderName. I may be wrong, but I can’t seem to get this to work in Excel on my computer.
Thanks
Boris
Any ideas on how to create a threading structure in outlook to organize messages by thread, like a messageboard? This would be extremely helpful in organizing messages – also to remove duplicate content in the replies?
If InStr(olMail.Body, “excel”) > 0
What does this mean?
I’m writing a macro to retrive the body of an outlook 2007 messages into excel.
Angel: Instr means one string is inside of a another. If “excel” is in the body of the email, Instr will return its starting position (an integer). If it can’t find “excel” anywhere in the body, it will return 0.
Thanks Dick
Very helpful article. Thanks.