Send email to multiple users via send HTTP request to Sharepoint

[mhdukes1]

Hi, I need assistance with sending one email to multiple users. I created a Microsoft Form for other departments to submit a legal request. This form uses a flow to transfer the information into a sharepoint list and sends a receipt email to the requestor (via send an HTTP request to SP). 

Next, I've created another flow for the SP list which has the same information but should go to the assigned staff (default assignee, 2nd assignee and staff). I have a separate column for each email address. The flow fails if I put all three dynamic content columns in the "To" field, and other times it won't hold just one column. It simply removes the information and instead the result is [""] and no email sent.

Ultimately, Step 1 processes to the requestor and then Step 2 starts and goes to the assigned staff.  I've learned everything so far via Google and Youtube videos but unable to finish this last part for several weeks. My office wants to utilize Sharepoint as its primary database for requests and document storage. Please I need help ASAP.

Step 1 - MS Form (Flow)

Step 2 - SP List (Flow) 

[ScottShearer]

@mhdukes1 

Can you tell me why you are usings a SharePoint HTTP action?

[mhdukes1]

I need the email to come from a "No Reply" email not my address. Hence, I'm using the SharePoint HTTP action.

[mhdukes1]

@ScottShearer The flow works if I'm testing the HTTP action. However, the next time it's triggered it loses the dynamic content and doesn't send an email. The results show ["] as if the content was never there. At this point, I'm only testing with one email address. However, I need it to go to three people. If I can get this figured out, the flow will automate our request process.

[ScottShearer]

@mhdukes1 

Have you considered using a Send an Email action or a Send an email from a shared mailbox action?

[mhdukes1]

@ScottShearer yes I tried those options but don't have permissions.

[ScottShearer]

@mhdukes1 

Are you trying to send the email as someone other than yourself?

If you are sending from a shared mailbox, do you have permissions to send from the shared mailbox?

[mhdukes1]

@ScottShearer I'm trying to send the email from the Legal Department. However, it won't let me send 'To' atty 1, atty 2 and staff. Each entity has a different people picker column in my SP list so that an email can be sent. Right now while testing, I'm all three entities so should receive all the emails but not, only one if lucky.

[mhdukes1]

@ScottShearer based on my research multiple emails call for an array. I cannot figure out what to use so that all three entities get an email. I've read initialize variable, compose, select, join option, etc. but somehow missing something. I've so confused at this point 

[ScottShearer]

@mhdukes1 

My apologies if I am not understanding your issue.

If you are using a Send an Email action you won't need an array - just a semicolon delimited string

If you you want 1 email with multiple recipients, you can simply reference each person or group column email.  If there is more than one to: or CC:, just put a semicolon between them.

If any of the person or group columns allow for multiple selections:

• Create a string variable
• Loop through the email addresses
• Use an append to string variable action to add each email address and add a semicolon between them.
• Use the variable for to: cc: as appropriate.

[mhdukes1]

@ScottShearer I tried using the semicolon so all three users are added and received the following error. My email address is in sharepoint so this should've worked. 

{"odata.error":{"code":"-2130242040, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The e-mail message cannot be sent. Make sure the e-mail has a valid recipient."}}}
clientRequestId: 8bbe5b31-667b-45af-81bb-c2035d2d5953
serviceRequestId: b0b688a0-80f9-d000-c030-dd167507dda7

In short, my problem is the email won't send consistently to one user nor when sending to all three users.

Can you send me a screenshot of the other option?

[mhdukes1]

@ScottShearer can I use the string variable with "Send an HTTP request to Sharepoint? I still received an error.

The execution of template action 'Apply_to_each_3' failed: the result of the evaluation of 'foreach' expression '@variables('Emails')' is of type 'String'. The result must be a valid array.

I tried to implement your instructions here but missed something.

[mhdukes1]

@ScottShearer error keeps advising "result must be valid array". 

[mhdukes1]

@ScottShearer did I loop through the address correctly? I don't think so and believe that may be the problem. Can you send a screenshot of this step?

[mhdukes1]

@ScottShearer unfortunately, I'm still having issues sending one email to the three individuals in my separate people columns. As I mentioned before, I need to use the "send http request to sharepoint". I need help converting the emails into an array variable then loop to attached in the body. I've tried your suggestion and researched everything. Please help with a screenshots. I'm trying but definitely missing something. 

[eagutierrez1]

I was wondering if you figured out a away? I am also trying to do some thing similar. I am using msForms as a trigger - when a form is completed, then have the send an http request to sharepoint email up to three users. It only works with one email address as you have stated. I'm new to all of this.

[ahleonar]

@mhdukes1 @eagutierrez1 

Sending an email with the HTTP connector has a slightly different way of separating email addresses as the other send an email connectors, instead of using a semicolon use a comma. Each email will also need to be surrounded by quotes. The format should be as noted below:

"To": {"results":["user1@domain.com","user2@domain.com","user3@domain.com"]}

[mhdukes1]

@ahleonar no I never figured it out. I became frustrated and just left it alone. Now I'm back and still need to send an email via HTTP connector. Any suggestions?

[mhdukes1]

@ahleonar does this work with dynamic content? I recreated the list with multiple columns of approvers: Dept, Legal and Staff. The number of approvers varies in each column. I used Compose, Select and Join to get the emails in string. However, the Send HTTP doesn't capture each email in quotation marks as your post mentioned. Any idea how to resolve this?

[ahleonar]

@mhdukes1 

Yes you can, it will require some string manipulation but it is possible. You will need to do 2 things:

1) Append your contacts using dynamic content to a string variable like this (screenshot below too)

"{Dynamic Content for email address}",

Doing it this way will format your emails address to be properly sent to your For or CC recipients with quotes and a comma separator to separate each email address.

2) Secondly, and this is very important otherwise you will get an error message "Not a well formatted JSON stream". You will need to remove the last comma for the string of correctly formatted email string you just created.

This is necessary because a comma will always be added to every email address and look like this "email1@domain.com","email2@domain.com", "email3.domain.com", <- note the extra comma here. 

To fix this you will need to write a substring, sub function. This function will remove the last character from the string you just created. You can copy and paste this formula just add your variable name and remove the curly brackets. 

substring(variables('{YourVariableName}'), 0, sub(length(variables('{YourVariableName}')),1))