Compare email components and send multiple attachments for each Email component

[stefi]

Hi,

I want following automate:

There is given an Excel file with multiple columns.
From this file I just need 5 columns:
email receiver, email CC, email subject, attachment path, timestamp

I want to know, how many times each constellation of the 3 columns (email receiver, email CC, email subject) appears in the data table (same entries in these columns per line).

For each constellation one email with the attachments should be sent.

If a constellation exist more than one time, e.g. 10 times, ideally one email (depending on the attachment file size) should be sent with the 10 attachments (in order to avoid sending 10 emails to the same receiver).

Before sending this email, the number and the file size of the attachments should be checked.
If the number is greater than 5, one or more zip file should be created depending on the file size of all attachments to send.

If the file size of all e.g. 10 attachments exceed 15 MB, a zip file should be created with attachments until the size of 15 MB is reached and the first email with this zip file attachment should be sent.
After sending the email, a timestamp of the sent mail should be entered in every line, from which is taken the attachment path for this zip file, in data table in order to document the sending.

After that a second zip file with the retaining attachments of this constellation should be created and sent in a second email, again Not exceeding the 15 MB file size.

If all attachments for this constellation are sent, the next constellation should be processed in the same way.

Could you please help me how I can automate this scenario in an efficient way?

Many thanks in advance!!!

[sandyarpa767]

Hi @stefi ,

can u break down ur problem statement in to smaller pieces ?
so that it will be easy for some one have a proper solution for ur problem statement .

step-by-step approach to achieve this:

1. Read the Excel File:

• Use the Read Range activity to read the Excel file into a DataTable.

2. Group Data:

• Use the Group By method in LINQ to group the DataTable based on the columns: email receiver, email CC, and email subject.

3. Process Each Group:

• For each group, check the number and size of attachments.
• If the total size exceeds 15 MB or the number of attachments exceeds 5, split them into multiple emails with zip files.

4. Send Emails:

• Use the Send Outlook Mail Message or Send SMTP Mail Message activity to send emails.
• Ensure to attach the necessary files and update the timestamp in the DataTable.

5. Update Excel File:

• After sending each email, update the timestamp column in the DataTable.
• Use the Write Range activity to update the Excel file with the new timestamps.
  Based on my understanding i have provided a high lvl overview of solution

Regards
Sandy

[stefi]

Hi @sandyarpa767

Thank you for Young reply. The high Level approach is this what I want to do.

But I do not know how to realize this.

Which parts must be processed in the step „process each group“?

In my understanding the adding of the attachments for the Email or creating the zip files for the email and the Step send Mails and Write back the timestamp into datatable Must be within the „For each group“ processing, correct?

And is the usage of count-indexes necessary in Order to know in which lines the timestamps have to be written back?

Thank you.

Kind regards
stefi

[sandyarpa767]

Yes, you are correct. The steps to add attachments, create zip files, send emails, and write back timestamps into the DataTable must be within the “Process Each Group” step. Additionally, the usage of count-indexes or maintaining a reference to the original DataTable rows is necessary to know where to write back the timestamps.

[stefi]

Could you please provide an example for the usage of count-indexes or maintaining a reference to the original DataTable rows to know where to write back the timestamp?

I do not know how to use indexes unfortunately….

[sandyarpa767]

Hi @stefi ,

Please refer below image for using currentindex which acts as a counter to update in the corresponding row

** In Assign**:

• To: datatablename.Rows(currentindex)("Timestamp")
• Value: `DateTime.Now.ToString(“yyyy-MM-dd HH:mm:ss”)

Regards
Sandy