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.
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.
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.
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.
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.
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.
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
@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.
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
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.
@supermanPunch So we had to add one more column to the csv output as shown below.
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?
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 :
Move the First Column to the Last and Check with the same code.
or
Change the Index of the Column in Grouping Query in Assign Activity from 2 to 3.
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.
This change needs to performed everywhere where the row(2) is being used.
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).
Anil_G
shaik.muktharvalli1
ashokkarale
Akash_Javalekar1
sudster
mohamed.shaker
Affan_Basar
lrtetala
johnom1
erkanceylan