So I was writing my initial question when I partially solved it while creating an example document to share (lol) but then ran into a new problem which is that Hyperlink can only be 255 characters long. I will post my original post below but my current issue is that I am creating a mailto hyperlink and it may contain as many as 15 emails which will almost always cross that 255 character threshold. I am trying to stay away from VBA as this spreadsheet will be hosted as a Microsoft Teams tab and is supposed to be an easy way for some of my superiors to start emails to specific groups of people with a single click. I am not sure if VBA can be used in a spreadsheet that is being accessed through the Microsoft Teams app.

So my questions are as follows:
  1. Is there a way to create a hyperlink that has more than 255 characters without using VBA?
  2. Has anyone tried using VBA script in a spreadsheet that is being accessed through Microsoft Teams?

Thanks in advance!

My original (unposted) post:
All,

A few weeks ago I was able to get the following formula to work as a way to start an email with an automatically populated "To:" field to multiple recipients.
Please Login or Register  to view this content.
This method requires the user to manually enter each cell into the formula manually which is not the most efficient method.

My main worksheet has the following columns: Company, Discipline, Name, Title, Email, Phone. This worksheet has all of the contacts that may be working on a project and is to be used as a starting point for each project. When I project is started, the user will duplicate this worksheet and then erase all contacts that do not apply to that project. Since each project will have different contacts in different cells, the manual approach to creating email shortcuts will simply waste time. I want to be able to create hyperlinks that email everyone from a given company (or combination of companies) without the manual update process.

So I did some research and came up with the idea to use TEXTJOIN combined with an IF statement to create the same string that resides inside the HYPERLINK code shown above.
Please Login or Register  to view this content.
This formula works perfectly and creates the same string that exists in the original formula. Unfortunately, when I plug it into the Hyperlink formula I get a warning that "a value used in the formula is of the wrong type of data".

Please Login or Register  to view this content.
While creating a sample document and, subsequently, simplifying the amount of data within, I solved the issue which was a 255 hyperlink character limit which means my Textjoin idea is actually working properly. Now I just have to figure out a way to get around that limit.