Excel cell value to email body

Help Activities
, , , ,
1

Hi
I have an excel sheet 1 from cell B (name) and cell D (ID_no) would like to put them into email body.
EG. When it recognize New York is in US country then will write them in email A template with cell B2 and D2 text value in one row. when it recognize LA is in US country also write cell B3 and D3 text value in another row in email.

But when it recognize London is in UK country write them in email B template with cell B5 and D5 text value in one row.

image
image750×261 7.4 KB

I got the below flow chart…knowing something missing. please help, Thanks!

image
image788×618 16.6 KB

image

2

@koolrc812

If I understand correctly you want to send email for each country spearately with respective details.Please follow as below. Let us say data is in dt

  1. First you have to get the unique country details for that please create a datatable countrydt =dt.DefaultView.ToTable(True,{"country"})
  2. Now use for loop on this countrydt
  3. Inside the loop use a filter datatable and filter the dt to get all the rows related to the country and get data into filtereddt …in filter you have to give country equals currentrow("country") and in the filter options you will columns table…where you will keep only name and id_no
  4. Next activity inside loop will be a send email activity where you have option to provide html content and add the datatable to it

Cheers

3

Hi,

I recommned to use dictionary<String,String> to handle each template as the following, for example. Can you try this?

image
image777×861 25.5 KB

Sample20230110-2L.zip (9.2 KB)

Regards,

4

Hi
what do I need to change to in the below box?

image

5

Hi,

This is for String.Format method. Please see the following document in details.

More specific, {0} will be replaced with 1st item of the 2nd argument of Format method.
{1} will be replaced with 2nd item of the 2nd argument of it.

image
image1458×550 53.9 KB

If you can share expected mail body, we might be able to write template string for it.

Regards,

6
  1. First you have to get the unique country details for that please create a datatable countrydt =dt.DefaultView.ToTable(True,{"country"}) ← generate data table activity? or assign activity?
  2. Im using Send mail activity. May i know how to put html in body part?

Thanks

7

@koolrc812

  1. It should be used in assignactivity…

Countrydt on left and remaining on right…it gets unique values from
The specified column and creates a datatable of it

2 . To create html content use create html content activity…you can check the video on how to add…the output can be used in mail body

Feel free to ask if youa re stuck

Cheers

8

Hi
i dont have this part as im using Send mail activity (do not have it Send Email activity)

image
image768×660 60.3 KB

Im want to download create HTML content and send email activity but cant find them in manage packages…

9

Hi @Yoichi
my template A mail body is

Hi,
Please help to prepare the related documents for the below cases.

name - New York, id_no - 123456, country - US
name - LA, id_no - 234567, country - US

email template B

Hi,
Please help to prepare the related documents for the below cases.

name - London, id_no - 456789, country - UK

Thanks

10

Hi,

  1. extract Date from excel.
    Even though excel only have yyyyMMdd, it write hours,minutes,seconds(00:00:00)
    I want to extract only yyyyMMdd. How could it be possible? Try preserve format in read range activity.
  2. Before for each row use a write text file activity and pass text as “” .so it will write a empty text on the text file.

You can also try this package for convert excel range to html table,

HTML Table from Excel with Formatting - RPA Component |

11

Hi,

Do you need to grouping? If so, can you try the following sample?

image
image1074×779 36.4 KB

Sample20230110-2Lv2.zip (9.2 KB)

Regards,

12

Its a grouping email.
may i please know after the for each activity, its ok the message box activity? any other activity after that? coz I can only see the below activities.
Do I do if condition as there will be 2 different email send (Template A with condition country - US and Canada), (Template B email with condition country - UK)? Thanks

image
image504×564 14.2 KB

13

HI,

I just downgraded and modified the above sample as the following. Can you try this?

image
image986×792 30 KB

Sample20230110-2Lv3.zip (9.5 KB)

Regards,

14

it shows Message box: The given key was not present in the dictionary.

15

HI,

Do you mean the above sample throws exception in your environment?
Can you share screenshot of the error?

Regards,

16

Hi @Yoichi

image
image1967×789 41.5 KB

17

Hi,

Did you use same workbook in the above zip file?
At least, it seems project name is not same as the above sample.

Regards,

18

Oh I rename it, cause would be easier for me to recognize it. should I rename it back to Main?

19

Hi,

It’s not relevant with xaml file name. Can you share your project files which error occurs as zip file? I’ll check it.

Regards,

20

@koolrc812

From activities select the filter icon and check the modern activities these all are the modern ones

Cheers