none
Count Followup Emails using Excel VBA RRS feed

  • Question


  • For full disclosure: I have also posted this question on SO and MrExcel, but I haven't got any responses yet, so I thought of seeking for help here :)

    I am using Office 2013 and I am trying to get a count of the followup items in one of my email folders and this value will be written into a cell.

    So I am using the below code after adding the Outlook Object Library reference:

    Dim Folder As Outlook.MAPIFolder
    
    
    Dim objOL As Outlook.Application
    Set objOL = New Outlook.Application
    
    
    MailboxName = "mymailboxhere"
    Main_Folder_Name = "Inbox"
    Sub_Folder_Name = "Test"
    
    
    Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Main_Folder_Name).Folders(Sub_Folder_Name)
    
    
    Dim itms As Outlook.Items
    Set itms = Folder.Items
    
    
    Dim FollowupItms As Outlook.Items
    Set FollowupItms = itms.Restrict("[FlagStatus] = 2")
    
    
    Followup = FollowupItms.Count
    Worksheets("Sheet1").Range("A1").Value = Followup


    For some reason I keep getting the Followup count as 0 even though there is at least an email flagged as followup.

    To test I have tried the below:

    For Each Msg In itms
    MsgBox Msg.FlagStatus
    Next


    And the Flagstatus of one of the emails is 2 and the same shows in the Msgbox during the test.

    The code works fine when counting emails that are UnFlagged or emails that are marked as Completed.

    This makes absolutely no sense to me. Any thoughts?
    Thursday, September 18, 2014 11:06 PM

Answers

  • Okay, I figured it out: itms.Restrict("[FlagRequest] = 'Follow up' AND [FlagStatus] <> 1") or even simpler itms.Restrict("[FlagStatus] > 1") both are working!
    • Marked as answer by CaptainABC Friday, September 19, 2014 10:33 AM
    Friday, September 19, 2014 10:33 AM

All replies

  • Hi,

    After testing, I find using FlagRequest property in Restrict method can pick out the following up items. When you mark a mail item with a flag through the user interface, the default value of FlagRequest is "Follow up". You can set the [FlagRequest] as "Follow up" as follow.

    Set FollowupItms = itms.Restrict("[FlagRequest] = Follow up")

    to replace the line in the code above

    Set FollowupItms = itms.Restrict("[FlagStatus] = 2")


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by CaptainABC Friday, September 19, 2014 9:49 AM
    • Unmarked as answer by CaptainABC Friday, September 19, 2014 10:08 AM
    Friday, September 19, 2014 9:45 AM
    Moderator
  • I am testing it now, but it looks like using FlagRequest really solved the problem :)
    Friday, September 19, 2014 9:50 AM
  • Okay, after testing there is one problem though, using FlagRequest as Follow up, both Followup and Completed items are counted, is there a way to differentiate between the two as I am only looking to restrict for the mails flagged as followup.

    Friday, September 19, 2014 10:09 AM
  • Okay, I figured it out: itms.Restrict("[FlagRequest] = 'Follow up' AND [FlagStatus] <> 1") or even simpler itms.Restrict("[FlagStatus] > 1") both are working!
    • Marked as answer by CaptainABC Friday, September 19, 2014 10:33 AM
    Friday, September 19, 2014 10:33 AM