Sending an email to the employees who did not fill out the survey

Help Activities
,
1

I have two excel files. One file has name of all the employees in the company and another excel file has the name of the employees who filled out the survey. I need to send out the email to the employees who did not fill out the survey. Below are the files:

Excel1 (this has the name of the employee in the company)

Employee Name  email

ABC               ABC@test.com
DEF               DEF@text.com
XLD               XLD@est.com
PQR               PQR@text.com
HJD               HJD@test.com

Excel2 ( name of the employees who filled out the survey)

PQR
XLD
ABC

I want to send an email to employees “DEF”, “HJD”. Is it possible to do this task through UI path. Both the excel spreadsheet are ready.

any help will be appreciated.

2

Hi @anjail

Read both Excel files using Read Range:

  • dtAllEmployees → contains Employee Name + Email
  • dtResponded → contains Employee Names who completed the survey

Use the following LINQs

Standardize the data

respondedList = dtResponded.AsEnumerable().Select(Function(r) r(0).ToString.Trim.ToLower).ToList()

Filter employees who did NOT respond

dtPending = dtAllEmployees.AsEnumerable().Where(Function(emp) Not respondedList.Contains(emp("Employee Name").ToString.Trim.ToLower)).CopyToDataTable()

Use a For Each Row activity on dtPending and send emails.

Regards,
Christopher

3

Hi @anjail ,
Yes, this can be achieved in UiPath.

Required Dependencies

  • UiPath.Excel.Activities
  • UiPath.System.Activities
  • UiPath.Mail.Activities

Variables

  • dtEmployees (DataTable)
  • dtSurvey (DataTable)
  • employeeName (String)
  • rowsFound (DataRow())

Steps

  1. Read Employee.xlsx into dtEmployees using Read Range .
  2. Read Survey.xlsx into dtSurvey using Read Range .
  3. Use For Each Row in Data Table on dtEmployees .

Inside the loop:

Assign

employeeName = CurrentRow("Employee Name").ToString.Trim

Assign

rowsFound = dtSurvey.Select("[Employee Name] = '" + employeeName + "'")

If

rowsFound.Length = 0

If True, the employee has not filled out the survey.

Use Send Outlook Mail Message :

To

CurrentRow("Email").ToString

Subject

Survey Reminder

Body

Hi " + employeeName + ",

You have not yet completed the survey. Please complete it at your earliest convenience.

Thank you.

Output

For the sample data provided, emails will be sent only to DEF and HJD since they are present in Employee.xlsx but not in Survey.xlsx.

Thanks.

4

Yes, you ca do this in starightforward

  1. first, read both Excel files into DataTables.
  2. Use a LINQ query(given below) to find employees from Excel1 whose names are not present in Excel2.
  3. Loop through the filtered records and send emails using the email address from Excel1.

Assuming:
dtEmployees = Excel1 (Employee Name, Email)
dtSurvey = Excel2 (Employee Name)

dtPending = dtEmployees.AsEnumerable().Where(Function(emp) Not dtSurvey.AsEnumerable().Any(Function(s) s("Employee Name").ToString.Trim.Equals(emp("Employee Name").ToString.Trim,StringComparison.OrdinalIgnoreCase))).CopyToDataTable()

5

@anjail

the following linq will give you array of emails to which you need to send email

Email_Arrayvariable = dt1.AsEnumerable.Where(function(x) Not dt2.AsEnumerable.Any(function(y) y("Name").ToString.Equals(x("Employee Name").ToString))).Select(function(x) x("email").ToString).ToArray

Apart from this if you want to use activities

use join datatable and join dt1 and dt2 on Name

then use filter to filter the columns related to dt2 for blank using filter datatble

the remaining rows are the rows for which you need to send email

cheers

6

@anjail

You can do the process by following below steps,

  1. Take Read range workbook activity, and pass your first excel file path and sheet and create output variable as dt1
  2. Take Another Read range workbook activity, and pass your second excel file path and sheet and create output variable as dt2
  3. Take assign activity, create a data table variable(dtPendingEmails) and pass that variable in To section and in value section New DataTable() this is initializing the variable to avoid the null exceptions
  4. Take another assign activity, pass above created variable(dtPendingEmails) in To section and in value section enter the below expression,
    dt1.AsEnumerable().Where(Function(x) Not dt2.AsEnumerable().Any(Function(y) y(“Name”).ToString() = x(“Name”).ToString())).CopyToDataTable()
  5. Take for each row in datatable activity, pass dtPendingEmails variable in Data Table section
  6. Take Send outlook mail message activity in that pass CurrentRow(“email”).ToString in To section accordingly enter your subject and body.

image
image577×729 33 KB

Hope it helps,
YK

7

Thank you! Your solution worked right away without any errors.