How to filter out unwanted email addresses, InStr function? 1

[tmryan2]

Hi, this should be an easy problem but I haven't been able to figure out how:

I have two tables: Table 1 called "Contacts" contains a bunch of email addresses. Table 2 called "Do_not_email" contains email addresses and domains that don't want to receive email from me.

Contacts Do_not_email
------------- --------------
123@abc.com 456@def.com
456@def.com abc.com
789@ghi.com

In the above example then, only 789@ghi would get an email because 456@def.com is on the do_not_email list, and the entire abc.com domain is blocked as well.

But how do I perform this match in Access? I can't just add both tables in a query and join them because I need to account for the abc.com domain cases as well. I'm guessing I might need to use the InStr function somehow?

Any tips on how to solve this would be appreciated.

Thanks!

 

[scking]

The very easiest way would be to add a field to the Contacts table "Do_Not_Email" and make it a yes/no field.

Then you could do something like this:

Dim strSQL As String
Dim db As Database
Dim rst As Recordset

strSQL = "SELECT * FROM Contacts WHERE Do_Not_Email=No;"
Set Db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
If rst.RecordCount<>0 Then
Do While Not rst.EOF
EmailTo(rst.Contact)
Loop
End If

If you don't opt for this solution then you could open Contacts as a recordset, open Do_not_email as a recordset and start reading the contacts recordset. For each contact read through the Do_not_email recordset and when you have a hit don't email them.

Steve King Growth follows a healthy professional curiosity

 

[tmryan2]

Thanks scking! I haven't thought of that before. I guess I just needed a kick in the right direction.

I ended up using your second suggestion: opening up the Do_not_email table as a recordset, and for each record in the Contacts table, cycle through the entire Do_not_email table to find a match. The speed is a bit sluggish, but it works.

Thanks again for the quick response!

 

[andylec]

Use the unmatched query wizard to find all records in Contacts table that are not in the Do_not_email table. This will improve performance.