Exporting Unflagged Message Counts to Excel


This post is for Chris Bull who asked for some modifications to the code in my post titled Exporting Outlook Message Counts to Excel. Chris needs to export message counts for messages in two public folders that meet specific conditions. Here’s how Chris explained it to me in his comment.

I need to Create an Excel spreadsheet which will count the total number of ‘unflagged’ messages in two named public folders as well as the number of ‘unflagged’ messages over 7 days old. It would be useful to tag this with the date the counts were generated. Ideally I would like this to be done automatically on a weekly basis.

Sorry, to clarify there are people who mark emails with a flag and others who mark them with a tick. So really I need ‘unflagged and unticked’ as the criteria!

In order to meet Chris’ requirements I modified the code from my original post by

  • Adding the ability to pass two Outlook folder paths instead of one.
  • Adding the necessary conditional statements to determine if each item in the folder is flagged or not.
  • Changing the output to write the date and the four counts, two for each folder, to the spreadsheet.


I tested the code on my system and verified the counts. They were correct. Chris will need to test it on his system to ensure it’s working properly before placing it into production.

One additional note. For those of you who may not be familiar with Outlook folder paths, here’s how they work. A folder path in Outlook is essentially the same as a folder path in the file system. The one difference being that Outlook folder paths do not include a drive letter. The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \). Consider the following folder structure:

Mailbox - Doe, John
    - Calendar
    - Inbox
    - Tasks
Personal Folders
    + Marketing
        + Proposals
        + Reviews
    + Projects
        + Project 1
        + Project 2
Public Folders - John.Doe@company.com
    + All Public Folders
        + Projects
            + Project Blue
            + Project Green

The path to “Inbox” is “Mailbox – Doe, John\Inbox”.
The path to “Reviews” is “Personal Folders\Marketing\Reviews”.
The path to “Project 1” is “Personal Folders\Projects\Project 1”.
The path to “Project Green” is ” Public Folders – John.Doe@company.com\All Public Folders\Projects\Project Green”

Requirements.

  • Outlook 2007 – 2010.
  • Any version of Windows.
  • The spreadsheet(s) must already exist.
  • The computer must be on for the task scheduler to function.

Instructions.

  1. Open Notepad.
  2. Copy the code below and paste it into Notepad.
  3. Edit the code as needed. I’ve included comments where changes can/should be made.
  4. Save the file with a .vbs extension. You can name it anything you want.
  5. Create a scheduled task that runs periodically. You can set the frequency to whatever you want.
  6. Set the task to run this script.
'Constants
Const olNoFlag = 0
Const olMail = 43

'Declare some variables
Dim olkApp, olkSes

'Connect to Outlook
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

'Call the export process once for each folder count to be exported
'Format is ExportMessageCountToExcel <Path to First Outlook Folder>, < Path to Second Outlook Folder >,  <Path and filename of the Excel file to export to>, <Number of the sheet the count goes on>
'The following lines are examples.  Edit them as needed.  Add additional lines as desired.
ExportMessageCountToExcel "Mailbox - Doe, John\Inbox", "Projects\Project1", "c:\Users\jdoe\Documents\Message Counts.xlsx", 1

'Disconnect from Outlook
olkSes.Logoff
Set olkSes = Nothing
Set olkApp = Nothing
WScript.Quit

Sub ExportMessageCountToExcel(strFolder1, strFolder2, strWorkbook, intSheet)
    Dim olkFld, olkItm, excApp, excWkb, excWks, lngRow, lngUnflaggedCount1, lngUnflaggedCount2, lngUnflaggedOver7Count1, lngUnflaggedOver7Count2
   
    'Process the first folder
    Set olkFld = OpenOutlookFolder(strFolder1)
    For Each olkItm In olkFld.Items
        If olkItm.Class = olMail Then
            Select Case olkItm.FlagStatus
                Case olNoFlag
                    If DateDiff("d", olkItm.ReceivedTime, Date) > 7 Then
                        lngUnflaggedOver7Count1 = lngUnflaggedOver7Count1 + 1
                    End If
                    lngUnflaggedCount1 = lngUnflaggedCount1 + 1
            End Select
        End If
    Next
   
    'Process the second folder
    Set olkFld = OpenOutlookFolder(strFolder2)
    For Each olkItm In olkFld.Items
        If olkItm.Class = olMail Then
            Select Case olkItm.FlagStatus
                Case olNoFlag
                    If DateDiff("d", olkItm.ReceivedTime, Date) > 7 Then
                        lngUnflaggedOver7Count2 = lngUnflaggedOver7Count2 + 1
                    End If
                    lngUnflaggedCount2 = lngUnflaggedCount2 + 1
            End Select
        End If
    Next

    'Write the data to the spreadsheet
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(strWorkbook)
    Set excWks = excWkb.Worksheets(intSheet)
    lngRow = excWks.UsedRange.Rows.Count
    If lngRow = 1 Then
        If excWks.Cells(lngRow,1) <> "" Then
            lngRow = lngRow + 1
        End If
    Else
        lngRow = lngRow + 1
    End If
    excWks.Cells(lngRow, 1) = Date
    excWks.Cells(lngRow, 2) = lngUnflaggedCount1
    excWks.Cells(lngRow, 3) = lngUnflaggedOver7Count1
    excWks.Cells(lngRow, 4) = lngUnflaggedCount2
    excWks.Cells(lngRow, 5) = lngUnflaggedOver7Count2
   
    'Clean-up the objects
    Set excWks = Nothing
    excWkb.Close True
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    Set olkFld = Nothing
    Set olkItm = nothing
End Sub

Function OpenOutlookFolder(strFolderPath)
    Dim arrFolders, varFolder, bolBeyondRoot
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = olkApp.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function
Advertisements

14 comments on “Exporting Unflagged Message Counts to Excel

  1. Hi David,

    I couldn’t find the appropriate post to ask a query regarding a VBA code on excel.

    I need your help to build VBA code for below.

    I have 2 sheets in on excel workbook.

    I need to check cell in column F (which has about 1000 cells as data, keeps on changing with every new report)in sheet “Alpha” and i want to look up that cell in sheet “beta ” in column “e”.

    If cells matching then i want to paste the entire row in sheet “delta” and if not matching i want to paste it in sheet ” gamma”.

    This needs to be loop because i wouldnt know how many cells would have data.

    you had helped me before with one of these kind and thats why i am reching to you again.

    Hope to hear back soon!

    Thank you
    Chirag

    • Hi, Chirag.

      Good to hear from you again. The best place to post a comment for a new topic is probably my Suggestions page.

      I need to make sure I understand what you want. Does this correctly describe the process?

      1. Open the workbook
      2. For each row in sheet “Alpha”
      3. Read the value from column F
      4. Search column E on sheet “Beta” for a match
      5. If a match is found, copy the current row from sheet “Alpha” to sheet “Delta”
      6. If a match is not found, then copy the row from sheet “Alpha” to sheet “Gamma”

      If that captures the process, then

      1. What’s the last column used in sheet “Alpha”?
      2. Can the solution assume that it’s reached the end of data when it hits a blank cell in column F on sheet “Alpha”?

    • Hi David,

      Thank you for your response.

      yes, the procedure you have descried is correct.

      1. What’s the last column used in sheet “Alpha”?

      Last column is P

      2. Can the solution assume that it’s reached the end of data when it hits a blank cell in column F on sheet “Alpha”?

      Yes, it can assume the blank cell as the end of the data.

      Thanks again!

      Chirag

    • Chirag,

      One additional question. I was re-reading your original comment and noticed that you said “I have 2 sheets in one Excel workbook” while then mentioning sheets Alpha, Beta, Delta, and Gamma. Does that mean that Alpha and Beta are in one workbook with Delta and Gamma in another, or are all four in the same workbook?

  2. Name of my Mail Box is “Service Delivery”
    and in the arguments, I’m using it as:
    ExportMessageCountToExcel “Mailbox – Service Delivery\Inbox”, “C:\Documents and Settings\mbatra\My Documents\Message_Counts.xlsx”, 1

    I wanted to include screen shot of my outlook mailbox, but this browser won’t allow.
    Can I email you at TechnicLee@earthlink.net?

    Thanks & Regards.
    Mohinder

    PS: I read your brief profile on this site and am more than impressed.

  3. David,

    Yes I can see that this could be very annoying but I was hoping that if I could resolve the conflicts then they might not reoccur, or at least that in resolving it I might glean some clue as to how to stop it reoccurring….

    The other option I have been thinking about as a workaround is to have the code copy all messages in the public folder to a local folder in my inbox, count them, and then delete the local versions. I have just tested this manually and the code you wrote will work if I copy the folder to my inbox first- problem is there is 6000 emails in one of the folders and this takes a while to copy…

    I have spent the last couple of hours looking into how to resolve these conflicts but it doesn’t seem like anyone else has managed it! So far as I can figure out this is a known issue with using public folders, multiple users and an exchange server – the system is unable to prioritise changes as they are made resulting in different copies of the same item. I think that in this case my Outlook may have downloaded an item with no flag but when it checks the server, someone else has flagged the item and so it doesn’t know which one to count for the code.

    So frustrating to be so close and yet so far!

    Chris

  4. Hello again,

    I have done a little research and this seems to relate to synchronisation between Outlook and the Exchange server. I have tested the code on my personal email and it works fine – it just won’t work on any public folders….

    I’ll keep at it – any ideas would be gratefully received…

    Chris

  5. Hi David,

    Firstly thank you very much for your help with this, especially so promptly! I am getting an error message right now:

    Line:31
    Char:13
    Error:One or more items in the folder you synchronised do not match. To resolve the conflicts, open the items, and then try this operaqtion again.
    Code:80020009

    I had already resolved one error relating to the folder path but am not sure what to do with this one…

    I have double-checked and there are ten (unflagged and unticked) items in one folder and eight in the other. I have not altered the code in any way other than to specify the public folder paths and Excel output file.

    Any ideas?

    Chris

    • You’re welcome, Chris.

      The code doesn’t synchronize anything and line 31 is checking to see if the current item is flagged. I can’t imagine what’s causing the error. Perhaps something to do with the public folder syncing to your local computer. That would be something outside of the code.

    • Hi David,

      There may possibly be a way to resolve this using

      MailItem.IsConflict and then when it returns ‘true’ to use

      MailItem.Display to make it should appear with information on resolving the conflict

      Does this sound feasible?

      Chris

    • Possibly. It sounds like you’d have to respond to a prompt for each message in conflict. That sounds like it’d be really annoying, especially if there are a lot of messages in the folder that are in conflict. I can add the necessary code and you can see what happens.

      I think a better approach would be to figure out why the messages are in conflict and see if there’s a way to prevent that from happening.

    • Hi David,

      I think I have managed to resolve this!!!

      The conflicts seem to be old conflicts where two people tried to edit the same message at the same time. No-one ever bothered to resolve the conflict.

      There is a conflicts folder but for some reason Outlook doesnt move them there so I had to manually search through 8000+ messages trying to find the 30 or so which had conflicts and then resolving each one.

      This I have now done and the code now works! Totally awesome!!

      There is the potential for this to reoccur though so if you could try to add in a prompt when it finds a conflict which opens it to allow resolution (like I suggested earlier) that would be really helpful. There is only ever likely to be one or two which pop up and it will alert me to their presence as well as allowing me to resolve. Or in fact if it could automatically select whichever one is flagged/ticked and resolve automatically that would be even better.

      Thanks again for all your help so far,

      Chris

    • Chris,

      That’s good news. I’ll see about modifying the code. I don’t use public folders myself, so I have no means of testing the changes once I’ve made them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s