Results 1 to 15 of 15
  1. #1
    Tim Bankerd
    Guest

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

    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----

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    There is always a way.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  4. #4
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Which version of Access are you using?

  6. #6
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Works great, thanks HansV

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  9. #9
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  11. #11
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Can you tell us which line causes this error?

  13. #13
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    It occurs on this line

    Set fld1 = ns.Folders("Public Folders")

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)

  15. #15
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •