Working with Outlook Tasks

As with appointments, if you have an Access table of tasks created many Office versions ago, I recommend exporting the task data to Outlook, so it can be maintained in the Task List (in Outlook 2007, renamed the To Do List) for future use.

The table that I imported from the sample Tasks database (tblTasks) to Outlook tasks can be used as an example of how to export Access task data to Outlook. The following function does the export (it can also be run from the mcrExportTasksToOutlook macro):

Public Function ExportTasksToOutlook()

On Error GoTo ErrorHandler

Dim fldTasks As Outlook.Folder Dim tsk As Outlook.TaskItem Dim strTaskName As String Dim dteStartDate As Date Dim dteDueDate As Date Dim strStatus As String Dim lngStatus As Long

Dim strPriority As String Dim lngPriority As Long Dim strDescription As String

Set appOutlook = GetObject(, "Outlook.Application") Set nms = appOutlook.GetNamespace("MAPI") Set fldTasks = nms.GetDefaultFolder(olFolderTasks) Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblTasks")

With rst

Do While Not .EOF

Check that there is a task subject.

strTaskName = Nz(![Title]) Debug.Print "Task: " & strTaskName If strTaskName = "" Then

GoTo NextTask End If

Check for valid dates, and convert blank dates into 1/1/4501 (that is a blank date in Outlook).

If IsNull(![Start Date]) = True Then dteStartDate = #1/1/4501# Else dteStartDate = Nz(![Start Date]) End If

If IsNull(![Due Date]) = True Then dteDueDate = #1/1/4501# Else dteDueDate = Nz(![Due Date]) End If

Convert the text Status value to a number for Outlook.

lngStatus = Switch(strStatus = "Not started", _ 0, strStatus = "In progress", 1, _ strStatus = "Completed", 2, _ strStatus = "Waiting on someone else", 3, _ strStatus = "Deferred", 4, _ "", 0)

Convert the text Priority value to a number for Outlook.

strPriority = Nz(![Priority])

lngPriority = Switch(strPriority = "(1) High", 1, strPriority = "(2) Normal", 2, _ strPriority = "(3) Low", 3, _ "", 0)

strDescription = Nz(![Description])

Create a new task item in the selected Tasks folder.

Set tsk = fldTasks.Items.Add tsk.Subject = strTaskName tsk.StartDate = dteStartDate tsk.DueDate = dteDueDate tsk.Status = lngStatus tsk.Body = strDescription tsk.PercentComplete = Nz(![% Complete]) tsk.Close (olSave)

NextTask:

.MoveNext Loop End With

MsgBox "Tasks exported to Outlook"

ErrorHandlerExit: Exit Function

ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 429 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Figure 8.24 shows the exported tasks in the Tasks folder (in Outlook 2007, this folder is now called the To Do List).

FIGURE 8.24

Tasks in the Outlook To Do List exported from an Access table.

FIGURE 8.24

To import tasks from your local Outlook Tasks folder into an Access table (tblImportedTasks), use the following function (it can also be run from the mcrImportTasksFromOutlook macro):

Public Function ImportTasksFromOutlook On Error GoTo ErrorHandler

Dim fldTasks As Outlook.Folder Dim tsk As Outlook.Taskltem Dim strTaskName As String Dim dteStartDate As Date Dim dteDueDate As Date Dim strStatus As String Dim lngStatus As Long Dim strPriority As String Dim lngPriority As Long Dim strDescription As String Dim lngPercentComplete As Long Dim itm As Object Dim strSQL As String

Set appOutlook = GetObject(, "Outlook.Application")

Set nms = appOutlook.GetNamespace("MAPI")

Set fldTasks = nms.GetDefaultFolder(olFolderTasks)

Clear table of old data.

strSQL = "DELETE * FROM tblImportedTasks" DoCmd.SetWarnings False DoCmd.RunSQL strSQL

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblImportedTasks") Iterate through tasks in the Tasks folder and import them to the Access table.

For Each itm In fldTasks.Items If itm.Class = olTask Then Set tsk = itm

With tsk strTaskName = Nz(.Subject) dteStartDate = Nz(.StartDate) dteDueDate = Nz(.DueDate) lngStatus = Nz(.Status) lngPriority = Nz(.Importance) strDescription = Nz(.Body) lngPercentComplete = Nz(.PercentComplete) End With

With rst rst.AddNew

![Subject] = strTaskName

If dteStartDate <> #1/1/4501# Then

![Start Date] = dteStartDate End If

![Due Date] = dteDueDate End If

'Convert Priority number to text for Access strPriority = Switch(lngPriority = 1, _ "(1) High", _

lngPriority = 2, "(2) Normal", _ lngPriority = 3, "(3) Low", _ 0, "")

![Priority] = strPriority Convert the Status numeric value to text for Access.

strStatus = Switch(lngStatus = 0, _ "Not started", _

lngStatus = 1, "In progress", _ lngStatus = 2, "Completed", _ lngStatus = 3, _ "Waiting on someone else", _ lngStatus = 4, "Deferred", _ 0, "") ![Status] = strStatus

If lngPercentComplete > 0 Then lngPercentComplete = _

lngPercentComplete / 100

End If

![PercentComplete] = lngPercentComplete ![Notes] = strDescription

.Update End With

End If Next itm rst.Close

DoCmd.OpenTable "tbllmportedTasks"

ErrorHandlerExit: Exit Function

ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 42 9 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Figure 8.25 shows the table of imported tasks.

FIGURE 8.25

A table of tasks imported from Outlook.

FIGURE 8.25

A table of tasks imported from Outlook.

For a more realistic scenario, in which you want to create tasks based on data in an Outlook table, see the CreateProjectTasks function (it can be run from the mcrCreateProjectTasks macro). This function creates an Outlook task for each record in tblContactsWithProjects that has not had its supplies replenished for a month or more, and writes data from several fields in the Access record to the task item:

Public Function CreateProjectTasks()

On Error GoTo ErrorHandler

Dim fldTasks As Outlook.Folder Dim tsk As Outlook.Taskltem Dim dteMonthAgo As Date Dim dteReplenished As Date Dim dteNextMonday As Date Dim strProject As String

Set appOutlook = GetObject(, "Outlook.Application") Set nms = appOutlook.GetNamespace("MAPI") Set fldTasks = nms.GetDefaultFolder(olFolderTasks) Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblContactsWithProjects") dteMonthAgo = DateAdd("m", -1, Date) dteNextMonday = NextMonday()

With rst

Do While Not .EOF

Check whether supplies were last replenished more than a month ago.

dteReplenished = Nz(![SuppliesReplenished]) strProject = Nz(![CurrentProject])

If dteReplenished < dteMonthAgo Then

Create a new task in the local Tasks folder.

Set tsk = fldTasks.Items.Add tsk.Subject = "Replenish supplies for " _

& strProject tsk.StartDate = dteNextMonday tsk.Status = 0 tsk.Importance = 1 tsk.Close (olSave) End If .MoveNext Loop End With

MsgBox "Outlook project tasks created"

ErrorHandlerExit: Exit Function

ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 42 9 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Figure 8.26 shows one of the tasks created by this procedure.

FIGURE 8.26

A task created from data in an Access table.

FIGURE 8.26

A task created from data in an Access table.

Was this article helpful?

0 0

Post a comment