Duplicate Email Addresses

[Bill Oertell]

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.

 

[Paul Robinson]

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]

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.

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" <[email protected]> wrote in message

Thanks.