Create a table using Excel fields and input it in an email

(0) Share

Report

Posted on by Json123

133

Hi,

I am trying to grab specific fields from an Excel, create a table, and then input it in a body of an email.

For more background, I have an excel sheet where if an email address is listed, a notification will be sent out to that user. However, the email address can be listed more than once. I need to send out just ONE email with all of the rows' information with that email address.

So for the example above, an email like below should get sent:

I was able to create this using the web version, but due to some limitations, I have to use the desktop version.

Any help would be appreciated.

Thanks!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (13)

Answers (0)

UK_Mike on at

Like (0)

Report
Copy link

Link copied!

Perhaps use Excel to remove the duplicates in the first instance ?

Was this reply helpful? Yes No
Json123 133 on at

Like (0)

Report
Copy link

Link copied!

Hi,

The duplicates aren't really the issue (I'm able to to that in the automation). Really, what I want is to pull the information from Excel and put it in the body of the email. However, 1 email per 1 email address listed in the Excel with ALL the information it has consolidated.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report
Copy link

Link copied!

Yes but if you have 2 identical email addresses are the row values the same ?

Was this reply helpful? Yes No
Json123 133 on at

Like (0)

Report
Copy link

Link copied!

No, the row values are different.

So it should send ONE email with a table of the different values. Please see the picture below.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report
Copy link

Link copied!

Ahh thats where you lost me, initially you said all of the rows value, as in single row but you meant table, gotcha.

Was this reply helpful? Yes No
Json123 133 on at

Like (0)

Report
Copy link

Link copied!

Sorry about that. I see how that's confusing. I just updated it as plural

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report
Copy link

Link copied!

So its the whole table ? no other conditions ?
Like I said excel / power query can remove the duplicate email addresses and put them into a wee table same sheet or other sheet, for each email send table ?

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Copy link

Link copied!

If you have the freedom to sort by email, do so.

Here is the logic after launching excel and sorting:
Get First Free Row
Set %PreviousEmail% to %''%
Loop 2 (assuming a title row) to %FirstFreeRow - 1% increment of 1
'We will loop through each row with %LoopIndex% as the row number and grab the email from the current row and the next row in two separate variables into %CurrentEmail% and %NextEmail
If %LoopIndex% > 2
Read from Excel at D and %LoopIndex - 1%
End(If)
If %CurrentEmail% 'does not equal' %PreviousEmail%
Capture the current row number as the %StartRow%
End(If)
If %CurrentEmail% 'does not equal' %NextEmail%
Send Email with Excel extracted from A and %StartRow% to D and %LoopIndex% NOTE: LoopIndex is the EndRow
End (if)

Was this reply helpful? Yes No
yoko2020 495 on at

Like (1)

Report
Copy link

Link copied!
@Json123
This is an example PAD code, you can copy and paste in PAD.
This is using sql query processing and outlook desktop for sending email

Requirements:
- Office365 64 bit installed.
- Outlook desktop configured
- Download sample excel file from here

PAD Code :

# ----------------CHANGE FILE PATH BELOW---------- SET FileExcel TO $'''C:\\Users\\abc\\Downloads\\Book1.xlsx''' SET tableStart TO $'''<div><table border=\"1\" cellpadding=\"2\">''' SET tablehead TO $'''<tr><th>Number</th><th>Name</th><th>Amount</th><th>Issues</th><th>Need to send email</th><th>Email Address</th></tr>''' Outlook.Launch Instance=> OutlookInstance SET ConnString TO $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%FileExcel%;Extended Properties=\"Excel 12.0 Xml;HDR=yes\";''' Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: ConnString Statement: $'''Select DISTINCT [Email Address] from [Sheet1$] ''' Timeout: 30 Result=> QR_Email LOOP FOREACH CI_Email IN QR_Email SET tableBody TO $'''%''%''' SET EmailAddress TO CI_Email['Email Address'] Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: ConnString Statement: $'''Select * from [Sheet1$] where [Email Address] = \'%EmailAddress%\' ''' Timeout: 30 Result=> QR_Data LOOP FOREACH CI_Data IN QR_Data SET tableBody TO $'''%tableBody%<tr><td>%CI_Data['Number']%</td><td>%CI_Data['Amount']%</td><td>%CI_Data['Name']%</td><td>%CI_Data['Issues']%</td><td>%CI_Data['Need to send email']%</td><td>%CI_Data['Email Address']%</td></tr>''' END SET tableEnd TO $'''</table></div>''' Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''your@email.com''' SendTo: $'''your@email.com''' Subject: $'''test''' Body: $'''%tableStart% %tablehead% %tableBody% %tableEnd%''' IsBodyHtml: True END

Was this reply helpful? Yes No
Json123 133 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I'm not understanding where you're getting the "Set %PreviousEmail% to %''%"

Is %PreviousEmail% the %EmailAddress%

Was this reply helpful? Yes No