Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Email Addresses
I have a workbook that needs to get sent out to several different recipients.
Occasionally some of those recipients might be duplicates. How can I make sure that duplicate recipients receive only one copy of the workbook? What I've done to read the list of email recipients is use a for-next loop. Something like this: For a = 1 to 7 email(a) = Cells(a, 2) 'assuming the list is in column B next a Then I use another for-next loop to email the workbook. I'll bet there's probably a better way of doing this. Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Email Addresses
Hi Bill,
Make a collection instead of an array of your email addresses. Duplicates won't arise and you can loop through the collection to post: Dim EmailCollection as new Collection Dim EmailAddress as Variant On error resume next For a = 1 to 7 EmailCollection.Add Cells(a, 2), Cstr(Cells(a, 2)) next a on error goto 0 'code... For each EmailAddress in EmailCollection 'whatever code you had next EmailAddress regards Paul "Bill Oertell" wrote in message ... I have a workbook that needs to get sent out to several different recipients. Occasionally some of those recipients might be duplicates. How can I make sure that duplicate recipients receive only one copy of the workbook? What I've done to read the list of email recipients is use a for-next loop. Something like this: For a = 1 to 7 email(a) = Cells(a, 2) 'assuming the list is in column B next a Then I use another for-next loop to email the workbook. I'll bet there's probably a better way of doing this. Any help would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Email Addresses
Thanks for the suggestion, Paul. I'll see if I can incorporate this into my
code. I think I was a little too brief in my original post. Let me explain a little further. On sheet1 I have a form (a regular worksheet in the shape of a form, not a real VB form) that has check boxes in each row from C to G. Each box represents an email address to which the form needs to be sent, so if the boxes in columns D and E are checked, then the form is mailed to say recipients 2 and 3 (assuming C goes with 1 and G goes with 5). Further complicating matters is that some recipients receive the whole workbook (macros and all) while others only receive the sheet. The distinction is noted in the email recipients sheet next to the columns with the email addresses (one column for those receiving the whole workbook and the other for those receiving only the worksheet). On top of that, I don't know exactly how many recipients a user might input in the recipients column. Rows 2 through 6 are mandatory, but I want a user to be able to input additional email recipients if they choose. For instance, they may want to email a copy of the form to their boss so that he or she knows the form has been sent. I'll bet I can accomplish this using a collection, huh? Just that it would have to be two collections, one for those receiving the worksheet only and one for those receiving the whole workbook. Thanks again for your help. "Paul Robinson" wrote in message om... Hi Bill, Make a collection instead of an array of your email addresses. Duplicates won't arise and you can loop through the collection to post: Dim EmailCollection as new Collection Dim EmailAddress as Variant On error resume next For a = 1 to 7 EmailCollection.Add Cells(a, 2), Cstr(Cells(a, 2)) next a on error goto 0 'code... For each EmailAddress in EmailCollection 'whatever code you had next EmailAddress regards Paul "Bill Oertell" wrote in message ... I have a workbook that needs to get sent out to several different recipients. Occasionally some of those recipients might be duplicates. How can I make sure that duplicate recipients receive only one copy of the workbook? What I've done to read the list of email recipients is use a for-next loop. Something like this: For a = 1 to 7 email(a) = Cells(a, 2) 'assuming the list is in column B next a Then I use another for-next loop to email the workbook. I'll bet there's probably a better way of doing this. Any help would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
Need help finding non-duplicate email addresses... | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Email addresses in Excel need to format for mass email | Excel Worksheet Functions | |||
Transfer Email addresses from spreadsheet to email address book | Excel Discussion (Misc queries) |