Solved

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

Posted on 2008-10-02
5
1,242 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now