Multiple e-mails being sent to one recipient!

email

#1

I have a column in an Excel sheet (named “POC Email”) which contains repetitive values (same e-mail appearing in more than one row).

I need to send an attachment to all the recipients in the column. However, I need to ensure that the e-mail along with the attachement is sent to a recipient only once (even though the recipient appears multiple times in the column).

How can we achieve this ?

Greatly appreciate your help.

Thank you!


Convert "list" to "line"
#2

What you could do is grab all the emails from the spreadsheet, and put them in a list. With that list, you can set the value of that list equal listName.Distinct(), which will have only one of each e-mail address. Hope that helps!


#3

Something like this should help.Make sure you convert emails to lower case while getting them to list and for Distinct to work.

string.Join(";",YourDataTable.AsEnumerable().Select(Function(s) s.Field(Of String)(“POC Email”).ToLower).ToList.Distinct)


#4

Thanks a lot, Vinay and Jacob! Let me explain the scenario. Please find attached the template.Template.xlsx (9.7 KB)

This is a template of vendors who would be shipping to various states mentioned. One vendor can ship to multiple states and different states under one vendor may have different POCs as shown in the template.

There is a master sheet that includes n number of such vendors shipping to states across India. We need to use the data in this template to filter that master sheet based on the ID and State and then send consolidated data to the POC e-mails. For eg. ABC (1001) has no state assigned which means data for all states for ABC goes to one single POC. GHI (1002) has one POC for 3 states (MH, TN and AP) and a different POC for WB and MP. 1003 and 1004 have one POC each and they ship to one state only.

(a) We need to ensure we send the correctly filtered data to the respective POC.
(b) We are saving the filtered data to separate excel files and then sending the files to the POCs.
(b) A POC should receive only one e-mail with consolidated data for whatever ID(s) and state(s) is/are allocated to him.
© Vendor-State combinations not assigned to any POCs (i.e. not appearing in the template excel but are there in the master sheet) go to a single separate email (say mno@gmail.com) for additional research on who the POC for those would be.

How can we achieve this ?

Greatly appreciate your help.

Thanks.


#5

In this case, it might be easiest to have a dictionary, with the e-mail as the key, and in the value, you have all the information compiled, in whichever way you think is easiest. One potential storage solution would be just saving the datarow object that you get when you use a for each row activity, but you are free to use whatever storage you think will be easiest. From here you iterate through each row in the table, adding the information to the dictionary with the corresponding e-mail, and then after getting all the data, formatting it into an e-mail, and sending one email to each user.


#6

Thanks Jacob! I have got the data that I want. However, the only problem that I am facing is sending the e-mail once for that POC. If there are 3 states assigned to one POC, the mail is triggered to him thrice and not once even though I am using a ‘Distinct’ on the List.

I am trying to analyze how to solve this problem (either by keeping the send e-mail block out of the loop or even using a counter)


#7

If you use the dictionary solution, you won’t need to use distinct. After the dictionary is filled out, you can iterate through each e-mail, and grab the corresponding list from the dictionary. you can then proceed to iterate through this list, which should only have the information that corresponds with one email address. From here you can compile that information in whichever way your company needs, and then send one email for each distinct email address. For example, the workflow should be something like this

Hope this helps!