Solved: Using Outlook calendar for Scheduling

[Movian]

Hi,
i have been trying to setup a good way to use the outlook calendar to organize scheduling for a medical clinic from our Access database.

currently the system will be able to add an apointment. However when the apointment window comes up you can't do much with it. It seems that by opening that apointment window in code you lock most other outlook functions. So you cant look at the calendar to find a good spot to schedule.

Here is what i have at the moment

~edit~ realized this sub was opening a dao table connection when it didn't need to so have removed it

Private Sub Command0_Click()
CreateApointment Me.Text4.Value & ", " & Me.Text3.Value & " - " & Me.Procedure, "Home:" & Me.Text10.Value & " - Cell: " _
& Me.Cell & " - DayTime: " & Me.DayTime, "Medical ID" & vbNewLine & Me.Text1.Value & vbNewLine & vbNewLine & "Address" _
& vbNewLine & Me.Text6.Value & vbNewLine & Me.Text7.Value & ", " & Me.Text8.Value & " " & Me.Text9.Value & vbNewLine _
& vbNewLine & "Comment:" & vbNewLine & Me.Text32
End Sub


Public Sub CreateApointment(subj As String, Location As String, Body As String)
Dim olapp As Object ' Outlook.Application
Dim olapt As Object ' olAppointmentItem
Dim flag As Boolean
flag = False

On Error GoTo makenew:
Set olapp = GetObject(, "Outlook.Application")
On Error Resume Next


If flag = False Then
Set olapt = olapp.CreateItem(1) ' olAppointmentItem = 1
With olapt
.Start = Date + 1 + TimeValue("19:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = subj
.Location = Location
.Body = Body
.ReminderMinutesBeforeStart = 120
.ReminderSet = True
.Display True
End With
End If

makenew:
If Err.Number = 429 Then
Set olapp = CreateObject("Outlook.Application")
Set olapt = olapp.CreateItem(1) ' olAppointmentItem = 1

With olapt
.Start = Date + 1 + TimeValue("19:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = subj
.Location = Location
.Body = Body
.ReminderMinutesBeforeStart = 120
.ReminderSet = True
.Display True
End With
flag = True
End If
Resume Next
End Sub
What i would ideally like to do is have it so when i click the button (first section of code) instead of bringing up a new apointment window. It would instead bring up the users calendar view then when they double click in a calendar (our clients typically want to use multiple calendars) when it brings up that apointment window from the double click it would THEN populate that window with the information.

I am unsure if this is possible and if it is i have NO idea how to go about it. I look around for months for information on seting up a calendar system in access and everything i found wasn't realy what we were after. Thats why we decided to try and use the oulook calendar as it apears to offer everything we need. However while automation with most other office products (word, exell) seem easy and straight forward. it looks like the outlook automation was designed by a blind monkey :\

no offence if anyone here was involved in that : pray2:

But as always any help is apriciated!

[OBP]

Movian, I can't help with Outlook as I have the same type of VB code as you, it only makes appointments.
If you can't get it to do what you want I have a Simple Appointments system that is simple to use with 15 or 30 minute time slots that uses the ActiveX Calendar for date selection.

[OBP]

Movian, I have found quite a bit of VBA code to manipulate Outlook directly, but not how to open and "View" it.

[Movian]

Ok well how about adding appointment information to an OPEN appointment window ?

if i approach it from that method i also need a way to open outlook into the calendar view. (on review i can just tell our clients to just have outlook open already and they can switch between)


This way i can have two buttons one to open the calendar view so the user can find the slot they want to use. Double click it to open the apointment window. Go back to our system and it inputs the data for the apointment.

There a decent way to go about that? if not i would need to find a way to copy all the required information to the clipboard so they can just paste it into the appointment window.

The problem being when i add an appointment directly from code there is no way to view the calendar to find a good time/date.

OBP i apriciate the offer of the scheduler in Access but im presuming it doesn't have an outlook calendar/google calendar style interface which is what our clients really need (not the sharpest tools in the shed :( )

I looked around the web for a good 2 weeks to find an in access solution but it looks like the fact that you can't create controls on the fly essentially stops any decent interface functionality :\

so im stuck interfacing with Outlook... atleast for now :)

[Movian]

OK, took a little longer than i was hoping but this is the solution i have ended up with. This allows the user to locate their own spot. Double click the spot to activate the appointment window, go to our system and hit a button and it places all of the required appointment information into the appointment window. Then make that window active so the user can edit and save.

Public Sub CreateApointment(subj As String, location As String, Body As String)
Dim olapp As Object ' Outlook.Application
Dim olapt As Object ' olAppointment

'Should generate an error if outlook is not open and or if there is no active item (EG appointment window)
On Error GoTo NotOpen:
Set olapp = GetObject(, "Outlook.Application")
Set olapt = olapp.Application.ActiveInspector.CurrentItem
On Error GoTo 0

If olapt.Class = olAppointment Then
'checking if the active item is an apointment window & if it is procede to enter client information
With olapt
.Subject = subj
.location = location
.Body = Body
.ReminderMinutesBeforeStart = 120
.ReminderSet = True
.Display True
End With
Else
'window does not appear to be an appointment window.
MsgBox "Apointment window does not apear to be the last outlook window opened. Please select your apointment slot and try again", vbCritical, "No Apointment Window"
End If
Set olapt = Nothing
Set olapp = Nothing

NotOpen:
'if there is no error then resume, however if there is an error then presume its due to Outlook or appointment window being not accessable.
If Not Err.Number = 0 Then
MsgBox "No Apointment Window open. Please select your apointment slot and try again", vbCritical, "No Apointment Window"
Exit Sub
End If
End Sub

[OBP]

Movian, that is good.
My system is very similar to Outlook to look at, but currently doesn't do recurring appointments, but easily could.
It has Time slots like outlook and a Calendar for date selection, but it's big advantage is that it knows who the user of the database is (secured database or login required) so it is "their" appointment book, plus it has a combo for selecting "Customers/Clients" from the database.
I have it as a Stand alone Form or as a Tabbed Form where the user does their "Business". It also can be configured to make appoinments for other users using an "employee" combo to select them first.

[Movian]

hmm, is there any chance i could take a look over your solution ?

it sounds what i might have been looking for origionally. I would need to alter it so that certain users are able to view other users calendars. Also where does it get the user information ? as in my database the user accounts are all done in code and do not have anything to do with the built in access security

[OBP]

Movian, I usually use it in conjunction with Access Security, or if someone has used a login form with that.
Can you get your coded User Accounts and derive their ID from that?
I have attached the basic version of the database from about a year ago, it uses a Dates table and a Timeslot Table (15 minute slots) to generate a Date & Times table in advance.
It uses Combos to select the User and Customer.
I am sure you can improve it and make it do what you want by adding a notes field etc.
I have removed most of the old Dates to make it small enough to attach.