Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

Sending Outlook Appointment to Public Calendar with VBA

I don't have public calendars set up on my machine and I need to code an application sending appointments via Microsoft Access VBA to a calendar named "Construction Calendar". The following code works for the default calendar:

Public Sub CreateAppt(ApptDate As Date, ApptTime As Date, ApptDays As Long, ApptSubject As String, ApptNotes As String, ApptLocation As String, ApptReminderMinutes As Integer)
    On Error GoTo AddAppt_Err
    ApptDays = ApptDays * 60 * 24
   
    Set outobj = CreateObject("outlook.application")
    Set outappt = outobj.CreateItem(olAppointmentItem)
    With outappt
       
       .Start = ApptDate & " " & ApptTime
       .Duration = ApptDays
       .Subject = ApptSubject
       If Not IsNull(ApptNotes) Then .Body = ApptNotes
       If Not IsNull(ApptLocation) Then .Location = _
          ApptLocation
       If ApptReminder Then
          .ReminderMinutesBeforeStart = ReminderMinutes
          .ReminderSet = True
       End If
       .Save
    End With
 Set outobj = Nothing

 MsgBox "Appointment Added!"
      Exit Sub
AddAppt_Err:
         MsgBox "Error " & err.Number & vbCrLf & err.Description
         Exit Sub
   
End Sub
0
pabrann
Asked:
pabrann
1 Solution
 
Jack LeachCommented:
I'm not going to pretend I can give a complete answer here, but a bit of guidance, at least.  Essentially, what you need to do is get access to the desired calendar Folder from Outlook.  Most calendars can be obtained easily enough by looping profiles and folders and checking the DefaultItem property of a Folder object to determine if it's an olAppointmentItem default, in which case you have a Calendar.

However, I've seen cases where SharePoint/Exchange shared calendars don't show up in this list.  In such case, you do the following:

- Get the ActiveExplorer
- Get the CalendarModule ("modules" are the main Outlook groups: Mail, Tasks, Calendar, Contacts etc)
- Loop the NavigationGroups of the CalendarModule (eg, the top-level of the tree-like selector thing on the left nav panel)
- Loop the NavigationFolders of the NavigationGroup that you need, which is where you can finally get your Folder reference to the shared calendar.

Fun fun.


Here's some basic code to get your profiles.  You'll have to adapt, as I haven't cleaned it up:

'returns a collection of all profiles currently loaded into the outlook session
Public Function GetProfileList() As VBA.Collection
On Error GoTo Err_Proc
'=========================
  Dim ol As Object
  Dim o As Object
  Dim ret As VBA.Collection
'=========================

  Set ret = New VBA.Collection
  
  Set ol = OutlookAutomation.MAPINamespace
  
  For Each o In ol.folders
    ret.Add o, o.Name
  Next o

'=========================
Exit_Proc:
  Set ol = Nothing
  Set GetProfileList = ret
  Exit Function
Err_Proc:
  Err.source = "OutlookAutomation.GetProfileList"
  Select Case Err.Number
    Case Else
      HandleError
  End Select
  Resume Exit_Proc
  Resume
End Function

Open in new window



Given a Profile objects (such as the above returns), here's some code to get the Calendar:

Public Function GetCalendarFolders(ProfileName As String) As VBA.Collection
On Error GoTo Err_Proc
'=========================
  Dim ret As VBA.Collection
  Dim o As Object
'=========================

  Set ret = New VBA.Collection
  
  For Each o In OutlookAutomation.GetProfileFolder(ProfileName).folders
  
    If o.DefaultItemType = olItemType.olAppointmentItem Then ret.Add o, ProfileName & ":" & o.Name
  
  Next o

'=========================
Exit_Proc:
  Set GetCalendarFolders = ret
  Exit Function
Err_Proc:
  Err.source = "OutlookAutomation.GetCalendarFolders"
  Select Case Err.Number
    Case Else
      HandleError
  End Select
  Resume Exit_Proc
  Resume
End Function

Open in new window



Again, you'll have to adjust the code accordingly.  Again, this is for "default" Outlook objects, and obtaining a reference to the a public calendar via Outlook may require the more convoluted process as previously described.  Perhaps though, this will get you a bit more acclimated with the Outlook model, thus giving a push in the right direction.

Once you have your desired Calendar folder, just .Add to the .Items collection.

Good luck,
-jack
0
 
pabrannPresidentAuthor Commented:
I am going to try this while I'm at my client site this week. Thank you very much for your assistance so far. I'm sorry I haven't been able to get to this any sooner..
0
 
MacroShadowCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Jack Leach (http:#a40203708)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now