How to book meetings into shared Outlook calendar with VBA Excel

[Nille79]

Hi everybody :-)

I only have a little experience with VBA and no experience with Excel Forum - this is my first thread, so please be kind ;-)

The case:
I have an Excelfile with names, initials, dates, time etc. It's basically an overview that tells when the employee is going to the annual meeting with the boss. The task is to write a code that can loop through the lines in the table and book the meeting in the boss' calendar.
Without problems I can make a code that loops through the lines in Excel and I've made the code below that - played in Outlook - will book a meeting in the boss' calendar. Each code works fine in the separate applications.

My problem is to combine the two, so I can loop through the code in Excel and for each line book the meeting in the boss' calendar and send the invitation to the employee.

This is my code for the Outlook-part:
Option Explicit

Sub SelectCalendar()
Dim objPane As Outlook.NavigationPane
Dim objModule As Outlook.CalendarModule
Dim objGroup As Outlook.NavigationGroup
Dim objNavFolder As Outlook.NavigationFolder
Dim objFolder As Folder
Dim calItem As Object

Dim i As Integer

Set Application.ActiveExplorer.CurrentFolder = Session.GetDefaultFolder(olFolderCalendar)

DoEvents

Set objPane = Application.ActiveExplorer.NavigationPane
Set objModule = objPane.Modules.GetNavigationModule(olModuleCalendar)

With objModule.NavigationGroups
Set objGroup = .Item("Shared calendars")
End With

For i = 1 To objGroup.NavigationFolders.Count
On Error GoTo handler
If (objGroup.NavigationFolders.Item(i).Folder.FullFolderPath = "00xxxxxxxxxxxxxxxxxxxx00") Then
Dim foldernum As Integer
foldernum = i
End If
gohere:
Next

Set objNavFolder = objGroup.NavigationFolders.Item(foldernum)
Set calItem = objNavFolder.Folder.Items.Add(olAppointmentItem)
calItem.MeetingStatus = olMeeting
calItem.Subject = "Test-meeting"
calItem.Location = "Meetingroom"
calItem.Start = #8/17/2022 3:00:00 PM#
calItem.Duration = 90
calItem.RequiredAttendees = "[mailadress]"
calItem.Save
If (calItem.Recipients.ResolveAll) Then
calItem.Send
Else
calItem.Display
End If

Set objPane = Nothing
Set objModule = Nothing
Set objGroup = Nothing
Set objNavFolder = Nothing
Set objFolder = Nothing
Set calItem = Nothing

Exit Sub
handler:
GoTo gohere

End Sub


-----

If you think I should make changes in the code above, please make the changes in my own code instead of providing me a full new code - I need to understand what I'm doing :-)

I really hope that someone out there can help and please don't just send me links to other pages on the internet - I've been there and tried different things before reaching out here...

Looking forward to your answers.

Pernille

[Toonies]

have a look at this it may help you out

https://eileenslounge.com/viewtopic.php?t=31690

[Nille79]

have a look at this it may help you out

Thanks for your quick reply but the thread is not the same topic as mentioned in my post.

Pernille