How to use vba to export Outlook 2003 replied and flag complete date/time?

Posted on 2008-10-02
Medium Priority
Last Modified: 2012-05-05
I need to develop some performance metrics from Outlook 2003 to track the time a email is replied/forwarded and completed. I am currently using cfg to display the fields and copy all selected items to clipboard. This method has to be done manually and one folder at a time. I need a macro to go thru all folders and subfolders to extract the two fields.
Question by:lll7883
  • 3
  • 2
LVL 76

Expert Comment

by:David Lee
ID: 22667357
Hi, lll7883.

Extract those two fields to what?

Author Comment

ID: 22675769
csv or excel file
LVL 76

Expert Comment

by:David Lee
ID: 22676869
This is possible, but before depending on this data you should understand how Outlook calculates the time an email was sent or forwarded.  Outlook does not actually store this information.  Instead, it uses a MAPI property to store what it calls the "last verb executed" and the date/time of that execution.  When you reply to an item, the last verb field is set to a value indicating that a reply was the last action and the date/time is recorded.  The problem with this is that the reply doesn't have to be sent.  Open a message, select reply, then delete the reply.  Now, re-open the original item and you'll see that Outlook says you replied at some date/time even though you never actually sent the reply.  That makes the data useless.  If I knew that I was being tracked on this metric, then I could simply open the item shortly before flagging it as complete, select reply, then cancel the reply.  When you collected the metrics it'd look like I replied and then flagged the item complete a short time later.  I'd never fail at meeting my metrics.  If you really need to collect meaningful metrics on this, then you need a means of knowing when a reply or forward was actually sent.  Outlook does not store that information.  You'd have to design a means of collecting it.  

As a side note, Outlook's object model does not expose this information.  It's possible to get it, but it requires using MAPI calls.  I'm letting you know because that will affect your ability to continue using a solution should you eventually upgrade to Outlook 2007.

Author Comment

ID: 22693054
I understand the data may be useless if one chooses to cancel the reply just to make the metrics look good. I have some other ways to control this potential problem.  You said it is possible.  Do you have any example of MAPI calls? Thanks
LVL 76

Accepted Solution

David Lee earned 750 total points
ID: 22694856
Here's the code for doing this.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on Module1
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
5.  Edit the code as needed.  I included comment lines wherever something needs to or can change
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set the Security Level to Medium
10. Close Outlook
11. Start Outlook

Run the main macro, RetrieveMetrics.  It should process every Outlook folder and extract the information you're looking for.  It uses the following logic when processing folders/items.

1.  If the folder isn't a mail folder (e.g. calendar, contacts, tasks), then skip it.
2.  If the item in a folder isn't a mail item (e.g. a receipt, task request, etc.), then skip it.
3.  If the item hasn't been replied to or forwarded, then skip it.
4.  If the date cannot be retrieved for either field, then write "Unk" in its place.

I tested the code against a single folder and it appears to work okay.  I did not test against a folder tree.  Currently the macro only writes out the two dates.  If you need it to write out more information, then you can either mod the code yourself or let me know what other information you want and I'll make the change and post an update.

CDO must be isntalled on the computer you run this from.  If it's not, the code will error out immediately.
Dim mapSession As Object, objFile As Object
Sub RetrieveMetrics()
    'Declare some variables'
    Dim olkStore As Outlook.MAPIFolder, objFSO As Object
    'Create a CDO session and login to it using the existing Outlook session'
    Set mapSession = CreateObject("MAPI.Session")
    mapSession.Logon , , False, False, 0
    'Create the output file'
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the file name and path on the following line as needed'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Metrics.csv", True)
    'Launch the main checking process'
    For Each olkStore In Session.Folders
        RMProcessFolder olkStore
    'Logout of the CDO session'
    Set mapSession = Nothing
    Set objFSO = Nothing
    Set objFile = Nothing
    MsgBox "Completed", vbInformation + vbOKOnly, "Retrieve Metrics"
End Sub
Sub RMProcessFolder(olkFolder As Outlook.MAPIFolder)
    'Declare a few variables'
    Dim olkItem As Object, olkSubFolder As Outlook.MAPIFolder, arrMetrics As Variant
    'Process the items in the folder'
    If olkFolder.DefaultItemType = olMailItem Then
        For Each olkItem In olkFolder.Items
            If olkItem.Class = olMail Then
                Debug.Print olkItem.Subject
                arrMetrics = Split(GetMAPIData(olkItem), ",")
                If arrMetrics(0) <> "NA" Then
                    objFile.WriteLine Chr(34) & arrMetrics(0) & """,""" & arrMetrics(1) & Chr(34)
                End If
            End If
    End If
    'Process all the sub-folders under this folder'
    For Each olkSubFolder In olkFolder.Folders
        RMProcessFolder olkSubFolder
    Set olkItem = Nothing
    Set olkSubFolder = Nothing
End Sub
Function GetMAPIData(olkItem As Outlook.MailItem) As String
    'Set the error handler to allow us to handle errors in code
    On Error Resume Next
    'Declare some constants
    Const CdoPR_FLAG_COMPLETE = &H10910040
    Const CdoPR_ACTION = &H10800003
    Const CdoPR_ACTION_DATE = &H10820040
    'Declare a few variables
    Dim mapMessage As Object, _
        mapFields As Object, _
        strReplyTime As String, _
        strCompleted As String, _
        intValue As Integer
    'Get the selected message's ID
    Set mapMessage = mapSession.GetMessage(olkItem.EntryID, olkItem.Parent.StoreID)
    'Get message fields
    Set mapFields = mapMessage.Fields
    'Get the time the item was replied to or forwarded
    intValue = mapFields.Item(CdoPR_ACTION).Value
    If Err.Number = 0 Then
        If (intValue = 261) Or (intValue = 262) Then
            strReplyTime = mapFields.Item(CdoPR_ACTION_DATE).Value
            If Err.Number <> 0 Then
                strReplyTime = "Unk"
            End If
            'Get the date the item was flagged as completed
            strCompleted = mapFields(CdoPR_FLAG_COMPLETE).Value
            If Err.Number <> 0 Then
                strCompleted = "Unk"
            End If
            GetMAPIData = strReplyTime & "," & strCompleted
            GetMAPIData = ","
        End If
        GetMAPIData = "NA,NA"
    End If
    'Clean up
    Set mapMessage = Nothing
    Set mapFields = Nothing
End Function

Open in new window


Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Outlook is not just an email client. It's a personal information manager that has great features. Sometimes you may confront serious problems which leave you helpless. For a novice user, troubleshooting the problem is not easy. I've listed top 10 ti…
Configure external lookups on for external mail flow on Exchange 2013 and Exchange 2016.
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question