Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook from Access (2000)

    Hi,
    I've asked a similar question before, so forgive me for those who previously posted.
    I need to duplicate the Outlook calendar in Access. The premise was that the people using the database could enter Meetings in a calendar and have pop-up reminders displayed on their screen when that date arises. But to also allow other people to view the calendar, when staff are absent, so that tasks do not remain unactioned.
    Originally I was advised to set up a public calendar in Outlook to do this. What has since transpired is that although this is possible, the pop-up function is not. Either to software limitations, or something else.
    I'm aware that the tasks can be carried across from the Public Calendar to peoples personal calendars, thus enabling the pop-up function for individuals. But I'm concerned that people will not use this function due to it being more involved that they would like.
    And the Access question...................I would like to avoid the daunting prospect of replicating the Outlook calendar. But would a simpler calendar type database, that exports the Meeting task to the personal Outlook Calendar be possible?
    I envisage a calendar, where the user can scroll through days/months etc, entering Tasks within specific times, and then having them exported to the Personal Outlook folder on exit. This way any user can see the tasks in the Access Calendar, but the pop-ups will be displayed through Outlook, on their own personal Calendars.
    Hope this makes sense.............
    Or maybe I should just bury this dead dog!

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

    Re: Outlook from Access (2000)

    See for example Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003 (works the same in Office 2000) for how to create an appointment from Access.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    Hi Hans,
    Thanks for this, had to send the link home, as they've restricted .exe files at work.
    This seems to be the thing I'm after. However the coding seems to have an error. I'm not too sure whether this is due to the fact that the system is networked, or that the system should be sending the appointments to the Network users account.....could be something totally different!
    <hr>Private Sub Command23_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
    <hr>

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

    Re: Outlook from Access (2000)

    It would be useful if you told us what error message you get. You have set a reference to the Microsoft Outlook 9.0 Object Library (in Tools | References...), haven't you? Otherwise, VBA won't recognise Outlook objects.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    Sorry, that might help!!
    The error is:
    Compile Error: User-defined type not defined.
    Highlighting the bold text in this code:
    '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

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

    Re: Outlook from Access (2000)

    In the Visual Basic Editor:
    - Select Tools | References...
    - Locate Microsoft Outlook 9.0 Object Library (the 9.0 is the internal version number for Office 2000) in the list.
    - Tick its check box.
    - Click OK.
    - Select Debug | Compile <projectname> to see if the error message has gone away.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    The Microsoft Outlook 9.0 Object Library, when selected displays this message:

    Name Conflicts with existing module, project, or object library.

    The references that I currently have at work are:
    Visual Basic for Applications
    Microsoft Access 9.0 Object Library
    OLE Automation
    Microsoft ActiveX Date Objects 2.1 Library.

    Hmmm, any ideas?

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

    Re: Outlook from Access (2000)

    Check the following:
    1. Do you have a module named Outlook in your database? If so, give it another name.
    2. If not, select Tools | <projectname> Properties... in the Visual Basic Editor. If the Project Name is Outlook, change it to something else.
    Then try setting the reference again.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    Yes, I had indeed named the database Outlook............but it works now.
    Would you mind advising me on whether I could set this up in a more user friendly way?
    I'd like the user to be able to select reminders up to 2 weeks previous to the meeting, as opposed to selecting minutes. I debated whether to do a table, and work out how many minutes in a day, 2 days, through to 2 weeks, still with the user being able to select minutes, hours (for the first day only) and then displaying the text in the combobox.
    5 minutes, 10 minutes, 15 minutes, 20 minutes, etc, 1hr 15 minutes, 1hr 30 minutes, etc.
    I'd also like to display a report type calendar, where the user can view what appointments are available for the current week, preferably with the ability to scroll by week.
    I also need to create a report that displays what appointments are for that day, which pops-up before the user enters the database........this, I think I'm okay with.
    Sorry for all the questions................maybe a new post would be more beneficial.

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

    Re: Outlook from Access (2000)

    For extended reminders, I would indeed create a table with the descriptive texts to be displayed in the combo box in one field, and number values, for example the number of minutes, in another field.

    See the recent thread starting at <post#=435603>post 435603</post#> for calendar reports.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    Wow, <post#=285719>post 285719</post#> is precisely the thing I'm looking for. I've created a from, frmAppointments, that has the control, with the coding to place the meeting into Outlook. If this could then be displayed into the Calendar at this post,rather than FrmCalEvent, this would be ideal.

    I've had a look at the coding within the calendar form:

    Function dateDblclick(id)
    <hr>Dim response As String
    Dim tmp As Date
    On Error GoTo ErrTrap


    If Me("id" & id).Caption = "" Then
    response = MsgBox("Event does not exist for this date. Create a new Event ?", vbYesNo, "Create new event")
    If response = vbYes Then
    DoCmd.OpenForm " frmCalEvent ", , , "[ID] = " & 0, , acDialog

    Form_Current
    tmp = strMonth & ". " & Me("label" & id).Caption & "/" & intYear
    Forms![frmCalEvent]![StartDate] = tmp
    'Forms![frmCalEvent]![StartDate] = strMonth & ". " & Me("label" & id).Caption & "/" & intYear
    Else
    End If
    Else
    DoCmd.OpenForm "frmCalEvent", , , "[ID] = " & Me("id" & id).Caption, , acDialog
    Form_Current
    End If

    ErrTrap:

    End Function<hr>

    I'm pretty sure that I need to change other things apart from the form name, like ID, etc. Problem is, I'm not too sure what.
    Currently my form table has these fields.

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

    Re: Outlook from Access (2000)

    The database from the post you mention only stores dates for appointments, not times, so you would need to modify it extensively to be able to use it in your database. The code "as is" passes the date of the appointment to the popup form, not the time, simnply because time is not available.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    I'm not too worried about the time, currently the calendar stores the Title within the date, I would change this to store the title, and probably the Network user name. Currently when you click on the date it displays the entry in frmCalEvent, this again would be ideal.
    Or should I approach this from a different angle, stick to the reports?

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

    Re: Outlook from Access (2000)

    If you want to go on with this, you'd have to study the way the forms and table in the demo database interact, then translate it to your situation. The role of the ID and StartDate fields in the table in the demo database are played by ApptID and ApptDate in your table.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook from Access (2000)

    Before I go ahead and alter the structure of TblCalendar................
    I'm guessing that if I want to fill the Calendar with the information from TblAppointments, it should be done with the following?

    TblCalendar TblAppointments

    ID................................ApptID
    StartDate.................ApptDate
    EndDate..................ApptTime
    Title.........................Appt
    Comments.............ApptNotes

    I'd also like to add NetUser name to this.

    I've re-jigged the data in the coding so that FrmAppointments opens, and works. Its just backfilling this data into the calendar. Will this be affected if multiple meetings are assigned?

    I'm aware that if I alter the Table Structure in TblCalendar, that this will then need to be reflected in the coding. But presumably this needs to be done for it to correctly work. I presume I need to update the relevant references in the Private Sub Form_Current() in FrmCalendar, where ID, will now be ApptID, and so on, reflecting the change as above.

Page 1 of 2 12 LastLast

Posting Permissions

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