Excel to Email

[Anonymous2000]

Hi. I wonder if anyone can help. I have almost cracked it several ways but am always 1 piece missing. Basically I have a large spreadsheet like this:

 

[TABLE=width: 337]

[TR]

[TD]Organisation[/TD]

[TD]Device/User[/TD]

[TD]Contact[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Cmp1[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Cmp2[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Cmp3[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Usr1[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Usr2[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 1[/TD]

[TD]Usr3[/TD]

[TD]person@Org1[/TD]

[/TR]

[TR]

[TD]Org 2[/TD]

[TD]Cmp1[/TD]

[TD]person@Org2[/TD]

[/TR]

[TR]

[TD]Org 2[/TD]

[TD]Usr1[/TD]

[TD]person@Org2[/TD]

[/TR]

[TR]

[TD]Org 2[/TD]

[TD]Usr2[/TD]

[TD]person@Org2[/TD]

[/TR]

[TR]

[TD]Org 2[/TD]

[TD]Usr3[/TD]

[TD]person@Org2[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Cmp1[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Cmp2[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Cmp3[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Cmp4[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Cmp5[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Usr1[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Usr2[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Usr3[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 3[/TD]

[TD]Usr4[/TD]

[TD]person@Org3[/TD]

[/TR]

[TR]

[TD]Org 4[/TD]

[TD]Cmp1[/TD]

[TD]person@Org4[/TD]

[/TR]

[TR]

[TD]Org 4[/TD]

[TD]Usr1[/TD]

[TD]person@Org4[/TD]

[/TR]

[TR]

[TD]Org 4[/TD]

[TD]Usr2[/TD]

[TD]person@Org4[/TD]

[/TR]

[/TABLE]

 

I need to send 1 email to each org (so 4 emails in the example) but include all the objects in column 1 relevant to their organisation.

 

In Power Automate I can get it to send emails but it sends 1 per line.

In Excel I have got the contents of all the cells for each org in 1 cell using textjoin and Char(10). I get all of the correct items in a nice list but when I pass this to outlook via Power Automate it loses the formatting and I end up with a link of text. Also in Excel I have used the Hyperlink command - again I can get 1 email per line but it doesn't work if I use the single cell with all the lines in.

 

As I say - it feels like I am missing one piece of the puzzle.

 

Any help gratefully received.

 

Thanks

[andy_b]

Dunno if this helps: https://powerusers.microsoft.com/t5/General-Power-Automate/New-Lines-in-excel-cell-does-not-support-in-Power-Automate/td-p/1657228

 

Previously I have used a command line program (Send-It-Quiet Command Line sendmail tool) with excel to construct the command to send emails, but that is probably quite archaic these days.

[Arthur]

You should be able to do what you need with the following PowerShell modules...

 

ImportExcel + Mailozaurr + PSWriteHTML

 

More info: https://evotec.xyz/easy-way-to-send-emails-using-microsoft-graph-api-office-365-with-powershell