Sending e-mails to different addresses bases on a value

[tomswaelen]

Is it possible to make a flow that sends an e-mail based on an MS Forms input, but send it to different e-mail addresses? For example, there is a dropdown in Forms with options:

- Marketing department

- Account department

- Management

And direct the flow of e-mails based on that input? The message body is always the same.

I can see a number of options myself:

- use a switch on the department field, but it seems bad practice to duplicate the e-mail message in the branches of the switch

- a compose action with nested ifs, but this seems clunky

If this were in Excel, I would make a table and use a VLOOKUP.

Is there a better way to do this?

[David_MA]

You could still put the e-mail addresses in a spreadsheet. Have one column in the spreadsheet with the department name choices, and then another column with the e-mail addresses semicolon separated by. You can then use the List rows present in a table action with a filter query on the department name column.

When you go to address your email, it will put it in an Apply to each action, which is okay. This is because list rows action will return an array. You just need to be sure that each department is only listed one time in the spreadsheet. You could tell it to return the Top 1 Count, but it will still put it in an Apply to each.

[tomswaelen]

I only mentioned Excel as that is how I would have done it using Excel. I'd prefer not to use Excel for this flow.

I assumed I could also declare an array with the compose action, and then use a Filter Array to only filter the record I want based on the Forms input. But then I don't get the output of the Filter Array as dynamic content for the e-mail address.

[David_MA]

If you only have three departments as the choices, I don't really see using the Switch action being clunky. In your original post, you said you wanted to send the e-mail to different people based on the department, so I don't see how using the switch would cause duplicate e-mail addresses.

If there is a common group of people that you always send the e-mail to irrespective of the department, then I would create an array variable at the beginning of your flow and populate it with the common e-mail addresses. In the switch, you can append the array with the additional people you need to add based on the department selected.

This way, the send e-mail action is outside of the switch so you only have one action that sends the message, and it sends it to the people in the variable. Of course you will need to use a join expression on the values in the array to get them semicolon separated, but that is easy to do.

[tomswaelen]

Perhaps I should have been clearer.

There is a list of radio buttons in my MS Form:

Department A

Department B

Department C

....

There are about 7 departments. 

If a user chooses department A, an e-mail is sent to the e-mail address of that department (so no multiple addresses)

If a user chooses department B, an e-mail is sent to the e-mail address of department B.

Etc...

However, the content of the e-mail is exactly the same. So if I were to use a switch, I'd have to duplicate the Send e-mail action across all seven branches.

Another option I suppose is to put the content of the e-mail in a compose action, and reference that in the body  of the e-mail.

But is there no option to put the departments and their e-mail addresses in an array, and filter that array based on the Forms input? I can use a Compose action to define the array and use a Filter array action, using the forms input as filter. However, I don't get the output of the filter array as dynamic content for my e-mail address.

[David_MA · Accepted solution]

No, you wouldn't need to duplicate the e-mail. At the start of your flow, initialize a string variable named EmailAddresses for example, and leave the default blank. Then in your switch, set the EmailAddresses variable with the ones for the department selected (be sure to semicolon separate them if there is more than one e-mail address). Then outside and after the switch, use one e-mail action and use the EmailAddresses variable in the To field. For example:

[tomswaelen]

That did the trick, thank you! 🙂