• If you are new to these Forums, please take a moment to register using the fields above.


No announcement yet.

Export Tasks with custom fields from Outlook to MSAccess via VB

  • Filter
  • Time
  • Show
Clear All
new posts

  • Export Tasks with custom fields from Outlook to MSAccess via VB

    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:

    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 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!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")
    End If
    Next i
    MsgBox "Finished."
    MsgBox "No contacts to export."
    End If

    End Sub

    [--------end code----------]

  • #2
    Thanks for the code!

    Do you happen to know how to do the reverse - export tasks from Access to Outlook?

    I started using Access as my GTD tool a few months ago and I like it a lot. In addition to exporting to Outlook I am also thinking about a way to export directly to a website via FTP. Then I can view my task lists online with out having to manually upload them.


    • #3
      You can get tasks from Access to Outlook by Importing them into Outllook. From the menus, File, Import and Export, Import from another program or file...


      • #4
        Not exacly what I had in mind. The plan is to put a button on a form in Access that says "Export Tasks to Outlook." Another button will say "Upload Task Lists to Website."

        I'll search around the MS site and see what I can find.



        • #5
          Thank you - I've been trying to look at programming Outlook for some time now and this is great!