Taking Outlook and XML to Task in MS Access

Taking Outlook and XML to Task in MS Access

Garry Robinson

Summary

One of the most important part of managing a software business is keeping track of all the different jobs, possible jobs and other things that pass your way. When you are not so busy, your systems for managing these requests can be on paper, in a database, in a word file and more than likely in your email inbox or task box. When you are busy, trying to pull all of these things together at the one time can be a little hit and miss. In this article, Garry and Scott show how they have integrated Outlook tasks into their Access database so that they can prepare reports and hook into other related project data. This exercise also included a voyage into XML exporting from Outlook and attempts to view that information inside Microsoft Access.

Most of the previous discussions in Smart Access about Outlook have concentrated on using linked tables to retrieve the data from Outlook. Unfortunately when it comes to Outlook Tasks, most of the important task fields such as Body and Subject are invisible to the Outlook linker. So in this case, a more classical Automation approach was taken to retrieve the information. We hope that you enjoy the investigation as much as we did.

The Download Database

If you wish to try this Outlook demonstration, you will need a copy of either Outlook 2000 or Outlook 2002 on your computer. Now you need to setup some demo Tasks in Outlook (if you haven't already got some). For each task, you need to allocate a Category. To make this download database easier to understand, enter an category of "Project 1" or "Project 2" for some of the tasks that you setup. You will find the Category field on the bottom right hand side of the New Task form.

The download database is in Access 2000 format. The database will look through your Outlook tasks and bring across the tasks and store them in a table in the demonstration database. The software will also generate an XML file of the same tasks and store that in the same directory as the software.

The download database requires external references as follows
Microsoft Outlook 9.0 Object Library
Microsoft ActiveX Data Objects 2.5
Microsoft DAO 3.51 Object Library

You will need ADOXDB if you are at all interested in the XML exports and imports. Download the installation kit for this from

http://www.microsoft.com/data/

You probably will not need a DAO reference as I have commented out all the code that uses DAO. Initially I wrote all the non XML software using DAO as I am running the software in an Access 97 database. Search for lines with ' DAO comments and uncomment them. Conversely, comment all the lines with ' ADO so that you are using the correct recordset handler for the code. If you are using DAO, then you will want probably want to comment all lines with "XML File >>" so that XML exporting is turned off.

If you have Microsoft Outlook 97, you must program in VB script rather than vb. This is not covered in this article.

Microsoft Outlook 2000

When it came to programming Microsoft Outlook, I turned to my co-author Scott to work out all the tricky bits. Microsoft Outlook programming is not easy as the number of samples that are around for programming are quite scarce. This particularly applies to the document model for folders and sub folders and the different types of data that is stored in the folders. Without going into all the finer technical details about Outlook , here are some simple suggestions to making Outlook easier to manage as a programmer. First if you stick to the standard top level folders, then programming is far easier. By this I mean, Contacts, Inbox, Calendar, Notes and Tasks. Fortunately for us, these folders all have special constants to define where they are located. In this article, we are only interested in the Tasks folder and our initial piece of code start off as follows

Dim appOutlook As Outlook.Application
Dim appNS, appTasks
Dim AppItems As Outlook.Items

Set appOutlook = New Outlook.Application
Set appNS = appOutlook.GetNamespace("MAPI")
Set appTasks = appNS.GetDefaultFolder(olFolderTasks)
Set AppItems = appTasks.Items

The above lines of code are the basic code that you need to hook into the items inside the Outlook folders. The Outlook constant olFolderTasks defines that we would like to look at the Tasks folder. If you select the olFolderTasks constant and hit F2, you will open the object browser to show all the special constants for the important (and unmovable) Outlook folders. If you are following the code in the sample database, you will find that I have now jumped to the middle of the subroutine called AddOutlookTasks. This is because I first want to demonstrate the mechanics of getting the information from the Tasks folder.

Now we would like to work our way through all the tasks item by item and store the values for that task into variables. This is handled by first finding out the total number of tasks and then moving through the collection as follows. As you can see below, this actually is quite easy. The code is complicated a little by a number of tests that are performed to limit the tasks that are transferred. This includes testing if the Task is fully completed (designated by PercentComplete = 100) and also looking to see if the Task has been deferred. These are not transferred

ItemCount = AppItems.Count

' set up append query statements for every task

On Error GoTo 0
For i = 1 To ItemCount
itPerComp = AppItems(i).PercentComplete
If itPerComp < 100 And _
    AppItems(i).Status <> olTaskDeferred Then
    On Error Resume Next
    If (Not IsMissing(CatRequired) And _
      AppItems(i).Categories = CatRequired) Or _
      IsMissing(CatRequired) Then
      On Error GoTo 0
      DoCmd.Echo True, AppItems(i).Subject
      itDateCreated = AppItems(i).CreationTime
      itSubject = AppItems(i).Subject
      itBody = AppItems(i).Body
      itCategory = AppItems(i).Categories
      itImportance = AppItems(i).Importance
    End If
End If
Next
Set appOutlook = Nothing
Listing 1 – The Outlook code that transfers Task item fields to variables

If you are a little low in Outlook programming experience level, here are some tricks to find out important things that you will need to know in Outlook. Open the Object Explorer (hit F2) and choose Outlook as the library to search and enter TaskItem to search for TaskItems. This shows you all the fields that are available in Task Items as well as all the other properties, methods and events that relate to the TaskItems. Without the object explorer, finding the actual field names in the collection is next to impossible. If you wish to program other types of Outlook folders, search for MailItems, ContactItems, NoteItems and AppointmentItems. Then you can then substitute the task field name collection field names.

<< Click to expand
Figure 1. The object explorer shows you all the fields in an Outlook task.

That’s all there is to looping through your Outlook tasks. Setup the collection, loop through it and store the results in variables or directly into your target table or recordset.

Storing The Results In An Access Database

Now as the Smart Access reader is more than likely going to be storing the Outlook information in an Access database, I will step back a bit in the sample code to demonstrate the important visual basic that is required to append the Outlook Tasks to an Access table. To manage the importation of the Outlook Tasks into the database, I have setup a subroutine which has two important arguments that need further description.

Call AddOutlookTasks(User_FX)

Sub AddOutlookTasks(userNameRequired As String, _
Optional CatRequired As Variant)

The first and very important argument is the system username.   If you switch machines or login with different usernames, you or your colleagues are going to be accessing a different Outlook task list.   Importing those tasks in the database will mean that it will be necessary to identify which task list is actually going to be imported into the database. To do this, I use the Environ function that identifies the end user from an environment variable that is stored on any NT, Win XP or Win 2000 PC.    Other operating systems do not carry this environment variable.

Public Function User_FX() As String
' Assume NT, Win2000 or WinXP User
User_FX = Environ("USERNAME")
If User_FX = "" Then
' Must be Win95 98 user

    User_FX = "Unknown"
End If
End Function

If the NT login identifier was FreddyM, then all my tasks in the Access database would be identified with a FreddyM username.   This allows us to process tasks for more than one person in the same system.   This could be very useful to managers who need to assess user tasks when they are sick or if they need to be reallocated.

The other argument to the subroutine is that of Category Required.   In some parts of our software, we find it more convenient (faster) to only update all tasks for a given category. You will see references to the categoryReq variable in the code that follows.

The first step in the process will more than likely be clearing out any existing task information allready stored in your database.   This is handled in this subroutine by using an SQL delete statement.   There is an additional where clause in the SQL that I generate which allows the subroutine to only delete and load Outlook tasks from one category using the CatRequired field.

Const outlookTbl = "tblOutlookTasks"
Dim qryStr As String

qryStr = "delete from " & outlookTbl & _
" OutlookTasks where systemUserName = '" _
& userNameRequired & "'"
If Not IsMissing(CatRequired) Then
qryStr = qryStr & " and category = '" & CatRequired & "'"
End If

DoCmd.SetWarnings False
DoCmd.RunSQL qryStr
DoCmd.SetWarnings True

Now I will open an ADO recordset so that I can write records to the Access table that holds all the tasks. This Access table is defined by the outlookTbl constant and its structure should match that of the Outlook task fields.

Set rstTasks = New ADODB.Recordset
With rstTasks
   .ActiveConnection = CurrentProject.Connection
   .CursorType = adOpenKeyset
   .LockType = adLockOptimistic
   .Open outlookTbl, , , , adCmdTable
End With

Now I can return to the Outlook items that were being processed and stored in the variables.   These variables are then added to a new record and appended to the tasks table in the Access database.

itSubject = AppItems(i).Subject
itBody = AppItems(i).Body

With rstTasks
.AddNew
!DateCreated = itDateCreated
!Subject = itSubject
!Category = itCategory
!Body = itBody
!PercentComplete = itPerComp
!Importance = itImportance

' Add the NT/WinXP/Win2000 username
!SystemUsername = userNameRequired

.Update
End With

Just a quiet word of warning. Constant loading and reloading of Access tasks into an Access table will bloat your database.   You will need to be vigilant with your compacting of the database. If you are only storing the tasks to produce reports, you may want to use a temporary database and link to the tasks using the remote queries that I described in Smart Access last year.

Integrating Outlook Tasks With The Access Database

As Microsoft Outlook stores its data in a very flat structure, merging this information into a corporate world will require integration with a database.   To do this, I use the projectID primary key in the Project table that my Access database as the common key between the Access database and the Outlook tasks table. In Outlook, the project code is stored in the Category table.   Figure 3 shows how the Outlook items can be viewed in a sub form below the projects table to view all the tasks for a given project. The Category field was chosen because all the different Outlook collections such as Calendar, Inbox and Notes also have a very prominent Category key. This seemed like an appropriate place to store a project identifier.

The best way to describe how these 2 separate programs relate to each other is to look at both the project table and the outlook table that I have imported using a simple Access relationship.    This is shown in Figure 2.

Figure 2 - Relationship diagram showing how Outlook tasks are integrated with the Access database

Figure 3. Outlook tasks are integrated using the Category field to join with project ID field

Naturally, all tasks that so not have a Project will end up as orphans in this model.   These can be easily picked up by adding an Orphan project or simply deleting all Tasks without projects during the import stage.

But the main reason why I do all this integration is to produce a really neat report that shows details on a individual project along with the tasks for that project. The report all sorts the tasks from highest priority to lowest priority.    On the top of the form in figure 3, you will see a hyperlink entitled Task Printout. The click event for this hyperlink fires the following code to open the report with a project filter. The report which shows one task per page is shown in figure 4.

DoCmd.OpenReport "rptProjectsOutlook_A5", _
acPreview, , "ProjectID = '" & Me!ProjectID & "'"

Figure 4 - Shows a single Outlook task per page filtered by project.

As this example is very relevant to programmers, I have found that A5 paper (1/2 size of A4) is a very handy format for jotting down discussions on different programming tasks. With A5 being relatively small, it is easy to keep all the tasks apart and sort them into programming order. When I use larger sheets of paper, I always store multiple tasks on the one sheet and have a bit of trouble sorting out the jobs.   This also applies to allocating the jobs to my subcontractors.

Using XML Instead Of Access Tables

As mentioned above, I am always a little reluctant to storing temporary data in an Access database due to the bloat factor.   So I decided to look into other ways to transfer the database from Outlook across to Access. My initial thoughts were to make either a comma delimited file or a fixed width text file. This method was quite appealing as Access has a very good linker for both of these formats of files. Whilst I believe that these are quite legitimate ways to transfer the data, I decided instead it was time to refine my skills with XML and see if Access 2002 offered any great improvements in XML as was promised in the promotional material.

So now I return to my earlier exploits with the Outlook task items to help describe what I came up for XML creation. Initially as I really like the simplicity of the ADO save recordset method as a simple way of making an XML, I setup the following to establish my ADO recordset.

Dim rstXML As New ADODB.Recordset
Dim xmlFile As String
xmlFile = GetDBPath_FX & "tasks.xml"

Now I encountered my first real dilemma which took hours of poring through help and online help because as I wasn’t using any existing data, I had no query or recordset upon which to establish my XML file. Then I came across the Append method of Fields collection in ADO. This allows you to actually establish the names, types and attributes of fields in a recordset without actually making a table anywhere. The arguments are field name, type and length. As you can see in the code snippet below, this is not unlike a create table SQL statement . One argument that was confusing was field length as the ADO help indicated this was not required for some field types such as date but the compiler still required it. Where I have entered a field length of zero is where the length is not actually relevant but it is needed.

With rstXML.Fields
.Append "DateCreated", adDate, 0
.Append "Subject", adChar, 150
.Append "Body", adLongVarChar, 5000
.Append "Category", adChar, 20
.Append "PercentComplete", adSingle, 0
.Append "Importance", adTinyInt, 0
.Append "SystemUsername", adChar, 50
End With
rstXML.Open

So now we return to the individual Outlook items (as seen in Listing 1) where instead of storing the variables from Outlook into an access field using

With rstTasks
    .AddNew
    !DateCreated = itDateCreated
    !Subject = itSubject

we instead use the following code for the XML recordset

With rstXML
    .AddNew
    !DateCreated = itDateCreated
    !Subject = itSubject

!Importance = itImportance
   !SystemUsername = userNameRequired
   .Update
End With

This is surprisingly exactly the same code for both writing to an Access table as the XML recordset. The only different thing is the XML update clause doesn’t actually store the information in any table as it is still a temporary process. This doesn’t happen until the recordset is closed and saved at the end as follows. The Kill statement is the way that you remove any existing XML file with the same name. The Save method of the recordset is what generates an XML file. Note that you require ADO version 2.5 to make an XML file using the adPersistXML constant.
' Now delete the xml file and write a new one

On Error Resume Next
Kill xmlFile
On Error GoTo 0
rstXML.Save xmlFile, adPersistXML
rstXML.Close
Set rstXML = Nothing

The header section (schema) of the XML that is generated looks a little like the following.   Note that the field types and sizes are stored in the header section.
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='DateCreated' rs:number='1' rs:write='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='Subject' rs:number='2' rs:write='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='150' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>

Now the data section of the XML is also of interest as it shows the information from the Outlook tasks. It also shows that the <rs:insert> tag is used which I suspect caused Access 2002 some issues later on.
<rs:data>
<rs:insert>
<z:row DateCreated='2002-03-06T15:51:47' Subject='Makes Lots of Money from Chocolate Company ‘                                                                                                           Body='' Category='Project 2           ' PercentComplete='0' Importance='2' SystemUsername='Garry '/> <z:row DateCreated='2002-03-06T15:52:20' Subject='Spend All Profits on Chocolate '
Body='This project will require maximum consumption of clients goods to test that the product sells well.' Category='Project 2           ' PercentComplete='0' Importance='0' SystemUsername='Unknown                                           '/> </rs:insert>
</rs:data>
</xml>

The XML Dead Ends In Access 2002/2000

One of the more lauded features of Access 2002 in the Microsoft documentation was its ability to import and export XML.   Well when I decided to import the XML file that I had generate with Microsoft ADO technology, it wasn’t interested in reading the file and it certainly offered no clues as to what was wrong with the XML file.   That really wasn’t a huge problem as the XML exercise was all about viewing the data in Access without making an internal table.

Next I started investigating the new feature that was added to Access 2000 where instead of using a recordsource (SQL or table) behind a form, you can use a recordset as follows

Set Me.Recordset   = xmlRst

Unfortunately this only works if your recordset uses a location property of adUseClient. This precludes the use of external XML recordsets to populate a form even in readonly mode.   Another more significant issue is that Access reports cannot have their own recordsets at all.

Unbound Recordset

Now as far as I could see, I was left with only one choice in my quest to import the XML into a table. That solution was to use ADO again and cycle through the XML file using an unbound form and filling the fields as each record was displayed. This process starts with the Form Open event in the form shown in figure 5.

set rstXMLForm = New ADODB.Recordset

With rstXMLForm
.Open “c\my documents\tasks.xml"
Call cmdFirst_Click
Me!NumRecords = .RecordCount

Figure 5 – An Unbound form is used to display the XML tasks one at a time.

Most of the navigation and display code is handled under the navigation buttons that I have added to the top of the form. The goto next record and goto last record buttons have the following code in the on click events

Private Sub cmdLast_Click()
On Error Resume Next
rstXMLForm.MoveLast
Call recordLocation
Call displayXML
End Sub

Private Sub cmdMoveNext_Click()
On Error Resume Next
rstXMLForm.MoveNext
Call recordLocation
Call displayXML
End Sub

Now all the display of the fields in XML recordset is handled by assuming that the fieldnames in the recordset are exactly the same as the unbound fields on the form. That way the same code can be easily transferred to another form and another XML recordset.

Private Sub displayXML()
Dim i As Integer
With rstXMLForm
For i = 0 To .Fields.Count - 1
    Me(.Fields(i).NAME) = Trim(.Fields(i).Value)
Next i
End With

End Sub

Finally there are 2 other noteable features of this form that are highlighted by the Filter toggle button (see figure 5). The click event will actually apply a filter to the XML recordset that is being used. This is really neat that you can actually filter test data in this way. Note that the filter is applied to the Importance field from the Outlook tasks which you will probably recognize as that priority tag of High (2), Normal (1) and Low (0) that you can attached to your emails. I also demonstrate the code necessary to capture the absolute location in your recordset to display on the form.

Private Sub tglFilterImp_Click()

' Filter the records to show high importance only

With rstXMLForm

If Me!tglFilterImp Then
    .Filter = "importance = 2"
Else
    .Filter = adFilterNone
End If
Call recordLocation
Me!NumRecords = rstXMLForm.RecordCount

End With
Call displayXML

End Sub

Private Sub recordLocation()
Dim recPosInt As Integer

recPosInt = rstXMLForm.AbsolutePosition
If recPosInt = adPosBOF Then
    recPosInt = 1
ElseIf recPosInt = adPosEOF Then
    recPosInt = Me!NumRecords
ElseIf recPosInt = adPosUnknown Then
    recPosInt = -99
End If
Me!recPos = recPosInt

End Sub

Summing Up

Hopefully this detailed article has shown you how relatively easy it is to import Outlook tasks into your database and use them in a meaningful way.   In addition to that we have demonstrated that integrating XML is going to be a tougher nut to crack than may be first indicated by the Microsoft Press. It seems that the best way to guarantee success with XML is to use the same technique for writing the software as you use for reading the software. In this case we resorted to ADO to generate the XML. That said, the XML file that was generated is now in a format that it can be easily sent anywhere in the world. This cannot always be said about Outlook and Access and other proprietary file formats.

Useful Further Reading and Resources

May I suggest that you use the Object Explorer to find out more about the things that you can retrieve from an Outlook task item. Use the toolbar button in VBA or the menu View ~ Object Explorer. Select Outlook as the library and search for TaskItem

Serious ADO written by Rob McDonald and published by APRESS.

http://www.microsoft.com/data/   is the place for your latest MDAC/ADO download

http://www.xmldevelopernewsletter.com/ is where you can find out more about XML.

Outlook programming examples
http://www.outlookexchange.com/articles/home/outlooklinks.asp#KnowledgeBase

Outlook task reminder
http://support.microsoft.com/kb/q162371/

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol11/html/olobjTaskItem_HV05247372.asp

Or this group discussion link

or http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=19694

Helen Feddema has lots of interesting Office Automation code http://www.helenfeddema.com/CodeSamples.htm

Smart Access magazine Articles Of Relevance
Valid and Well-Formed XML in the March 1999
Access Exchange - October 2000
Remote Queries - September 2001
Access to Automated Email - March, 1999

Useful Further Reading and Resources

Rick Dobson wrote a great article on macro security for Access 2003 in for Smart Access in April 2004

Information about Service Pack for Jet
http://support.microsoft.com/default.aspx?kbid=239114

Listings and discussions of unsafe VBA and Control Event Function
http://support.microsoft.com/default.aspx?kbid=294698

How to obtain the latest Jet Service pack
http://support.microsoft.com/default.aspx?kbid=239114

Information about the latest service pack
http://support.microsoft.com/?kbid=829558

Read about Microsoft Jet Expression Service sandbox mode
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010446591033

A page at my site covering things to be aware of when installing Access 2003
http://www.vb123.com/toolshed/03_access/install.htm

Find out how to extract the version of Jet your users are running (and log to a file)
Go to this page

Conclusion

When Access 2003 rolls along onto your client or companies computers, you are going to look like a bit of a turkey if the best thing that you can recommend to these users is that they turn off all macro protection so that your software works. It is much better to make sure that your database works well by testing for sandbox mode at all times on your pc. You should also endeavor to ensure that all your key testers upgrade to the latest version of Jet 4 and use the sandbox protection as well to improve the coverage. Finally, you can reduce the complication of the Access 2003 rollout by ensuring that as many people as possible have upgraded their version of Jet 4 before they upgrade Access.

A different slant on these issues is a marketing one. If you are an independent Access developer, the simplest approach to Macro security in Access 2003 would be to tell your clients to adopt the lowest level of protection. In these days of constant computer security issues, being weak on security is not a good long term strategy. Wouldn’t it be a great thing instead to say to your customers that you are recommending that they adopt Sandbox protection for all versions of Access 2000 upwards as their first stage of Access macro protection? To do this, you need to ensure that they are using Jet Service Pack and have the sandbox registry item set so that unsafe expressions are protected on their computer. That is the approach that my company has taken both for development and end users for the time being.

Downloads
Click here for the download file if you own "The Toolshed" Else click here

Author Bio.

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies popular web site at http://www.vb123.com/ or sign up for his Access email newsletter by sending a blank email to tips@vb123.com The web site features many Access resource sand software that are used by more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at … www.gr-fx.com

Other Pages On This Site You Might Like To Read

Processing Regnow E-Mail Orders using Outlook and Access
Stop Those Annoying Outlook Warning Messages
Processing E-Mail Orders using Outlook and Access
Personalised Email from Access and Word
Improving The Performance Of Outlook
Backing Up or Moving Microsoft Outlook
Backing Up Your Access Data With XML

This article first appeared in the July 2002 Edition of Smart Access. Reprinted with permission from Pinnacle Publishing (http://www.pinpub.com/).

and was written by Garry Robinson from GR-FX Pty Limited