Studio X - Send various emails with Filtered Dynamic Data from Excel

Help StudioX
1

Hello,

I am attempting to automate a task which downloads a client report and uses the excel information to send various emails to different recipients.

In this case, column K will have all the unique IDs associated to the recipients. I need to be able to filter by column K and send various email with the data from the different IDs the report will have.

  • The Unique IDs from column K will always have 6 digits
  • Each Unique ID may contain 1 or more rows of data
  • An email will need to be sent with the data for each Unique ID (data Column C to Column P)
  • The number of filtered results (Unique ID) may vary

Is anyone able to assist with screenshots from StudioX of what steps I should take to copy all applicable rows from each Unique ID result so that I can add to different email messages?

2

Hi @Fabs

If possible could you share the Excel file.

Regards

3

Hello @Fabs

  1. Excel Application Scope:

    • Use the “Excel Application Scope” activity to read the Excel file.

  2. For Each Row:

    • Use the “For Each Row” activity to iterate through the rows in the DataTable.

    Inside the loop:
    3. Compose Email:

    • Use the “Compose Email” activity to create the email message. Use variables or expressions to include data from the current row.
    1. Send Outlook Mail:
      • Use the “Send Outlook Mail” activity to send the email. Configure the recipients, subject, and attach relevant data from the current row.

    End of the loop.

  3. End Excel Application Scope:

    • Close the Excel application scope.

Thanks & Cheers!!!

4

Unfortunately I’m having issues to upload the file.

How exactly would I be able to compose an email based on filter results of a specific day for different clients?

5

@Fabs

Assign activity:
filteredRows = clientData.Select(“Client = ‘ClientName’ AND Date = ‘SpecificDate’”)

Compose Email activity:
Body: “Dear Client, here is the information for the specific day:” & Environment.NewLine & String.Join(Environment.NewLine, filteredRows.Select(Function(row) row(“Column1”).ToString()))

Send Email activity:
Configure the email details and use the composed body.

6

Each day I’ll get a report similar to the example below. I need to be able to filter by column K for each different ID result and compose an email for each filter result. Please note that some days I may have more IDs from column K.

With this example, I would need to send 3 emails as there are 3 different IDs from column K. For this report, one email would have 1 row, another 2 rows, and the 3rd email 15 rows.

Payers Name Amount of Direct Debit Original Value Date of Direct Debit File PAYPAL CLIENT Name Mandate Reference Indem Claim Bank Ref Reason Code Reason Code Meaning Service User Number Sequence Number Paying Bank Name Penny Deposit Date First Deposit Amt Second Deposit Amt
NAME SURNAME 10.99 15/11/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 123456 2023122703A970658737 BANK NAME 12 27/03/2018 1 0
NAME SURNAME 24.99 15/06/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 123456 2023122703A970658738 BANK NAME 12 27/03/2018 1 0
NAME SURNAME 12.69 09/08/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888296 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 8.99 19/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 2.49 31/03/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888296 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 10.99 19/10/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 14.99 18/09/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 10.99 20/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 23.01 20/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 1.99 11/10/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 7.78 20/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 10.99 21/11/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 10.99 21/08/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888296 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 7.55 16/11/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 14.99 25/09/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 8.99 12/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 10.99 12/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 456789 2023122903A971888295 BANK NAME 45 21/11/2017 1 0
NAME SURNAME 268.25 13/12/2023 CLIENT 1ABJ11111FX7W DDICABCLLLLL01023819 5 No Instruction held 789456 2023122903A971769301 BANK NAME 789 22/11/2021 1 0
7

Hi @Fabs

You have not mentioned the column names. Please Mention that too. If possible share the data in an excel file too.

Regards

8

I have updated the table with the column names. The data in this case is not real, but it was added as the same format.

9

Okay. Thank you. @Fabs

10

Hi @Fabs

Check out the zip file. Hope this meets your requirement.

image
image854×2245 81 KB

Forum Task.zip (297.4 KB)

Hope it helps!!

1 Like
11

Hi @Parvathy

I can’t open the file with Studio X. Could you please share the 2 variable so I can manually add to my project and see if it works for what I need?

Otherwise, are you able to provide the xaml file for Studio X?

12

Hi @Fabs

Check out the xaml file. It should help you.
Main.xaml (20.0 KB)

Regards

13

@Parvathy I had to download Studio (not Studio X) and it really worked! Thank you!

Would you be able to help in amending the shared zip file so that the table also includes the headers?

I also need the Subject to have: “DDIC Report ID” + BANK NAME from Paying Bank Name Column + CurrentDate"

  • I’ve updated the table above and deleted the first 2 columns which weren’t needed
1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.