Results 1 to 9 of 9
  1. #1
    IdleJack is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Location
    Leeds, West Yorkshire England
    Posts
    15

    Access appointments to Outlook

    I'm creating a dbase that (if all goes well) by the click of a button appointments created in access 2007 will be pushed into outlook 2007.

    I found the code below and have created a dbase that should achieve what I want; however I'm having a few problems.....

    I got the code from this site: http://support.microsoft.com/kb/209963 and I followed the directions as per the web page.

    When I click the 'Send To Outlook' button I get the following error:

    User-defined type not defined

    And the code below, in red, is the section of code that is highlightedL

    Private Sub cmdAddAppt_Click()
    On Error GoTo Add_Err

    'Save record first to be sure required fields are filled.
    DoCmd.RunCommand acCmdSaveRecord



    'Exit the procedure if appointment has been added to Outlook.
    If Me!AddedToOutlook = True Then
    MsgBox "This appointment is already added to Microsoft Outlook"
    Exit Sub
    'Add a new appointment.
    Else
    Dim objOutlook As Outlook.Application
    Dim objAppt As Outlook.AppointmentItem
    Dim objRecurPattern As Outlook.RecurrencePattern

    Set objOutlook = CreateObject("Outlook.Application")
    Set objAppt = objOutlook.CreateItem(olAppointmentItem)

    With objAppt
    .Start = Me!ApptDate & " " & Me!ApptTime
    .Duration = Me!ApptLength
    .Subject = Me!Appt

    If Not IsNull(Me!ApptNotes) Then .Body = Me!ApptNotes
    If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
    If Me!ApptReminder Then
    .ReminderMinutesBeforeStart = Me!ReminderMinutes
    .ReminderSet = True
    End If

    Set objRecurPattern = .GetRecurrencePattern

    With objRecurPattern
    .RecurrenceType = olRecursWeekly
    .Interval = 1
    'Once per week
    .PatternStartDate = #7/9/2003#
    'You could get these values
    'from new text boxes on the form.
    .PatternEndDate = #7/23/2003#
    End With

    .Save
    .Close (olSave)
    End With
    'Release the AppointmentItem object variable.
    Set objAppt = Nothing
    End If

    'Release the Outlook object variable.
    Set objOutlook = Nothing

    'Set the AddedToOutlook flag, save the record, display a message.
    Me!AddedToOutlook = True
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Appointment Added!"

    Exit Sub

    Add_Err:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Exit Sub
    End Sub


    Any help in correcting the code is really appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's the error you would get if you missed steps 8 & 9. Did you?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    IdleJack is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Location
    Leeds, West Yorkshire England
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    That's the error you would get if you missed steps 8 & 9. Did you?
    I followed all the steps as mentioned and I have now got it to work by moving the Microsoft Outlook 9.0 Object Library checked box to the top of the priority list.

    But (and yes I have but face!) when I create an appointment using the form it migrates it into Outlook the appointment is created approx 7 years in the past.

    For example I created on appointment dated the 01-Oct-11 and the appointment created was scheduled for Sat 19/07/2003???

    Is this because the Microsoft Outlook Library is 11 and not 9?

    If yes please advise how I change the coding to work for both MS Outlook Library 11 and 12??

    Thanks

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The most common way to make the code work with varying versions of Outlook is called "late binding":

    http://support.microsoft.com/default...;en-us;Q245115

    I'd need to see your specific code to debug why the date is wrong. The code you posted includes some hard-coded dates in 2003.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    IdleJack is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Location
    Leeds, West Yorkshire England
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    The most common way to make the code work with varying versions of Outlook is called "late binding":

    http://support.microsoft.com/default...;en-us;Q245115

    I'd need to see your specific code to debug why the date is wrong. The code you posted includes some hard-coded dates in 2003.
    thanks for the post I thought there may have been some hard coding for '03.

    The code I used was as per the original post/link above.

    What do I need to change for MS Access/outlook 2007?

  6. #6
    IdleJack is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Location
    Leeds, West Yorkshire England
    Posts
    15
    Sorted! Appointments are now been sent from access to outlook on the correct date/time!

    The problem was in the reoccurance pattern of the original piece of code I used. I just set the pattern start/end date to reference the ApptDate field and hey voila!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ludingtons is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    1
    Hi
    I am trying to create a dbase in Access 2016 that will push appointments to Outlook 2016. I am running into the problem that the article explaining how to do so http://support.microsoft.com/kb/209963 is written for Access 2000 and is not updated. I am stuck on steps 8 & 9. I can't find the Tools menu where I can click "References" and choose the Microsoft Outlook 9.0 Object Library check box. Can anyone help me by translating these steps for a total rookie who is using Access 2016 for the first time?


  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The tools menu is in the VBA editor, Alt-F11 from the main Access window.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automating Outlook from Access
    By RMittelman in forum Programming
    Replies: 11
    Last Post: 10-05-2021, 09:57 PM
  2. Linking Access and Outlook
    By SamanthaSteClaire in forum Access
    Replies: 1
    Last Post: 07-19-2011, 02:32 PM
  3. Calculating Max appointments
    By ET123 in forum Access
    Replies: 2
    Last Post: 07-24-2010, 07:32 AM
  4. Outlook to Access
    By Aubreylc in forum Import/Export Data
    Replies: 1
    Last Post: 01-25-2010, 09:37 AM
  5. MS-Access into Outlook - possible?
    By techexpressinc in forum Access
    Replies: 0
    Last Post: 01-09-2009, 01:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums