Results 1 to 3 of 3

Thread: Add Multiple Optional Attendees To Outlook Calendar Invite

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Add Multiple Optional Attendees To Outlook Calendar Invite

    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:
    Code:
    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
    Last edited by Excel Fox; 02-06-2013 at 09:27 PM. Reason: Added code tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    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:

    Code:
    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.
    Last edited by Kainic; 02-06-2013 at 09:47 PM.

Similar Threads

  1. Replies: 12
    Last Post: 12-10-2019, 09:56 PM
  2. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  3. Add Named Ranges To Multiple Workbooks Using VBA
    By Stalker in forum Excel Help
    Replies: 5
    Last Post: 04-23-2013, 12:20 PM
  4. Outlook Send Mail With Multiple Recipient and CC
    By noobtron in forum Excel Help
    Replies: 2
    Last Post: 10-31-2012, 07:14 PM
  5. Replies: 7
    Last Post: 05-09-2012, 11:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •