How to collate excel data by department to email to that department?

Help StudioX
,
1

I need to consolidate a few rows of excel data into 1 email to send out base on the Department Description.
For example row 2 to 4 belonging to “Dept 1” will be in 1 email, row 5 to 6 belonging to “Dept 2” will be in another email and so on.

image
image971×188 8.63 KB

2
  1. Loop Through Data: Use the “For Each Row” activity to loop through the sorted DataTable.
  2. Check Department Change: Inside the loop, use an “If” activity to check if the value in the “Department Description” column has changed from the previous row. If it has, it means you’ve finished processing the previous department’s data. In this case, send an email for the previous department and reset the emailBody variable.
3

@Mav

Arrayvariable=dt1.AsEnumerable. Skip(1).Take (2).ToArray

Arrayvariable
=dt1.AsEnumerable. Skip(4).Take (2).ToArray

Cheers

4
  • Read Range into datatable (originalDT)
  • For Each Row in Datatable: originalDT.DefaultView.ToTable(True,“Department Description”)
    ** Filter Datatable originalDT → departmentDT; filter will be “Department Description” = CurrentRow(“Department Description”).ToString
    ** Do what you want with departmentDT to include it in the email (ie create HTML, save as CSV to attach, whatever you want)
    ** Send email
5

@Mav

Welcome to the community

please follow the steps

  1. Let the orginal datatable be dt and now use assign activity with distinctdt = dt.DefaultView.ToTable(True,"Deparment Descrription","Email address")
  2. Now use for each row in datatable on distinctdt
  3. Inside loop use filter datatable activity on dt and save output in filtereddt and filter is on "Deparment Descrription" value would be currentRow("Deparment Descrription").ToString
  4. After that use filterdt in your email and for email id you can use currentRow("Email address").ToString…Use create html content activity to convert the table to html table and use in email

Hope this helps

cheers

6

Hi, thanks for the steps!
I am not able to find the Assign activity. I am using StuioX 2022.10.5.

7

Hi @Mav

If you are using StudioX then use Set Variable activity

8

Hi @Mav

=> Use read range workbook activity to read the excel and store in a datatable. Let’s called the datatable variable name as Inputdt
=> After that use the filter datatable activity to filter the column Department which equals to Dept1. The output of filtered datatable activity is Filtereddt.
=> If you want to insert the Filterdt in the body of the mail, use create html content activity to insert the Filteredt in the body of mail. The output of create html content activity is Bodyhtml.
=> In send mail message activity you can pass the Bodyhtml variable in the body field, and in the properties check the Isbodyhtml option.

Hope it helps!!

9

@Mav

For studio x assign would be termed as set variable

Also if you are unable to see datatable operations or activities then in activities , click on filter and select developer

Cheers

10

But this only filters out Dept 1. How do I filter for the rest of the Dept?

11

I managed to filter out the remaining departments with another masterlist excel that contains all the department names and emails. However, if my masterlist contain more departments than my raw excel, a blank table and email will be created. How do I prevent blank email from being created and sent out?

12

Loop through just the unique departments:

  • For Each Row in Datatable → yourDT.DefaultView.ToTable(True,“Department Description”)
    ** Filter Data Table to get a datatable of just the current unique department in the loop
    ** Send email