How to send one email to different people in an excel cell

[sam123123123]

Hello guys,

I would like to send an email with the same HTML table to multiple people. For example, one Excel cell has two email addresses, I am wondering how to create a flow that retrieves two email addresses from one Excel cell and sends the email with the same HTML table. Thanks! Really appreciate!!

[Chriddle]

In the Send Email connector, if you use cell D2 value as To, the email will be sent to both.

[sam123123123]

Hello Chriddle, 

Thanks for the response. I know the email connector can do it.

The following pictures are my flow, hope it's not difficult to achieve it. Right now, I just send one HTML table to one person; but I hope to retrieve the email address like the original picture, to send the same HTML to two people or three. Thanks

 

 

 

[Chriddle]

This requires further explanation 😉

Maybe you should give an example: Who should receive an email with what data?

[sam123123123]

Hello Chriddle

For exmaple, I run the flow by dragging an Excel file to Sharepoint, and then the flow retrieves the data and unions repetitive emails and then sends an HTML table related to John's sales to John. In this flow, I just send one HTML table to one person. But I want to send the same sales HTML table to not only John but also others.

Hope the picture can clarify my ideas. Really thanks!

 

[Chriddle]

Where are Elon and Peter from? How do you know that the email to Sam should also be sent to Elon?

Is this defined in a separate list?

[sam123123123]

Yes, it's defined in a separate list. Thanks!

[sam123123123]

Hello Chriddle,

Hope this picture is useful. I use Vlookup to locate the email address. 

 

[Chriddle · Accepted solution]

I would create the flow like this:

ExcelData is a Compose with the rows from Excel

[
	{"Date": "2023-01-01", "sales": 200, "Name": "Sam", "Email": "sam@example.com"},
	{"Date": "2023-02-01", "sales": 300, "Name": "Sam", "Email": "sam@example.com"},
	{"Date": "2023-03-01", "sales": 400, "Name": "Sam", "Email": "sam@example.com"},
	{"Date": "2023-01-02", "sales": 200, "Name": "John", "Email": "john@example.com"},
	{"Date": "2023-01-03", "sales": 200, "Name": "Marry", "Email": "Marry@example.com"},
	{"Date": "2023-01-04", "sales": 200, "Name": "altman", "Email": "altman@example.com"},
	{"Date": "2023-01-03", "sales": 200, "Name": "John", "Email": "john@example.com"},
	{"Date": "2023-01-04", "sales": 300, "Name": "John", "Email": "john@example.com"}
]

EmailData is a Compose with the data from the separate email list

[
	{"Email": "sam@example.com", "OtherEmail": "Elon@example.com"},
	{"Email": "john@example.com", "OtherEmail": "Peter@example.com"},
	{"Email": "Marry@example.com", "OtherEmail": null},
	{"Email": "altman@example.com", "OtherEmail": null}
]

Select

From (an array of unique emails from ExcelData)

union(
	xpath(
	    xml(
		json(
		    concat(
			'{"Root":{"Item":',
		       outputs('ExcelData'),
			'}}'
		    )
		)
	    ),
	    '//Email/text()'
	),
	json('[]')
)

 Map Email (concats the email with the according "OtherEmail" fromEmailData)

item()

;

first(	
	xpath(
	    xml(
		json(
		    concat(
			'{"Root":{"Item":',
		       outputs('EmailData'),
			'}}'
		    )
		)
	    ),
	    concat('//Item[Email="', item(), '"]/OtherEmail/text()')
	)
)

DataNodes (an array of XML nodes belonging to the current email)

xpath(
    xml(
	json(
	    concat(
		'{"Root":{"Item":',
	       outputs('ExcelData'),
		'}}'
	    )
	)
    ),
    concat('//Item[Email="', item(), '"]')
)

Apply to each loops over the output of the Select

body('Select')

Select 2 translates the XML nodes to Power Automate objects

From

items('Apply_to_each')['DataNodes']

 Map

json(item())['Item']

Create HTML table creates the HTML table from the objects above

body('Select_2')

Compose is just to show how to use the values in the "Send an email"

To

items('Apply_to_each')['Email']

HTML table

body('Create_HTML_table')

The Compose's output

First loop

Second loop

etc.

[sam123123123]

Hello Chriddle,

Thanks for the solution. Howeever, the "select 2" seems like cannot get anything out. And Also with the HTML table.Thanks!

Flow output

Whole flow:

Inside the flow:

[Chriddle]

But the output of the first Select looks as expected? Probably the issue starts there.

[sam123123123]

Hello Chriddle,

it looks like this, an empty array?

ExcelData:

Email data:

[Chriddle]

Show your expression in Map DataNodes

[sam123123123]

[Chriddle]

Since your data comes from a "List rows present in a table" (not a Compose),

it should be outputs('ExcelData')?['body/value'] and not outputs('ExcelData') in this expression.

[sam123123123]

Ok thanks. It works now.

one more question. if I want to send more than two emails, is it true that I can change the following expression in "concat" after other email? If yes, how should I change it? Reall thanks, or is the way I change it correct? Thanks

first(

xpath(

    xml(

json(

    concat(

'{"Root":{"Item":',

       outputs('EmailData'),

'}}'

    )

)

    ),

    concat('//Item[Email="', item(), '"]/OtherEmail/text() | //Item[Email="', item(), '"]/OtherEmail2/text()')

)

)

[Chriddle · Accepted solution]

No, this does not work.

One possibiliy is to just write several emails to the OtherEmail column: email1@example.com;email2@example.com

Another option is to add an additional expression to the Map Email like the one for OtherEmail.

[sam123123123]

OK thanks. It works.