Results 1 to 6 of 6

Thread: Stepping through restricted (date & unread) Outlook mail items (VBA from Access or Excel)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  2. #2
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    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
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  3. #3
    Board Member
    Join Date
    Dec 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  5. #5
    Board Member
    Join Date
    Dec 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  6. #6
    Board Member
    Join Date
    Dec 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •