# create a meeting invite getting invitee's names from cell values



## jmk15315 (Dec 29, 2022)

I have a userform that I have created to guide team members in a uniform manner to complete a form.  I have already created a macro to send a pdf of the meeting notes to attendees of the meeting.  What I am curious about is how I would create a meeting using the list of names from the cells referenced by the userform.  For example, the names listed in cells A1:A25.


----------



## bferraz (Dec 30, 2022)

Hey, try following code:


```
Sub Meeting()

    Dim ObjOutlook As Object
    Dim ObjMeeting As Object
        
    Set ObjOutlook = CreateObject("Outlook.Application")
    Set ObjMeeting = ObjOutlook.CreateItem(olAppointmentItem)


    With ObjMeeting
    
    .RequiredAttendees = WorksheetFunction.TextJoin(";", True, Range("A1:A25"))
    .Subject = ""
    .Location = ""
    '.Start = ""
    '.End = ""
    '.ReminderMinutesBeforeStart = ""
    .Body = ""
    .Display

    
    End With

End Sub
```


----------



## jmk15315 (Jan 3, 2023)

Thank you very much for the assistance.  The code did not work initially, as I did not select the correct preferences.  That I resolved however, it creates the meeting and displays the results, but, I have to click on the invite Attendees button to display the names imported from my worksheet.  Is there a way to have this happen automatically when the meeting invite window displays?


----------



## bferraz (Jan 3, 2023)

Try the version below. I made the changes on the initial code since you haven't updated it in here.


```
Sub Meeting()

    Dim ObjOutlook As Object
    Dim ObjMeeting As Object
    Dim RequiredAttendee As Outlook.Recipient
        
    Set ObjOutlook = Outlook.Application
    Set ObjMeeting = ObjOutlook.CreateItem(olAppointmentItem)


    With ObjMeeting
    
    .MeetingStatus = olMeeting
    .RequiredAttendees = WorksheetFunction.TextJoin(";", True, Range("A1:A25"))
    .Subject = ""
    .Location = ""
    '.Start = ""
    '.End = ""
    '.ReminderMinutesBeforeStart = ""
    .Body = ""
    .Display
    
    End With

End Sub
```


----------



## jmk15315 (Jan 3, 2023)

Here is the code I am using.  Clicking "Invite Attendees" in the meeting will populate the names from my worksheet, was just hoping to not have to click that button. 

Private Sub CommandButton2005_Click()

    Dim ObjOutlook As Object
    Dim ObjMeeting As Object
    Dim RequiredAttendees As Outlook.Recipient
    Dim OptionalAttendees As Outlook.Recipient

    Set ObjOutlook = CreateObject("Outlook.Application")
    Set ObjMeeting = ObjOutlook.CreateItem(olAppointmentItem)

    Sheet2.Visible = xlSheetVisible
    Sheet2.Select

    With ObjMeeting

    .RequiredAttendees = WorksheetFunction.TextJoin(";", True, Range("AG4:AG13"))
    .OptionalAttendees = WorksheetFunction.TextJoin(";", True, Range("AG14:AG51"))
    .Subject = ActiveSheet.Range("G11") & "-" & ActiveSheet.Range("G12") & "-" & ActiveSheet.Range("G22")
    .Location = ActiveSheet.Range("G21")
'    .Start = ""
'    .End = ""
    .ReminderMinutesBeforeStart = 15
    .Body = "Please forward this meeting invite to any team member you feel should also be included."
    .Display

    End With

    Sheet2.Visible = xlSheetHidden
    Sheet1.Visible = xlSheetVisible

End Sub


----------



## bferraz (Jan 4, 2023)

Did the last update manage to fix the problem?


----------



## jmk15315 (Jan 5, 2023)

Sadly it did not, but that is not a show-stopper.  I am still able to accomplish my goal, just one additional step that I can live with.


----------



## bferraz (Jan 6, 2023)

Sorry to hear that, I hope someone can help you with that. 
As far as my knowledge goes, changing the meeting status should fix this. If you want to give it a go, this is your current code with these changes.
Hope this works for you! 😁


```
Private Sub CommandButton2005_Click()

Dim ObjOutlook As Object
Dim ObjMeeting As Object
Dim RequiredAttendees As Outlook.Recipient
Dim OptionalAttendees As Outlook.Recipient

Set ObjOutlook = Outlook.Application
Set ObjMeeting = ObjOutlook.CreateItem(olAppointmentItem)

Sheet2.Visible = xlSheetVisible
Sheet2.Select

With ObjMeeting

.MeetingStatus = olMeeting
.RequiredAttendees = WorksheetFunction.TextJoin(";", True, Range("AG4:AG13"))
.OptionalAttendees = WorksheetFunction.TextJoin(";", True, Range("AG14:AG51"))
.Recipients.ResolveAll
.Subject = ActiveSheet.Range("G11") & "-" & ActiveSheet.Range("G12") & "-" & ActiveSheet.Range("G22")
.Location = ActiveSheet.Range("G21")
' .Start = ""
' .End = ""
.ReminderMinutesBeforeStart = 15
.Body = "Please forward this meeting invite to any team member you feel should also be included."
.Display

End With

Sheet2.Visible = xlSheetHidden
Sheet1.Visible = xlSheetVisible

End Sub
```


----------

