Add Multiple Optional Attendees To Outlook Calendar Invite

[Kainic]

Hi,

I'm trying to create an event on calendar and invite multiple people to it from an excel sheet. I'm succesful with a single person, using strTo but I'm having problems with strCC with multiple users.

Here's the code I'm using:


Sub PromoOutlk()

Dim olApp As Outlook.Application
Dim objapp As Outlook.AppointmentItem
Set olApp = New Outlook.Application
Set objapp = olApp.CreateItem(olAppointmentItem)

strSubj = Sheets("BBDD").Range("A2").Value
strBody = Sheets("BBDD").Range("B2").Value
strTo = Sheets("BBDD").Range("C2").Value
intStatus = Sheets("BBDD").Range("D2").Value
dtFecha = Sheets("BBDD").Range("E2").Value
dtTime = Sheets("BBDD").Range("F2").Value
strAlarm = Sheets("BBDD").Range("G2").Value
intDurAlarm = Sheets("BBDD").Range("H2").Value

With objapp
.Subject = strSubj
.Body = strBody
.RequiredAttendees = strTo
.BusyStatus = intStatus
.Start = dtFecha + dtTime
.ReminderSet = False
.MeetingStatus = olMeeting
If strAlarm = "Si" Then
.ReminderMinutesBeforeStart = intDurAlarm
.ReminderSet = True
End If
.Display
.Send
.Save
End With

Set objapp = Nothing
Set olApp = Nothing

End Sub

My idea is to have 2 fixed CCs (mail1@whatever.com and mail2@whatever.com) and 1 To (refered to a cell).
Hope you can help.
Thanks in advance.

Sergi

[Excel Fox]

Welcome to ExcelFox. Please use code tags to wrap all codes in your post. I've wrapped it this time.

Did you try using OptionalAttendees property?

[Kainic]

I'm a total noob using macros in excel and VB. I'll investigate a bit about OptionalAttendees and I'll ask you again if I can't find the answer by myself.

Thank you Excel Fox ;)

Edited:


Sub PromoOutlk()

Dim olApp As Outlook.Application
Dim objapp As Outlook.AppointmentItem
Set olApp = New Outlook.Application
Set objapp = olApp.CreateItem(olAppointmentItem)

strSubj = Sheets("BBDD").Range("A2" & Fila).Value
strBody = Sheets("BBDD").Range("B2").Value
strTo = Sheets("BBDD").Range("C2").Value
strCC = "mail1@whatever.com; mail2@whatever.com"
intStatus = Sheets("BBDD").Range("D2").Value
dtFecha = Sheets("BBDD").Range("E2").Value
dtTime = Sheets("BBDD").Range("F2").Value
strAlarm = Sheets("BBDD").Range("G2").Value
intDurAlarm = Sheets("BBDD").Range("H2").Value

With objapp
.Subject = strSubj
.Body = strBody
.RequiredAttendees = strTo
.BusyStatus = intStatus
.Start = dtFecha + dtTime
.ReminderSet = False
.MeetingStatus = olMeeting
If strAlarm = "Si" Then
.ReminderMinutesBeforeStart = intDurAlarm
.ReminderSet = True
End If
.Display
.Send
.Save
End With

Set objapp = Nothing
Set olApp = Nothing

End Sub


Edited to show the final code. Thanks for showing me the way. It was not that hard, but I didn't know about OptionalAttendees.