1

I have written a number of macros in excel which open up emails from my inbox, save the attachment and then do some processing on the attachments. I now want to tidy everything up by moving all the emails from a specific sender to a specified folder and marking them as read.

I have written (for which read borrowed and modified) the following VBA. The problem is that it doesn't loop through all of the Items in the inbox.

Const olFolderInbox As Integer = 6

Sub MoveMail()

Dim oOlAp As Object, oOlns As Object, oOlInb As Object
Dim oOlItm As Object, oOlAtch As Object, SubFolder As Object
Dim eSender  As String
Dim i As Integer
i = 1

Set oOlAp = GetObject(, "Outlook.application")
Set oOlns = oOlAp.GetNamespace("MAPI")
Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

Set SubFolder = oOlInb.Folders("FolderLevel2Name").Folders("FolderLevel3Name").Folders("FolderLevel4Name")

Debug.Print oOlInb.Items.Count

For Each oOlItm In oOlInb.Items
    eSender = oOlItm.SenderEmailAddress
    Debug.Print i & " --> " & eSender

    If eSender Like "name@domain.co.uk" Then
        'Debug.Print eSender
        oOlItm.UnRead = False
        DoEvents
        oOlItm.Save
        oOlItm.Move SubFolder
    End If

    i = i + 1

Next

End Sub

When I run this some of the items from name@domain.co.uk are moved to the correct folder, but not all. However without changing anything at all simply running it again moves more of the emails. Eventually if I run the macro enough times it eventually moves them all, without me changing a thing.

For example I tried to run it with 15 emails from name@domain.co.uk and 4 from other senders. The for loop stopped after processing 11 of the items in the inbox, 9 of which were from the specified sender and were moved the other two were correctly left in the inbox.

I then ran it again with the remaining 10 emails of which now 6 were from the specified sender. This time it looped through 6, 4 of which were from the specified sender and were moved. Eventually upon the third attempt it eventually looped through all of the remaining 6 items in the inbox, moving the remaining 2 from the specified sender. What's going wrong?

Stephen
  • 11
  • 1
  • Try looping backwards: `For i = oOlInb.Items.Count to 1 step -1: set oOlItm = oOlInb.Items(i)` – Rory Feb 26 '19 at 13:08
  • That's brilliant, thanks. It works now. I still don't understand why what I had done before doesn't work. If anyone can answer that I'd be grateful. – Stephen Feb 26 '19 at 13:22
  • 2
    When you move items, the others 'move up' in the collection, but your `For each` counter moves on to the next item, which means you skip some of them. That's why repeated running eventually gets them all. – Rory Feb 26 '19 at 13:37

0 Answers0