Choosing an Audience
Now, if we're lucky, we have a pool of users to whom we can send our message.
To do this, we must identify the users we want to contact, create a message for each user, and send that message. Because we're building a browser-based system, let's give the administrator a screen from which to send a message, with send.asp. This page enables the administrator to choose an audience, as well as to define a message and determine whether it should be sent out as HTML or plain text. View Listing 3
){kind=link}
Now that we've got the message and the audience, we need to send off the message. We'll do this with Listing 4. The first thing that we need to do is to select the users who will be receiving the email, using the criteria specified on the previous page. After opening a connection to the database, we start building the SQL statement we'll use. We know that we'll be checking against the attributes table for choices such as media or genre, so let's join the tables.
sqlText = "select distinct(u.email), u.first_name, u.last_name "&_
" from users u, attributes a "&_
" where u.email = a.email "&_
" and u.mailtype = '"&request("mailtype")&"'"
We're also specifying whether we're looking for text or HTML users. At this point, if there are no further choices, such as "TV" or "Fantasy," we'll be sending the message to all of our users. If we have narrowed it down, however, we'll create more of the SQL statement by looping through the submitted choices.
attWhere = ""
for each media in request("media")
if attWhere = "" then
'This is the first item
attWhere = attWhere &" a.attribute = '"&media&"'"
else
'We already have items
attWhere = attWhere &" or a.attribute = '"&media&"'"
end if
next
When we're finished, we wind up with a statement that looks something like this:
select distinct(u.email), u.first_name, u.last_name
from users u, attributes a
where u.email = a.email and u.mailtype = 'HTML' and
(a.attribute = 'tv' or
a.attribute = 'books' or
a.attribute = 'fantasy' or
a.attribute = 'spaceopera')
We'll do the same thing to create the where clause for genre limitations.
Finally, when we've created the recordset of email addresses and names, we’re ready to actually send the mail.