# Stepping through restricted (date & unread) Outlook mail items (VBA from Access or Excel)



## Darren Bartrup (Feb 11, 2015)

I'm having a slight problem processing Outlook emails and marking them as unread once they've been dealt with.

The code below asks for an Outlook folder and then shows in the immediate window how many unread emails there are today and the subject of each email.  This returns 65 for me.  

Note the commented out line  'oItem.UNREAD = False at the bottom of the code.
If I let this line execute the procedure returns 33 emails - so each email is processed, marked as unread and then removed from the filter but the filter isn't keeping track of which emails to remove.  It just removes the one at the top of the list I guess and causes a similar problem to deleting rows in Excel where you must start at the bottom and move up or risk deleting the wrong row.

Is there a way to process all unread emails and mark them as read?  
I tried putting *mFolderSelected.items.Restrict(sFilter).unread = False* after the loop, but as I expected it didn't work.


```
Public Sub OutlookTest()

    Dim olApp As Object
    Dim nNameSpace As Object
    Dim mFolderSelected As Object
    Dim oItem As Object
    Dim sFilter As String
    
    Dim i As Long


    Set olApp = CreateObject("Outlook.Application")
    Set nNameSpace = olApp.GetNamespace("MAPI")
    
    Set mFolderSelected = nNameSpace.PickFolder
    
    sFilter = "[ReceivedTime] >= '" & Format(Date, "ddddd h:nn AMPM") & "' AND [UNREAD]=TRUE"


    Debug.Print mFolderSelected.items.Restrict(sFilter).Count
    i = 0
    For Each oItem In mFolderSelected.items.Restrict(sFilter)
        i = i + 1
        Debug.Print i & "   : " & oItem.Subject
        'oItem.UNREAD = False
    Next oItem


End Sub
```

Any help greatly appreciated.


----------



## Darren Bartrup (Feb 12, 2015)

Figured it out with help from VBA Filter only returning exactly half the restricted criteria items - Stack Overflow

Just needed to pass the emails in the filter to a collection before processing them.

```
Public Sub OutlookTest()


    Dim olApp As Object
    Dim nNameSpace As Object
    Dim mFolderSelected As Object
    Dim oItem As Object
    Dim sFilter As String
    
    Dim colFilteredEmails As Collection
    Set colFilteredEmails = New Collection
    
    Dim i As Long


    Set olApp = CreateObject("Outlook.Application")
    Set nNameSpace = olApp.GetNamespace("MAPI")
    
    Set mFolderSelected = nNameSpace.PickFolder
    
    sFilter = "[ReceivedTime] >= '" & Format(Date - 1, "ddddd h:nn AMPM") & "' AND [UNREAD]=TRUE"
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Pass each email to a collection before processing. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each oItem In mFolderSelected.items.Restrict(sFilter)
        colFilteredEmails.Add oItem
    Next oItem


    Debug.Print mFolderSelected.items.Restrict(sFilter).Count
    i = 0
    For Each oItem In colFilteredEmails
        i = i + 1
        Debug.Print i & "   : " & oItem.Subject
        oItem.UNREAD = False
    Next oItem


End Sub
```


----------



## Neil Davies (Dec 3, 2015)

I have tried to use this, but it isn't working in Outlook 2016.

I already have a couple hundred thousand mails in a folder (at the same level as Inbox, not a sub of Inbox) and I want a script to mark all as unread.

I'm pretty new to VBA but cannot work out what I am doing wrong, or why I am getting no error.  Any help would be very welcome.


----------



## Darren Bartrup (Dec 4, 2015)

Welcome to the board.

Not sure why my original code isn't working - I haven't got 2016 so can't test.

This code should work.  Place it within a normal VBA module in Outlook.

```
Public Sub MarkAsUnread()


    Dim ns As NameSpace
    Dim TargetFolderItems As Folder
    Dim EmailItem As MailItem
    
    Set ns = Application.GetNamespace("MAPI")
    
    'Update to reflect your folder.
    Set TargetFolderItems = ns.Folders.Item("Mailbox - Darren Bartrup-Cook") _
                              .Folders.Item("Inbox")
                              
    For Each EmailItem In TargetFolderItems.Items
        EmailItem.UnRead = True
    Next EmailItem
    
End Sub
```


----------



## Neil Davies (Dec 4, 2015)

Thanks Darren; I am getting an error when I get to this line, saying object could not be found.

Public Sub MarkAsUnread()


    Dim ns As NameSpace
    Dim TargetFolderItems As Folder
    Dim EmailItem As MailItem

    Set ns = Application.GetNamespace("MAPI")

    'Update to reflect your folder.
Set TargetFolderItems = ns.Folders.item("Mailbox - neil.davies@solutionarchitect.com") _
                              .Folders.item("Inbox")
    For Each EmailItem In TargetFolderItems.Items
        EmailItem.UnRead = True
    Next EmailItem

End Sub

How can I find out the exact name of my mailbox?


----------



## Neil Davies (Dec 4, 2015)

Hi Darren,

I have worked through the name (I don't have "mailbox -" in front of my email address) but I am getting a type mismatch when I get to Next EmailItem ?


----------

