This is how to Create an Outlook Task from Access (Access97 or 2K)

Topic

New Reply

The below code can be placed into the “OnClick” Event of a generic button on any form. Each of the inserted variables can be exchanged for a control value of equal type in any manner supported by VBA/SQL. Make sure to create a Reference to Microsoft Outlook 9.0 Object Library (MS Access 2000), or Microsoft Outlook 8.0 Object Library (MS Access 97).

Tim Bankerd
Custom Designed Database Applications
wwg@fdn.com
The WorldWide Group of Online Services

—-Working Code Start—-

Private Sub Set_Up_A_Task_Click()
‘This will start an instance of Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject(“Outlook.Application”)
‘The following Dim’s are to set Outlook variables
Dim otTask As TaskItem
Dim ofFolder As MAPIFolder
Dim onNamespace As NameSpace
Dim ofInbox As MAPIFolder
Dim oicItems As Items
Dim omMail As MailItem
Dim orpRecurrence As RecurrencePattern

‘This block creates a new Task Record in Outlook
Set onNamespace = GetNamespace(“MAPI”)
Set ofInbox = onNamespace.GetDefaultFolder(olFolderInbox)
Set ofFolder = onNamespace.GetDefaultFolder(olFolderTasks)
Set otTask = ofFolder.Items.Add

‘This is the Subject Line of the Task
otTask.Subject = “This is the Task Subject Line”

‘This will create the entire body of the Task. Make sure to use SQL formatting for all of the Syntax
otTask.Body = “This should be the task itself.” & “This should be the second line of the Task.”

otTask.ReminderSet = True
otTask.ReminderTime = Date & ” ” & Format(Time + 1 / 24, “hh:mm:ss”)
otTask.ReminderOverrideDefault = True
otTask.ReminderSoundFile = “F:MSOffice2000OfficeReminder.wav”
otTask.ReminderPlaySound = True
otTask.Sensitivity = olConfidential
otTask.Assign
otTask.Recipients.Add “Put Email Name Here for each person in the task requirements”
otTask.Recipients.ResolveAll

‘This sets up the recurrance pattern of the Task. In this case
‘it will play the “Reminder.wav” file and present a reminder each day for 14 days.
Set orpRecurrence = otTask.GetRecurrencePattern
orpRecurrence.PatternStartDate = Date + 1
orpRecurrence.PatternEndDate = Date + 14
orpRecurrence.RecurrenceType = olRecursDaily

‘This sends the Task to Outlook
otTask.Send

End Sub

—-Working Code End—-

Reply | Quote

Replies

Thanks…
How can I then create an Outlook Calendar Event from Access with a Accept/Tentative/Decline that will add it to the users Outlook?

Reply | Quote

See Automating Microsoft Outlook 98 (the code will work in later versions too). Scroll down to “Example: Scheduling Meetings”.

Reply | Quote

HansV, I’m trying to use this code from the site provided in this post to create task items in outlook with the variable of the task sheld in a table or query.

Public Function taskme()
Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim itmTask As Outlook.TaskItem
Set ns = ol.GetNamespace(“MAPI”)
‘Create a new Task item.
Set itmTask = ol.CreateItem(olTaskItem)
‘Set some properties of the Task item.
With itmTask
.Subject = “Write article”
.DueDate = “8/22/97”
.Importance = olImportanceHigh
.Save
End With
End Function
I’m trying to figure out how to get it to loop through the records of the table/query until the last record then stop. Any help would be appreciated.

Reply | Quote

Which version of Access are you using?

Reply | Quote

Sorry HansV, 2003 the code I provided does work to create and save a task, can’t make it loop off query/table records though.

Reply | Quote

You’d do something like this:

Public Function taskme()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim itmTask As Outlook.TaskItem

On Error GoTo ErrHandler

Set ns = ol.GetNamespace(“MAPI”)
Set cnn = CurrentProject.Connection
rst.Open “tblSomething”, cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable
Do While Not rst.EOF
‘Create a new Task item.
Set itmTask = ol.CreateItem(olTaskItem)
‘Set some properties of the Task item.
With itmTask
.Subject = rst!Subject
.DueDate = rst!DueDate
.Importance = rst!Importance
.Save
End With
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Reply | Quote

Works great, thanks HansV

Reply | Quote

Any suggestions on how to create the task wihin a public folder shared across a network. Every user will, by default already have access to this public folder so login wont be an issue.

Reply | Quote

You can create an item in another folder than the default one for its type by using the Add method of the Items collection of the folder. Example:

Dim fld1 As Outlook.MAPIFolder
Dim fld2 As Outlook.MAPIFolder
Dim fld3 As Outlook.MAPIFolder

Set fld1 = ns.Folders(“Public Folders”)
Set fld2 = fld1.Folders(“All Public Folders”)
Set fld3 = fld2.Folders(“My Public Folder”)
Set itmTask = fld3.Items.Add(olTaskItem)

ns and itmTask are defined as in the code higher up in this thread

Reply | Quote

I’m getting the error;

“Object Variable or WITH Block not set”

I’m really out of my element on this one and I only have so much time to show that it can be done. I included the code i’m using, which is a derivitive of code provided by HansV last year. Any additional help would be appreciated.

Thanks much

Reply | Quote

Can you tell us which line causes this error?

Reply | Quote

It occurs on this line

Set fld1 = ns.Folders(“Public Folders”)

Reply | Quote

That is because you have placed this (and the following) lines above the line that sets ns:

Set ns = ol.GetNamespace(“MAPI”)

You cannot use an object variable before you have set it. The new code that creates a task item belongs inside the Do While Not rst.EOF … Loop, and replaces the original line

Set itmTask = ol.CreateItem(olTaskItem)

Reply | Quote

Thanks HansV, for your patience with the ignorant, works great.

Reply | Quote