I wanted to share some code to allow Access to fetch a task list from Outlook, even if the tasks contain user-defined fields. The built-in export command in outlook works only on the standard fields and will not export user-defined fields. I use the add-in and it has user-defined fields.
I use this code to make a printed report for my weekly review. The report shows the full body of each task's Notes section, something I was unable to do within outlook. I find it much quicker to do my weekly review on paper.
To use my sample code, first make an empty Access database with the following fields:
[name],[type],[length]
Project, text, 255
Subproject, text, 255
Subject, text, 255
Action, text, 50
Notes, memo
GtdOrder, number
Complete, Yes/No
DueDate, Date/Time
If you want different fields, modify the database and the code appropriately.
Copy-Paste the code from this message to a new VB module in Access. Run the module and it will fetch the data from outlook and populate the Access table.
I made a query and report to print out a sorted task list just the way I like it. I can't think of an easy way to post the query and report definitions here, so you'll have to make your own.
On my system, the module fires off that annoying outlook security warning and I have to click to allow the module to access my data for 1 minute.
Warning: the code is a bit ugly, no error-checking, no warranties, batteries not included, professional driver on closed course, individual results vary, etc.
I based the code on an example I found on Microsoft's website at http://support.microsoft.com/?kbid=290792 titled "How to programmatically export Outlook items to Access." I modified it for tasks instead of contacts, and added the add-ins custom fields.
[--------begin code----------]
Sub ImportTasksFromOutlook()
' This code is based in Microsoft Access.
' Code adapted by Ken Glade June 14, 2006
' Set up DAO objects (uses existing "tblTasks" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblTasks")
' Set up Outlook objects.
Dim ol As New Outlook.Application
Dim olns As Outlook.Namespace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.TaskItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderTasks)
Set objItems = cf.Items
iNumContacts = objItems.Count
If iNumContacts 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "TaskItem" Then
Set c = objItems(i)
rst.AddNew
rst!Subject = c.Subject
rst!Notes = c.Body
rst!DueDate = c.DueDate
rst!Complete = c.Complete
' Custom Outlook properties would look like this:
' rst!AccessFieldName = c.UserProperties("OutlookPropertyName")
rst!Project = c.UserProperties("Project")
rst!SubProject = c.UserProperties("Subproject")
rst!Action = c.UserProperties("Action")
rst!GtdOrder = c.UserProperties("GtdOrder")
rst.Update
End If
Next i
rst.Close
MsgBox "Finished."
Else
MsgBox "No contacts to export."
End If
End Sub
[--------end code----------]
I use this code to make a printed report for my weekly review. The report shows the full body of each task's Notes section, something I was unable to do within outlook. I find it much quicker to do my weekly review on paper.
To use my sample code, first make an empty Access database with the following fields:
[name],[type],[length]
Project, text, 255
Subproject, text, 255
Subject, text, 255
Action, text, 50
Notes, memo
GtdOrder, number
Complete, Yes/No
DueDate, Date/Time
If you want different fields, modify the database and the code appropriately.
Copy-Paste the code from this message to a new VB module in Access. Run the module and it will fetch the data from outlook and populate the Access table.
I made a query and report to print out a sorted task list just the way I like it. I can't think of an easy way to post the query and report definitions here, so you'll have to make your own.
On my system, the module fires off that annoying outlook security warning and I have to click to allow the module to access my data for 1 minute.
Warning: the code is a bit ugly, no error-checking, no warranties, batteries not included, professional driver on closed course, individual results vary, etc.
I based the code on an example I found on Microsoft's website at http://support.microsoft.com/?kbid=290792 titled "How to programmatically export Outlook items to Access." I modified it for tasks instead of contacts, and added the add-ins custom fields.
[--------begin code----------]
Sub ImportTasksFromOutlook()
' This code is based in Microsoft Access.
' Code adapted by Ken Glade June 14, 2006
' Set up DAO objects (uses existing "tblTasks" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblTasks")
' Set up Outlook objects.
Dim ol As New Outlook.Application
Dim olns As Outlook.Namespace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.TaskItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderTasks)
Set objItems = cf.Items
iNumContacts = objItems.Count
If iNumContacts 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "TaskItem" Then
Set c = objItems(i)
rst.AddNew
rst!Subject = c.Subject
rst!Notes = c.Body
rst!DueDate = c.DueDate
rst!Complete = c.Complete
' Custom Outlook properties would look like this:
' rst!AccessFieldName = c.UserProperties("OutlookPropertyName")
rst!Project = c.UserProperties("Project")
rst!SubProject = c.UserProperties("Subproject")
rst!Action = c.UserProperties("Action")
rst!GtdOrder = c.UserProperties("GtdOrder")
rst.Update
End If
Next i
rst.Close
MsgBox "Finished."
Else
MsgBox "No contacts to export."
End If
End Sub
[--------end code----------]