Create Draft emails based on Information in two Excel files

Hi team,

I have two mock-up data files. Data Dump file has all the information required for the email attachment.

Mapping file has details of from, to, subject, attachment password for different entities.

I am looking to create multiple draft emails in outlook. One email for each unique combination of Region, Client Name and Counterparty in Data Dump file.

For example, one email for Region = Belgium, Client Name = Minerva and Counterparty = Nova. Now, there are 3 line items for this combination in Data Dump file. An email has to be created based on the information in mapping file for this Region, Client Name and Counterparty combination. Corresponding columns in Mapping file are Location, Client and Broker for these columns. So, an email has to be created with to as “hf_otc@nova.com”, cc as “pranee07@gmail.com”, From as “pranee007@gmail.com” and Subject as “Belgium-request for statement-as of date-Nova”. Attachment should contain the 3 line items from Data Dump file (columns A-G) and it has to be password protected by password “MhsYjs78”.

Body of the email changes based on Comment in Data Dump file.

Initial Email : Body - Please find attached and provide response

1st Chaser : Body - Following up on the below

2nd Chase: Body - Humble follow-up again on the below.

Is this something which UiPath Studio can do?

Mapping File.xlsx (10.6 KB)
Data Dump.xlsx (10.0 KB)

For starters you could save the Data Dump data as a UiPath Datatable “DataDump_dt” and then make a “DataDump_Filtered_dt” where you only keep unique rows based on the Region, Client Name and Counterparty.

Assign Activity: DataDump_Filtered_dt = (From row In DataDump_dt.AsEnumerable() Group row By Key = New With {Key .Region = row.Field(Of String)(“Region”), Key .ClientName = row.Field(Of String)(“Client Name”), Key .Counterparty = row.Field(Of String)(“Counterparty”)} Into Group Select Group.First()).CopyToDataTable()

I am not sure i understood the rest of the flow, but at least this gets you a list where you only keep 1 row per unique combination of Region, Client name and Counterparty to work with :slight_smile: