Looping through outlooks inbox

[JohnSeito]

Hello,

I have used VBA to send emails out via outlook but how do I loop through outlooks inbox and find a particular email base of on the subject, and the sender and extract the attached document and put it on the desktop, could someone provide an example on how to do that ?

Thanks

 

[RickXL]

Hi,

This should get you started:

' Set Tools-->References-->Microsoft Outlook 15.0 Object Library

Sub SearchAndSave()
    Dim olApp       As Outlook.Application
    Dim olInBox     As Outlook.Folder
    Dim olItem      As Object
    Dim olMsg       As Outlook.MailItem
    Dim olAtmt      As Outlook.Attachment
    Dim DeskTop     As String

    Set olApp = CreateObject("Outlook.Application")
    Set olInBox = olApp.Session.GetDefaultFolder(olFolderInbox)
    DeskTop = CreateObject("Wscript.Shell").SpecialFolders("Desktop") & "\"
    
    For Each olItem In olInBox.Items
        If olItem.Class = olMail Then
            Set olMsg = olItem
            If olMsg.SenderName Like "*Rick*" And olMsg.Subject Like "*June*" Then
                For Each olAtmt In olMsg.Attachments
                    olAtmt.SaveAsFile DeskTop & olAtmt.Filename
                Next
            End If
        End If
    Next
End Sub

I have used "early binding" because I can never remember the properties and methods of all the objects. If you use the Tools-->References menu to set the Outlook library then all the options are shown when you place a dot after the object name. A dropdown list is shown.

Note that not everything in an inbox is a mail item so you need to make olItem an Object. After that you need to check what sort of object you have located. I used another variable name for this, olMsg, to keep the dropdowns working.

I assumed that you will be looking for emails from Rick with June in the subject

Warning: The olAtmt.SaveAsFile appears to overwrite without warning so if all your files have the same name you will only get one on the desktop.

I used WScript to get the Desktop name - there are other ways, including just hardcoding it.


Regards,

 

[JohnSeito]

looks good but when I tried your code and I have tried other codes as well (from others).
It always ended giving me the same starting point or same inbox mail to scan (and I don't think it scans any other inbox mail).

For example the sender variable is
olmsg.sendername = "sender"
and the subject is
olmsg.subject as = "subjct".

It never starts from the most recent email in the inbox but starts at, in this case 05/22/2017 and this particular mail in the inbox when our current date is now 06/06/17

 

[JohnSeito]

Ok, I have looked at it again and the code looks good. The code works, it just didn't start from the most recent inbox mail however that is fine.
I am still looking into it and I will let you know if I have any questions or if any issues pops up. But the code is EXCELLENT.

 

[JohnSeito]

I just noticed this code error message,

olatmt.SaveAsFile desktop & olatmt.Filename


Object variable or with block variable not set

 

[JohnSeito]

Sorry for all the messages. But I corrected my code and I see it saved the attachment to my desktop. Maybe I copied your code incorrectly before, but everything is EXCELLENT now. Thanks

 

[RickXL]

Glad you made it work for you in the end.

I suspect that Outlook has a file that it appends with new emails so the newest ones will be at the bottom. Any loop through that file will start from the oldest and work down to the newest.

The code is just a starting point. If you need it to search sub-folders of the InBox or other folders or accounts then changes will be required.

Anyway, thanks for keeping me informed of progress.

Good links for more information include: Ron de Bruin's site, https://www.rondebruin.nl/win/s1/outlook/mail.htm and that of Diane Poremsky, https://www.slipstick.com/. Slipstick includes stuff about Microsoft Exchange and some of the more esoteric parts of Outlook.


Regards,

 

[JohnSeito]

Okay, thanks for the link. It looks very helpful. Going through the sub-folders or other folders or account will be another set of challenge for me later on if I needed it.
At this point these email are only in the inbox so I am fine.

This code is just perfect. Cheers !