VBA to create meeting from template from a time slot selected in someone's calendar

[AlexP]

Hello Diane and all fellow forum members

For the last a few days I was struggling to write a VBA code that does very simple thing: assume I open ANY calendar available to me (either my calendar or any shared calendar), then I select a time slot I want schedule meeting at, then run a macro that creates a meeting with selected time. The code works fine if I select a time slot in my calendar, and it also picks correct time in a shared calendar - the only thing I can't figure out is how to get email address of the shared calendar (so I can add an attendee with this email address).

The code I've got at the moment is the following:

Sub TestSub()
Const datNull As Date = #1/1/4501#
Dim myItem As Object
Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient
Dim datStart As Date
Dim datEnd As Date
Dim oView As Outlook.View
Dim oCalView As Outlook.CalendarView
Dim oExpl As Outlook.Explorer
Dim oFolder As Outlook.Folder
 Set oExpl = Application.ActiveExplorer
 Set oFolder = Application.ActiveExplorer.CurrentFolder
 Set oView = oExpl.CurrentView
' Check whether the active explorer is displaying a calendar view.
 If oView.ViewType = olCalendarView Then
  Set oCalView = oExpl.CurrentView
  ' Create the appointment using the values in
  ' the SelectedStartTime and SelectedEndTime properties as
  ' appointment start and end times.
  datStart = oCalView.SelectedStartTime
  datEnd = oCalView.SelectedEndTime
Dim myTemplate As String
myTemplate = "<path to template>\templatename.oft"
Set myItem = Application.CreateItemFromTemplate(myTemplate)

  Dim strCategories As String
  Dim strSubject As String
  strCategories = "MyColourCategory"
  strSubject = "Change subject line"
  Set myRequiredAttendee = myItem.Recipients.Add("name.surename@outlook.com")
 
With myItem
  .Subject = strSubject
  .Categories = strCategories
  .ReminderSet = bolRemindMe
  .Body = strBody
  If bolRemindMe = True Then
  .ReminderMinutesBeforeStart = intRemindMe
  End If
 End With
  If datStart <> datNull And datEnd <> datNull Then
  myItem.Start = datStart
  myItem.End = datEnd
  End If

myItem.Display
 
End Sub

==========

So oCalView.SelectedStartTime picks correct time regardless which calendar is selected. Appreciate if you could point me how to extract email address of calendar.

AlexP

 

[Diane Poremsky]

is the calendar a shared exchange account or a shared calendar from outlook.com?

 

[AlexP]

Hello Diane,
It's a shared exchange calendar.

 

[AlexP]

I mean calendar in a shared exchange account. It appears that I'm trying to mimic functionality of the Button 'New Meeting' -> 'New Meeting with All' but the meeting is created from a custom template..

 

[Diane Poremsky]

if you select the calendar first, new intem > from template should work.... or you can use a macro. if you need to pass the calendar name to the macro, that will work too, although i don't think i have any code handy.
How to Open Outlook Templates and Files using Toolbar Buttons

 

[AlexP]

Hello Diana,
Yes, I actually need to pass the calendar name to the macro - I just can't figure out how to do it. I've got a shared calendar open, when I select a time slot in that shared calendar and run the macro it creates new appointment from my custom template, but ideally I also need to add owner of the shared calendar to the meeting invite.
Is there any way to get a reference to calendar name from selection?