form to bulk capture Email Addresses as separate rows

First off I'm just going to say this is the first time I have attempted to write anything in VBA so please be patient with me

I'm currently supporting a team that uses word to capture data from engagements, they are not comfortable with excel. To make it easier for them i was asked if i could create a form that they would input to that would write to a table that can then be used for analysis and insights. (as obviously word is not good in this regard)

I have created a form that works for the most part....the form attached writes a row of data and works when only one email address is used for Attendee,

The complication is that they want two capture fields "Invited Email Addresses" & "Attended Email Addresses" where they can just paste multiple email addresses at once from Outlook.

the logic should be to capture each unique email address as a new row and record the same values from the other capture fields for each row too. additionally it needs to record a yes or a no for both the Invited and Attended fields.
I will attach an image to show the current form and how it should look once recorded in excel....

i tried looking at a combination of split and loop but it was way beyond my current level

thanks in advance for any advice you can give
Screenshot 2022-01-29 095733.png

help us to help you - see big yellow banner - upload your workbook with de-sensitised data.
there is more chance of a speedy response if we do not have to reconstruct your app.

This will make a Invite list and Attend list based on the "Yes" values.

Place them in the TextBoxes in the userform

Please Login or Register  to view this content.

apologies i have now attached the current xlsx the macro shortcut is "ctrl q" there are three forms, formEngagement and FormUpdate are working fine FormNew is where i need to do the email splitting and yes/no on attend and invited fields.... the attached image displays how the data should look once saved in the form ie new line for each unique email and yes/no depending on which box they were in

My thoughts are is it possible to.......
1 - create an array from "Invited Email Addresses" by using split with ";" delimiter (call this InvArr)
2 - create an array from "Attended Email Addresses" by using split with ";" delimiter (call this AttArr)
3 - create an array from *InvArr" and "AttArr" (call this EmailArr)
3 - deduplicate any emails that appear in "EmailArr" (call this EmailUnique)
4 - have a floating Email value that is the first email in "EmailUnique" (call this Emailfloat)
5 - check to see if "Emailfloat" exists anywhere in "InvArr" returning yes or no (call this InvCheck)
6 - check to see if "Emailfloat" exists anywhere in "AttArr" returning yes or no (call this AttCheck)
7 - write values of "Emailfloat", "InvCheck" and "AttCheck" and the other static fields as a row
8 - updates "Emailfloat" to next value from "EmailUnique"
9 - repeats 5 - 8 until all values in "EmailUnique" have been checked and written

this would also need to work even if there was only one email input which might screw up the arrays.