Hi Carol,
Take a look at this thread:
http://groups.google.com/group/micr..._frm/thread/280020a06d9226c6/404ef4621955d903
Message # 5 shows you an example of creating a DAO (Data Access Objects)
recordset, which loops through all records in a table named "tblPeople". The
e-mail addresses are in a field named "EmailAddress". This table also
includes a field of Yes/No data type named "blnActivePerson". A SQL
(Structured Query Language) statement uses this field in the criteria of the
query. Note that a part of the code is word wrapped for me, when I view this
message in Google groups:
Set rs = db.OpenRecordset("SELECT EmailAddress FROM tblPeople " _
& "WHERE EmailAddress Is Not Null AND
blnActivePerson<>0")
The last part, "blnActivePerson<>0")" should be on the second line, unless
you use a line continuation character (a space plus an underscore) as
indicated at the end of the first line. If you do not have this type of field
in your database, then change this line of code so that it reads something
like this:
Set rs = db.OpenRecordset("SELECT EmailAddress FROM tblPeople " _
& "WHERE EmailAddress Is Not Null)
Make the appropriate substitutions for your table and field names. Note: If
you used any spaces or other special characters, or reserved words, in the
name of your table, then you need to enclose these values in square brackets.
Something like this for a table named "Members List" and a field named
"E-mail Address":
Set rs = db.OpenRecordset("SELECT [E-mail Address] FROM [Members List] " _
& "WHERE [E-mail Address] Is Not Null)
To get started using this code, click on the Modules tab in your database.
Create a new module. Copy and paste the code from message # 5 into your new
module. Save the module, but make sure to give it a name different from any
procedure in your database. (A procedure is a subroutine or function). An
easy way to avoid duplicating names is to prefix the names of modules with
lowercase "bas" or "mod" (these are two common prefixes in use). So, for
example, you might name the module "basConcatenateUsers".
In order for this code to compile and run, you will need to have a reference
set to the "Microsoft DAO 3.6 Object Library" (use the 3.5 Object Library if
you are using Access 97). To check your references, click on Tools >
References when you are in the VBE (Visual Basic Editor).
Try the code in message # 5 first, before attempting the code in message #
2. For the present time, comment out the line of code towards the end of this
procedure, which reads:
SendMail strUsers, "This is a test message"
Do this by adding a single apostrophe at the beginning of this line of code.
It should turn green in color (assuming you have the default colors assigned
in the VBE options). Uncomment the line above this line of code by removing
the apostrophe:
' Debug.Print strUsers
After you have copied the code into a new module, saved it, and verified
that you have a reference set to the DAO Object library, try compiling the
code (Debug > Compile ProjectName). Hopefully the code will compile without
any errors. To run this code, have your mouse cursor blinking anywhere within
the function. Then press the F5 button. To see the results, open the
Immediate Window (Ctrl G), if it is not already open. To see what "looping
through" a recordset means, try adding a break point to the line of code that
reads:
Set db = CurrentDb()
or to any line that you want, except that you cannot add a break point to a
line that starts with Dim. To add a break point, click in the grey area that
is on the left hand side. You should see the line of code highlighted in
maroon. Then press the F5 button again. Notice that the code breaks at your
breakpoint. It breaks before executing the indicated line of code. To single
step through the code, press the F8 key. To advance to a second break point,
or to finish running the code if no more break points have been added, press
the F5 key. Note that while you are in break mode, you will not be able to
switch to a different group of objects, such as Tables.
Your 1000 records will most likely be a problem with your ISP (Internet
Service Provider), if you attempt to send one message to all users at the
same time. They will likely flag the message as spam. You'll need to do some
investigating with your ISP to see how many e-mail addresses that they allow
on an outgoing message. You might want to send the same message to, say, 40
people at a time. You could do this with a modification to the procedure, but
for now, just try this much and see if you have success printing a
concatenated list to your Immediate Window.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
