0

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!

0

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

enter image description here

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.

enter image description here

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.

enter image description here

Column values in SPO List

enter image description here

Good Emails in arrayEmails

[
  "Hello@gmail.com",
  "Myemail@aol.com",
  "Youremail@yahoo.com",
  "Test@gmail.com",
  "Mantro.test@two.com"
]

2
  • Thanks so much for this very clear example! After posting the question I kept working on it from other angles, and so I modified how I'm working with the list data - in addition to my 10 individual text fields, I created an additional field in my SharePoint list (Recipients) and used some conditional logic in the Update Item flow to pull in any of the individual emails that are in those fields as a single text value with semicolons between each address. Details are below, but I really appreciate this and will keep it handy for the future!
    – Darryl
    Feb 18 at 18:50
  • You could mark the question as answered as your original question has been answered. Feb 18 at 19:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.