Solved

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

Posted on 2008-10-02
5
1,286 Views
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.
0
Comment
Question by:lll7883
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22667357
Hi, lll7883.

Extract those two fields to what?
0
 

Author Comment

by:lll7883
ID: 22675769
csv or excel file
0
 
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.
0
 

Author Comment

by:lll7883
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
0
 
LVL 76

Accepted Solution

by:
David Lee earned 250 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
    Next
 
    'Logout of the CDO session'
    mapSession.Logoff
    
    'Cleanup'
    Set mapSession = Nothing
    Set objFSO = Nothing
    objFile.Close
    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
        Next
    End If
    
    'Process all the sub-folders under this folder'
    For Each olkSubFolder In olkFolder.Folders
        RMProcessFolder olkSubFolder
    Next
    
    'Cleanup
    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
    Err.Clear
    intValue = mapFields.Item(CdoPR_ACTION).Value
    If Err.Number = 0 Then
        If (intValue = 261) Or (intValue = 262) Then
            Err.Clear
            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
            Err.Clear
            strCompleted = mapFields(CdoPR_FLAG_COMPLETE).Value
            If Err.Number <> 0 Then
                strCompleted = "Unk"
            End If
            GetMAPIData = strReplyTime & "," & strCompleted
        Else
            GetMAPIData = ","
        End If
    Else
        GetMAPIData = "NA,NA"
    End If
    
    'Clean up
    Set mapMessage = Nothing
    Set mapFields = Nothing
End Function

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
imap sent folders sychronization 2 46
Exchange 2010 Mailbox 6 35
Disable Outlook Security Warning Window Access 2016 5 51
VB script to continue despite error 2 16
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

828 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