Create Outlook appointment

[NISMOJim]

Hello All,

This is my first time trying to control Outlook through Access VBA, and I have no idea what I'm doing. I have created a database for users to reserve building space, and I would like the command button on the form to enter their scheduled usage time on an Outlook calendar.

So far, I have created a new calendar (ATCG8S1) in Outlook, which shows up just below my personal calendar (LoveJ), under My Calendars. When everything is up & running with the database, my plan is to send a share invite for the calendar to my boss. He will be the person entering all of the usage times through the database instead of me. I don't know if this is the best way to make this happen, but being unfamiliar with Outlook calendars, it's all I've been able to come up with. Any other suggestions are welcome.

The Access form I'm starting with contains text boxes for the user to enter; txtUser, txtGrp, txtStartDate, txtEndDate. It also has txtStartTime and txtEndTime, but these boxes default to 0:00:01 AM & 11:59:59 PM respectively, as all usage will be an all day event. These last two textboxes will not be visible on the form.

I found the following code on one of the forums, and changed the names to fit my needs (I think)...

Private Sub cmdReserve_Click()
Dim oApp As Object
Dim oCalendar As Outlook.MAPIFolder
Dim oNameSpace As Outlook.NameSpace
Dim oAppt As Outlook.AppointmentItem
Dim oAcct As Outlook.Recipient
Dim ApptNotes As String
Dim strName As String
Dim calName As String

On Error Resume Next

calName = "ATCG8S1"

Set oApp = CreateObject("Outlook.Application.16")
Set oNameSpace = oApp.GetNamespace("Mapi")
Set oAcct = oNameSpace.CreateRecipient(calName)
Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)
Set oAppt = oCalendar.Items.Add(olAppointmentItem)

'Save Appointment

With oAppt

.Subject = Me!txtUser & " " & Me!txtGrp
.Start = Format(Me.txtStartDate, "Short Date") &" " & Format(Me.txtStartTime, "Short Time")
.End = Format(Me.txtEndDate, "Short Date") & " " & Format(Me.txtEndTime, "Short Time")
.ReminderSet = False
.AllDayEvent = AllDay
.Save
.Close (olSave)

End With

MsgBox "Appointment Added!", vbInformation

'Release the Outlook object variable


Set oCalendar = Nothing
Set oNameSpace = Nothing
Set oApp = Nothing

Err_Exit:

Set oCalendar = Nothing
Set oNameSpace = Nothing
Set oApp = Nothing

Exit Sub

End Sub

Having no idea what any of the code means, I don't know to get this to work. The appointment does not get added to the calendar, but I am not getting any error messages. The only thing that does work is the popup message box at the end that says "Appointment Added"

As always, any help is appreciated.

[pbaldy]

For starters, delete or comment out this line, which is bad practice and hiding any error you may be getting:

On Error Resume Next

or change it to the normal:

On Error GoTo Err_Exit

Here's more on error handling:

http://www.baldyweb.com/ErrorTrap.htm

[pbaldy]

Actually just delete it for now, or change to the method in the link. At this point you want to see every error.

[NISMOJim]

Thanks for the quick reply. I commented out the error handling & got a Run-time error'-2147352567 (80020009) Outlook does not recognize one or more names.

Here is the line that hangs up

Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

[Micron]

I admit, no experience getting Access to populate Outlook so this might be a dumb question, but wouldn't it be easier to use Outlook directly, and if you needed that data in tables, pull it in from Access? You can set up rooms as resources and invite them to meetings (thus book them) so why try to do it from Access? There are also Access based appointment calendars already out there.

[NISMOJim]

When this project is done, there will be around 16 different spaces that can be booked, thus 16 different calendars. The boss liked the layout I have so far, since the user interface is so easy, so I'm just running with it. Gotta keep the boss happy! I've never been known to do things the easy way, but the simplicity of the booking with everything at their fingertips seemed like a good way to go. Much more user friendly than fumbling through 16 calendars. But only if it works!

[Micron]

So you not only want to see the schedule of 16 rooms for a given date all at the same time, you want to do the booking from Access as well?
Hope you're not leaving there too soon!

[NISMOJim]

That will be the nice part. They won't have to stumble through 16 calendars. When the building is selected, only the calendar for that building will show up.

[pbaldy]

I've added appointments to Outlook from Access but never using a shared calendar, so some of that code is new to me. The error seems to imply that the account isn't recognized. Have you set a breakpoint and seen what that resolves to?

[NISMOJim]

I set the break point at the line where the error occurs;

Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

When I hover over the oCalendar part, it says "oCalendar = Nothing.
When I hover over the oAcct part, it says "oAcct = ATCG8S1"
When I hover over the olFolderCalendar part, it says "olFolderCalendar = 9"

Not sure if it makes a difference, but in Outlook in the Calendar tab on the left side of the screen, I have folders listed as My Calendars and Shared Calendars. ATCG8S1 is under My Calendars. Maybe it is supposed to be under Shared Calendars, but I don't seem to have the option to set that up. I can only create a new calendar to be placed in My Calendars. Is that something I need some kind of administrative privileges to do?

[Micron]

It equals nothing because the line hasn't been executed if it's highlighted. You can't reliably check variables on the left side of = on a highlighted line. You have to move off of it by at least one, or whatever it takes to assign the value. As for the rest, I don't know, but I figured this answer would allow you to do over and report on what oCalendar is equal to.

[NISMOJim]

Ok, I moved the break up one line where it says "Set oAcct = oNameSpace.CreateRecipient(calName)". oCalendar (declared as Dim oCalendar As Outlook.MAPIFolder), still says " = Nothing", and olFolderCalendar still says "= 9". No idea what that means. Of course the line now with the break is where it sets oAcct, so nothing is there yet.

Of course I can't set the break to the line below the error line. Not sure where to go from here.

[Micron]

If you moved it up how does that allow where you had the break to be processed? If you can't move a break down one line because the next line isn't suitable then you add something in between and put the break there. A simple msgbox or debug.print will do.

[NISMOJim]

Ok,I added Debug.Print between the lines

Set oAcct = oNameSpace.CreateRecipient(calName)
Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

but I still get the same thing, oCalendar = Nothing, oAcct = ATCG8S1 & olFolderCalendar = 9.
Could this be because the calendar has not been shared with anyone yet? Something to do with the GetSharedDefaultFolder?

[Micron]

As I mentioned in post 5, I'm not the one who can help with automating Outlook. My contribution on the subject would be limited to basic code behaviour or techniques. Sorry.
I do think your assumption has merit though. In cases like this, I try to examine the members of a collection or set of properties, depending on which I'm trying to find. If what you're after is an object, then maybe along the lines of

Dim obj As Object
For Each obj in oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)
   debug.print obj.Name
Next

If that produces a list of items in a collection you're looking for, then you can find out if a certain value is present. I kind of doubt that you're looking for an object here, though.