Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Copy Excel Range in table format to Calculation Stage

Go to answer
sandeshtope
Level 5

‎09-08-24 05:13 AM

Hi All ,

I have a use case where I need to copy a specific range from an Excel table and store it in a calculation stage, including in my email body. Could someone guide me on how to achieve this in Blue Prism?

0 Likes
1 BEST ANSWER

Helpful Answers

Go to answer
Mohamad_747
MVP
In response to sandeshtope

‎13-08-24 05:58 AM

Hi @sandeshtope 

Then bring 3 data items of type text html_body html_middle and html_end 

Your flow should look like this : 

Mohamad_747_0-1723524501786.png

in html_body put this : 

<html>
<style>
table {
border-collapse: collapse;
}

th, td {
border: 1px solid black;
}


</style>
<body>

<p>Hi teams,</p>
<p>Please find the below table FYR.</p><br>

<table style="width:100%">
<tr><th>NAME</th><th>PRENAME</th><th>CONTRACT</th></tr>

 

 

In html_middle put this : 

<tr><td>***NAME***</td><td>***PRENAME***</td><td>***CONTRACT***</td></tr>

 

in html_end put this : 

</table>
<p>Regards.</p>
<p>Blue Prism</p>
</body>
</html>

 

in Set_mail_body calculation stage put this : 

[html_body]&Replace(Replace(Replace([html_middle],"***NAME***",[Data.Name])
,"***PRENAME***",[Data.Prename])
,"***CONTRACT***",[Data.Contract])

like this : 

Mohamad_747_1-1723524701770.png

 

 

in Close mail calculation stage put this : 

[html_body]&[html_end]

like this : 

Mohamad_747_2-1723524765086.png

 

in send mail put this  : 

 

Mohamad_747_3-1723524820488.png

when you send the mail you will receive this : 

Mohamad_747_4-1723525011212.png

 

PS : if you want to extract a part of your data in the excel sheet you can use this action : 

Mohamad_747_5-1723525069297.png

 

otherwise you can use filter or decision stage 🙂

please tell me if everything is good for you 

 

 

 

 

 

View answer in original post

6 REPLIES 6

Go to answer
Mohamad_747
MVP

‎09-08-24 09:50 AM - edited ‎09-08-24 09:53 AM

Hi @sandeshtope  

I can help you but we will opt for another approach. What I propose is to retrieve the range of the Excel file then we make a loop to build the body of your email. Could you tell me if this suits you? If yes could you put more details on the file?

Go to answer
Parthiban_Viatris24
Level 5

‎09-08-24 10:01 AM

You can also consider using an Excel Macro as an alternative method. It can effectively fulfill the requirements you've outlined in your post.

Parthiban A
0 Likes

Go to answer
Mohamad_747
MVP

‎10-08-24 12:23 PM

 

 

 

@sandeshtope 

Sorry I thought I posted the answer yesterday but looking again I didn't see the answer published (bad connection)
Let's suppose we have this excel file

 

Let's suppose I want to put my contracts in an email

 

Mohamad_747_1-1723288516210.png

 


1. Bring a data item of type text and  call it html_single_row , set its value to : 

<p>***CONTRACT***</p>

2. Bring a data item of type text and  call it Mail Body ,  set its value to : 

<style>
table {font-family: Verdana; font-size: 10pt; width: 100%}
td {border: 1px solid gray; border-collapse: collapse; padding: 5px}
</style>
<div style="font-family: Verdana; font-size: 10pt">
<p>Hello, </p>

<p>Please find below your contracts. </p></br>

***FILESSUCCESS***

<p>Regards</p>
<p>Blue Prism Robot</p>
<p><i>***Please dont reply to this automatic message***</i></p>
</div>

 

3.Open your excel file via MS EXCEL VBO and bring your data like this : 

Mohamad_747_3-1723288728387.png

 

4. Make a loop on your wanted column. In my cas it's Contract

 

your flow should look like this : 

Mohamad_747_4-1723288772420.png

in Set Contracts put this : 

[Contracts]&Replace([html_single_row],"***CONTRACT***",[Data.Contract])

see below the screen shot 

Mohamad_747_5-1723288876758.png

Final step bring Ms Outlook Email VBO 

and put this 

Mohamad_747_6-1723288919440.png

Replace([Mail Body],"***FILESSUCCESS***",[Contracts])

you will receive this mail at the end 

Mohamad_747_7-1723288978998.png

 

Regards

 

 

Go to answer
sandeshtope
Level 5

‎13-08-24 03:29 AM

Thanks @Mohamad_747  for your answer but below is my requirement

Lets consider My input excel looks like this :

sandeshtope_0-1723515852100.png

And The message body I am looking for looks like this :

 

Hi Team ,

Please find the below table FYR

 

NAMEPRENAMECONTRACT
AQabcd
BWefgh
CEhijk
DRlmno

Regards,

Blue Prism Robot

Please don't reply to this automatic message

0 Likes

Go to answer
Mohamad_747
MVP
In response to sandeshtope

‎13-08-24 05:58 AM

Hi @sandeshtope 

Then bring 3 data items of type text html_body html_middle and html_end 

Your flow should look like this : 

Mohamad_747_0-1723524501786.png

in html_body put this : 

<html>
<style>
table {
border-collapse: collapse;
}

th, td {
border: 1px solid black;
}


</style>
<body>

<p>Hi teams,</p>
<p>Please find the below table FYR.</p><br>

<table style="width:100%">
<tr><th>NAME</th><th>PRENAME</th><th>CONTRACT</th></tr>

 

 

In html_middle put this : 

<tr><td>***NAME***</td><td>***PRENAME***</td><td>***CONTRACT***</td></tr>

 

in html_end put this : 

</table>
<p>Regards.</p>
<p>Blue Prism</p>
</body>
</html>

 

in Set_mail_body calculation stage put this : 

[html_body]&Replace(Replace(Replace([html_middle],"***NAME***",[Data.Name])
,"***PRENAME***",[Data.Prename])
,"***CONTRACT***",[Data.Contract])

like this : 

Mohamad_747_1-1723524701770.png

 

 

in Close mail calculation stage put this : 

[html_body]&[html_end]

like this : 

Mohamad_747_2-1723524765086.png

 

in send mail put this  : 

 

Mohamad_747_3-1723524820488.png

when you send the mail you will receive this : 

Mohamad_747_4-1723525011212.png

 

PS : if you want to extract a part of your data in the excel sheet you can use this action : 

Mohamad_747_5-1723525069297.png

 

otherwise you can use filter or decision stage 🙂

please tell me if everything is good for you 

 

 

 

 

 

Go to answer
Mohamad_747
MVP

4 weeks ago

Hi @sandeshtope 

Any update ?

0 Likes