Answered

Convert the query result of sql to html table for e-mail

(0) Share

Report

Posted on by HaBiMuc

Hello everyone,
unfortunately i have not found an entry for my problem in the forum. there are similar questions, but unfortunately they have not helped me.

i use desktop power automate and would like to export the result of an sql query (queryresult) into an html table in a flow. this table should then be sent as an e-mail.

the data table result looks like this

ID | Pub | date | pm |
1 | apple | 2023-01-01 | john |

How can I implement this?

Thanks for your help.

HaBiMuc

Categories:

Power Automate Desktop

I have the same question (0)

All responses (22)

Answers (4)

Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (1)

Report

@HaBiMuc
I hope you're doing well! These scenarios are quite intriguing for me to resolve 🙂

To accomplish this, begin by initializing certain variables with HTML-formatted table and table column names. Once we receive the result from the SQL database, we can utilize a forEach loop to expand the string variable values by incorporating table row values, as illustrated in the following code.

Sample Email:
Code(Copy and paste the below code into your PAD flow):
SET HTMLTable TO $'''<table style=\"border: 1px solid black;\">
<tr>
<th style=\"border: 1px solid black; padding: 8px; text-align: left; background-color: #f2f2f2;\">ID</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">Pub</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">date</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">pm</th>
</tr>
<tr>'''
Database.Connect ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Automation360-Database;Data Source=DESKTOP-NAME''' Connection=> SQLConnection
@@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Fruits;Data Source=DESKTOP-AVANTHI\\SQLEXPRESS''' Statement: $'''SELECT * FROM [Fruits].[dbo].[Stocks]''' Timeout: 30 Result=> QueryResult
LOOP FOREACH CurrentItem IN QueryResult
SET HTMLTable TO $'''%HTMLTable%
<tr>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[0]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[1]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[2]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[3]%</td>
</tr>'''
END
SET HTMLTable TO $'''%HTMLTable%
</table>'''
Outlook.Launch Instance=> OutlookInstance
Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''deenucse@gmail.com''' SendTo: $'''deenucse@gmail.com''' Subject: $'''Hello Message''' Body: $'''Hello, Hope your are doing good.
Below is the table:
%HTMLTable%''' IsBodyHtml: True

Thanks,
Deenu

-----------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up. Thank you.

Was this reply helpful? Yes No
HaBiMuc 50 on at

Like (1)

Report

Thanks, Deenu for your help. I'll try this way.
Regards, Habimuc

Was this reply helpful? Yes No
HaBiMuc 50 on at

Like (0)

Report

Hi @Deenuji , while trying to convert into html I get a new error. I think the problem is, that the query result is empty because no dataset (which ist correct for the special query at this time).

Error: "Index „1“ is out of range"

Any idea for error handling in case of empty sql result?

Thanks, HaBiMuc

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (1)

Report

@HaBiMuc Could you please share your workflow for my further assessment pls?

Thanks,
Deenu

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (0)

Report

Hi @HaBiMuc

Any updates? If my previous responses have helped to resolve your original reported issues, please mark them as the preferred solution. If you found my response helpful, please give it a Thumbs Up. Thank you.

Thanks,
Deenu

Was this reply helpful? Yes No
HaBiMuc 50 on at

Like (0)

Report

Hi @Deenuji , sorry I was out of office. Here my anomized flow. Sorry that is in German. At line 6 I get the error "Der Index „1“ liegt außerhalb des zulässigen Bereichs." (= Index "1" is out of range") if the query have no result but the header of the table.
Thanks, HaBiMuc

Was this reply helpful? Yes No
Deenu 3 on at

Like (0)

Report

@HaBiMuc - Thanks for sharing your details

Was this reply helpful? Yes No
Verified answer

Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (1)

Report

@HaBiMuc
Thanks for the details.

I have included the requested logic in the below code by adding some if condition action for validating query result row count.

Code:
Database.Connect ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Fruits;Data Source=DESKTOP''' Connection=> SQLConnection
@@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Fruits;Data Source=DESKTOP-AVANTHI\\SQLEXPRESS''' Statement: $'''SELECT * FROM [Fruits].[dbo].[Stocks1]''' Timeout: 30 Result=> QueryResult
SET QueryResultCount TO QueryResult.RowsCount
Outlook.Launch Instance=> OutlookInstance
# Fetch Queryresult from SQL and if query not returns 0 rows then write HTML table for results and embedded that table in outlook message
IF QueryResultCount > 0 THEN
SET HTMLTable TO $'''<table style=\"border: 1px solid black;\">
<tr>
<th style=\"border: 1px solid black; padding: 8px; text-align: left; background-color: #f2f2f2;\">ID</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">Pub</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">date</th>
<th style=\"border: 1px solid black; background-color: #f2f2f2;\">pm</th>
</tr>
<tr>'''
LOOP FOREACH CurrentItem IN QueryResult
SET HTMLTable TO $'''%HTMLTable%
<tr>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[0]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[1]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[2]%</td>
<td style=\"border: 1px solid black; background-color: #f2f2f2;\">%CurrentItem[3]%</td>
</tr>'''
END
SET HTMLTable TO $'''%HTMLTable%
</table>'''
Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''deenucse@gmail.com''' SendTo: $'''deenucse@gmail.com''' Subject: $'''Hello Message''' Body: $'''Hello, Hope your are doing good.
Below is the table:
%HTMLTable%''' IsBodyHtml: True
ELSE
# Fetch Queryresult from SQL and if query returns 0 rows then send email without table
Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''deenucse@gmail.com''' SendTo: $'''deenucse@gmail.com''' Subject: $'''Hello Message''' Body: $'''Hello, Hope your are doing good.''' IsBodyHtml: True
END

Please try the above logic and let me know if this make sense to you

Thanks,
Deenu

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (0)

Report

@HaBiMuc
If my previous responses have helped to resolve your reported issues, please mark them as the preferred solution. If you found my response helpful, please give it a Thumbs Up. Thank you.

Thanks,
Deenu

Was this reply helpful? Yes No
HaBiMuc 50 on at

Like (0)

Report

Hi @Deenuji ,
sorry for my late reply, but it is very difficult for me, to translate your flow into my german power automate version.
First stop was on this:
How can I set this operation SET HTMLTable TO $''... - it is a new variable or make a new html table?
Maybe I change the language settings of PWD for easier work.
Regards, HaBiMuc

Try to set a new variable, but I'm not sure if this is correct:

Was this reply helpful? Yes No