How do I create an array of text email addresses to send a message from Flow?

Question

I have 10 text fields in my SharePoint list which may contain:

1. An email address
2. A note of "Email Missing" if an address is expected but not available
3. Null/empty

I want to send an email to any of the 10 potential columns that have a valid address (scenario 1). If the field contains "Email Missing" or is null, I obviously can't send for that column.

I initially thought I could just use the dynamic content from my SharePoint list in my BCC field (so the recipients can't see the other recipients' addresses) with semicolons between each, but got an error when any of them returned "Email Missing" where it was expecting an email address.

I have cobbled together a clumsy set of conditions to check, for each field, if it's either null or says "Email Missing", and to send only if it's valid, but it's very inefficient by the time I get through all 10, and if I need to later change the content of the email message, I need to update it after every one of these conditions when I send the message.

So, it seems that an array would work, but I haven't used them in Flow. Can anyone help outline how to:

1. Set up an array to hold all of the existing email addresses from my 10 text fields (while not including any with "Email Missing" text)
2. How to then call the contents of that array to send an email message to those recipients

I am triggering the flow based on when an item or file is modified, so when the user sets the "Action" field to "Ready" and saves the list item, the flow is launched. That part is working fine - I just need help to collect and use the email addresses.

Thanks!

Answer 1

I think you can accomplish this by reading column values dynamically and using one condition action.

See the screenshots and explanation below: (The example shows for a list, but applicable to a library as well)

The High Level View

Detail 1

Note: For expression triggerBody()?[items('Apply_to_each')], type function triggerBody()? in the expression window and then click Dynamic content and then select Current item under [Apply to each] found at the bottom.

Detail 2

Processing each email in arrayEmails. In this example, email is being sent separately. But a single email can be sent by concatenating all emails into a string variable.

Column values in SPO List

Good Emails in arrayEmails

[
  "[email protected]",
  "[email protected]",
  "[email protected]",
  "[email protected]",
  "[email protected]"
]

Answer 2

In the end, rather than wrestling further with incorporating an array, I took a step back and manipulated the data in my list so that the initial "update item" flow had the intelligence, and the "send email" flow could just use the compiled set of addresses.

I used the CONCAT function with a couple of nested IF statements for each field to test:

1. If the field is null, then just record that as an empty value ('')
2. If the field has the email missing warning text ("Missing"), then also also record that as an empty value, since I won't be sending any email based on that ('')
3. If the field isn't null or "Missing", then I'll assume it has an email address in it, so record that email address, and put a semicolon after it so I can include subsequent addresses as needed.

This is what the function looks like:

concat( if(empty(triggerOutputs()?['body/Email1']), '', if(endsWith(triggerOutputs()?['body/Email1'], 'Missing'), '', concat(triggerOutputs()?['body/Email1'], ';'))), if(empty(triggerOutputs()?['body/Email2']), '', if(endsWith(triggerOutputs()?['body/Email2'], 'Missing'), '', concat(triggerOutputs()?['body/Email2'], ';'))), if(empty(triggerOutputs()?['body/Email3']), '', if(endsWith(triggerOutputs()?['body/Email3'], 'Missing'), '', concat(triggerOutputs()?['body/Email3'], ';'))), if(empty(triggerOutputs()?['body/Email4']), '', if(endsWith(triggerOutputs()?['body/Email4'], 'Missing'), '', concat(triggerOutputs()?['body/Email4'], ';'))), if(empty(triggerOutputs()?['body/Email5']), '', if(endsWith(triggerOutputs()?['body/Email5'], 'Missing'), '', concat(triggerOutputs()?['body/Email5'], ';'))), if(empty(triggerOutputs()?['body/Email6']), '', if(endsWith(triggerOutputs()?['body/Email6'], 'Missing'), '', concat(triggerOutputs()?['body/Email6'], ';'))), if(empty(triggerOutputs()?['body/Email7']), '', if(endsWith(triggerOutputs()?['body/Email7'], 'Missing'), '', concat(triggerOutputs()?['body/Email7'], ';'))), if(empty(triggerOutputs()?['body/Email8']), '', if(endsWith(triggerOutputs()?['body/Email8'], 'Missing'), '', concat(triggerOutputs()?['body/Email8'], ';'))), if(empty(triggerOutputs()?['body/Email9']), '', if(endsWith(triggerOutputs()?['body/Email9'], 'Missing'), '', concat(triggerOutputs()?['body/Email9'], ';'))), if(empty(triggerOutputs()?['body/Email10']), '', if(endsWith(triggerOutputs()?['body/Email10'], 'Missing'), '', concat(triggerOutputs()?['body/Email10'], ';'))) )

I used that function to populate my new "Recipients" field, so it only includes the existing email addresses with semicolons, and I can simply use "Recipients" as the dynamic content for my BCC field when I send the email.