Compare variables, remove duplicates and/or null values

[mafhobb]

Hello.

I have a bit of an issue getting the code right for the following;

When an action is performed in a db, the code looks up the value of several of its controls to find a few e-mail addresses. I generally end up with five e-mail addresses stored in variables like this:

Commentmail
PMmail
staffmail
supervisor
managermail

Then I go ahead and put together an e-mail to send certain information to all of this people by concatenating the different strings into one and adding it to the .to field

It all works fine except when one of the two e-mails is the same (commentmail could be the same as staffmail, or even the same as supervisormail) in which case the code throws an error and the e-mail does not get sent becasue the same e-mail address is present twice (or three times) in the "sent to box"

Also, there is the posibility that one of the emails does not exist, in which case I use the Nz function to turn that e-mail into an empty string ""

commentmail = Nz(DLookup("[work e-mail]", "[RD Staff]", "[Name] ='" & [Forms]![Project Main]![R&D Work By] & "'"), "")

How can I compare all of these variables through code to make sure that only unique, not null e-mails are listed?

Thanks

mafhobb

 

[jdraw]

research Dcount (...) = 1 and IsNull = False