Looping through data in an excel and emailing

Hi Everyone,

I am trying to build an automation where i have to loop through data from an excel sheet and send an email out for each invoice. Also i should not be sending the email to an exclusion list i.e. if an email account is in the exclusion list then no need of sending the email out.

I am attaching a sample excel file. In one sheet there is the data which i need to read and email out with the email addresses as well in one of the rows. The second sheet includes the email addresses that need to be excluded.

If you look at the screenshot above. I need to send an email out to “abc @gmail.com” and the email should include the vendor name and invoice number.

For ex: First email which needs to be sent out should use the below parameters

To : abc @gmail.com

Subject: Please review.

Body : This invoice is overdue
VENDOR1
Invoice number # 937498327

Second email which needs to be sent out should use the below parameters

To : def @gmail.com

Subject: Please review.

Body : This invoice is overdue
VENDOR2
Invoice number # 70597205942

And then third email and so on…

One thing to keep in mind is we do not need to be sending “hij @gmail.com” and “klm @gmail.com” as they are part of the exclusions list.

Can someone please give suggestions or guide me on how to build this automation.

Khader.

I am not able to attach the excel file. So also posting a screenshot of what exclusion list looks like.

I have already posted the screenshot of how the excel looks in my original post.

Also please ignore spaces in the email addresses which i posted in my original post.

Khader.

why are there 2emails and invoice numbers under vendor3 and how to handle that?
image

@jack.chan

So since hij @gmail.com belongs to the exclusion list we do not need to be sending an email out to that user.

But instead of hij @gmail.com if the email was something different like abcdef @gmail.com(this does not belong to the exclusion list) Then the email should look like.

To : abcdef @gmail.com

Subject: Please review.

Body : This invoice is overdue
VENDOR3
Invoice number # 37840923
VENDOR3
Invoice number # 703952892

Also there is one more scenario which i forgot to mention in my original post. There could be a possibility that the same email account might be under another vendor. I have address one more invoice to the list(VENDOR7 - 29478592). Please see below.

So here qrs @gmail.com is mentioned twice for different vendors. In that case the email should look like below.

To : qrs @gmail.com

Subject: Please review.

Body : This invoice is overdue
VENDOR6
Invoice number # 8763279
VENDOR7
Invoice number # 29478592

Khader.

@jack.chan

Correction to a statement from my previous post.

I meant " I have added one more invoice to the list(VENDOR7 - 29478592)"

Khader.

Hi @khader.abdul ,

Is it possible for you to Re-Structure the Input Data, It might be difficult to Process or need to bring the Data in a format where it is suitable for Data Merge or Group.

Maybe a Data Re-Structure to below format :
image

@supermanPunch

Thanks for the reply. I guess even the restructuring part we may have to automate it if possible, if that is the only way.

As the excel data is in the format which i showed.

Actually the original report output gets generated in excel format with a lot of fields. Then we use the pivot table feature in excel and select the vendor name, invoice number and email fields and the pivot table creates a new sheet with displaying the three fields (Vendor Name, Invoice # and email) in the format which i showed in my screenshot.

Khader.

@supermanPunch We may be able to re-structure the data. I am looking into it.

Khader.

@khader.abdul ,

That should be better in terms of Understanding and Processing the Data, Let us know once you are able to Confirm it, and Provide us with the Format Accepted, so that we can provide you with better suggestions.

@supermanPunch

We are able to structure the data output as shown below. Also we do not have to worry about excluding any email addresses as we were able to also exclude the emails while generating the report output. So we can ignore the exclusion list which i mentioned in my original post.

image

Also the report is generated in a csv file which we convert to excel file. So if reading the data from csv file and sending out an email is easier than reading and sending it from an excel file then we can go ahead with using a csv file as well if needed.

All the emails which need to be sent out to the above file are shown below.

To : abc @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR1
Invoice number # 937498327

To : def @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR2
Invoice number # 70597205942

To : hij @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR3
Invoice number # 37840923
VENDOR3
Invoice number # 703952892

To : klm @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR5
Invoice number # 986287356

To : nop @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR6
Invoice number # 98762435978

To : qrs @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR7
Invoice number # 8763279
VENDOR8
Invoice number # 29478592
VENDOR9
Invoice number # 9879843709

Thank You,
Khader.

@supermanPunch
We are able to structure the data output as shown below. Also we do not have to worry about excluding any email addresses as we were able to also exclude the emails while generating the report output. So we can ignore the exclusion list which i mentioned in my original post.

image

Also the report is generated in a csv file which we convert to excel file. So if reading the data from csv file and sending out an email is easier than reading and sending it from an excel file then we can go ahead with using a csv file as well if needed.

All the emails which need to be sent out to the above file are shown below.

To : abc @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR1
Invoice number # 937498327

To : def @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR2
Invoice number # 70597205942

To : hij @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR3
Invoice number # 37840923
VENDOR3
Invoice number # 703952892

To : klm @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR5
Invoice number # 986287356

To : nop @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR6
Invoice number # 98762435978

To : qrs @gmail.com

Subject: Please review.

Body : The below invoices are overdue
VENDOR7
Invoice number # 8763279
VENDOR8
Invoice number # 29478592
VENDOR9
Invoice number # 9879843709

Thank You,
Khader.

@khader.abdul ,

Check the Sample workflow below :
Prepare_MailBody.xaml (11.6 KB)

I have used a Build Datatable to get a Similar Structured Data as that of yours. We Then Perform a GroupBy based on the Email Column. In this way, we can Combine the Related Data pertaining to One Email and Send it in One Mail.

Let us know if the Workflow is not as Expected.

@supermanPunch

How would i get the excel/csv data into the data table.

Khader.

@khader.abdul , Try using the Workbook Read Range Activity, In your Case, as the data doesn’t contain headers, Disable Add Headers Property.

Output of Read Range Activity is a Datatable, which you should be able to incorporate in the Process

@supermanPunch Can i just use the csv file and use the readcsv activity?

Khader.

@khader.abdul , Yes. You could also Try with it.

Let us know if you face any issues.

It worked thank you so much.

Khader.

1 Like

@supermanPunch So we had to add one more column to the csv output as shown below.

image

Now based on the output i made certain changes in the automation workflow which you helped me with. But i think i am missing something as when i run the automation the program is sending out separate emails for each invoice instead of combining the invoices if they have to one email account. Do you know what mistake i might be making?

Khader.

Hi @khader.abdul ,

The reason is because, you have added a Column at the Beginning which changed the position of the From Column or Email Column, hence the The Linq Query earlier used doesn’t work as it is picking up the Invoice Number Column for Grouping.

We can do one of the following changes and Check :

  1. Move the First Column to the Last and Check with the same code.

or

  1. Change the Index of the Column in Grouping Query in Assign Activity from 2 to 3.
    image

As we can see from the Screenshot, you have column names added as well, we can use the column names now, instead of the column positions.
image

This change needs to performed everywhere where the row(2) is being used.

Hello @khader.abdul ,

May I know how you are getting this Excel sheet or how this Excel is generating?
Is there any other standarization possible in the excel, like moving the row item to different columns(Vendor,Vendor id,Email id etc should be the columns).