VBA - sending a meeting invitation to several people

[hotmochaccin]

Hello!


I am having trouble with writing a code for the following function.


I have a table that has previously been completed by vba code, in which there are columns with names, surnames, e-mail addresses and in the last one: whether the person will receive the invitation for outlook meeting or not.

The entire invitation sent via Outlook with a link to Teams works great, but I have a problem with writing code that only people with an annotation "Invite" will be choosen for "RequiredAttendees" into a meeting.



Does anyone know how to sum these selected fields with e-mail addresses?

Thank you!

[georgiboy]

Hi hotmochaccin,

Welcome to the furum.

Do you have any code that you can share? - we can then look through what you have already.

[hotmochaccin]

Hello georgiboy, thank you :). Below are fragments of what I have:



Sub MeetingInvitation()


Dim OutApp As Outlook.Application
Dim Outmeet As Outlook.AppointmentItem
Set OutApp = Outlook.Application
Set Outmeet = OutApp.CreateItem(olAppointmentItem)


'who will receive an invitation
Dim people As Long
Dim i As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(X.Range("F:F"))
For i = X.Range("B12").Value To last_row
people = X.Range("A" & i).Value
Next


(...)


'invitation
With Outmeet
.Subject = X.Range("B2").Value & " - X"
.RequiredAttendees = people /this part is problematic
.Start = X.Range("F3").Value & " " & Format(X.Range("D3").Value, "h:mm")
(...)
End With

(...)
End Sub


The table looks like this:

e-mail | status?
xx | Invite
yy | Reservation list
zz |Invite


The previous code is setting the status so only two comments are possible. How many people will be invited is a constant value, but for example not always it will be the first 10 people.

The problem is that I don't quite know how to use 'If' or 'Loop' using the neighbor cell condition. I've already tried to do it by the hard way - copy only people to be invited to the adjacent column by adding "; " at the end of the email, but this method didn't work either.

[georgiboy]

Maybe you could try the below, it assumes the email address is in column 'F' and the word 'Invite' is in column 'G' it will look through the list and build a list of email addresses that have 'Invite' next to them and pass this to the 'RequiredAttendees' piece at the end.


Sub MeetingInvitation() Dim OutApp As Outlook.Application
Dim Outmeet As Outlook.AppointmentItem
Dim i As Long, x As Long, last_row As Long, people As String

Set OutApp = Outlook.Application
Set Outmeet = OutApp.CreateItem(olAppointmentItem)

'who will receive an invitation
last_row = Range("F" & Rows.Count).End(xlUp).Row
For i = 2 To last_row
If Range("G" & i).Value = "Invite" Then
people = people & Range("F" & i).Value & "; "
End If
Next

If Len(people) > 0 Then
people = Left(people, Len(people) - 2)
Else
MsgBox "No invites"
GoTo ender
End If

'invitation
With Outmeet
.Display
.Subject = Range("B2").Value & " - X"
.RequiredAttendees = people
.Start = Range("F3").Value & " " & Format(Range("D3").Value, "h:mm")
End With

Exit Sub
ender:
Outmeet.Delete
OutApp.Quit
End Sub

Hope this helps