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

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top