SEND MESSAGES TO A LIST OF E-MAIL RECIPIENTS

Using T-SQL, you can send messages to a list of e-mail recipients. The
extended system stored procedure, xp_sendmail, has an @recipients
parameter, which you can use for the list of e-mail recipients. You can 
generate
the list from a table and assign it to a T-SQL variable. The same T-SQL
variable is then used as the assignment variable for the @recipients
variable of the xp_sendmail extended system stored procedure. Declare 
the
assignment variable as VARCHAR(8000) to maximize the length in order to
accommodate the e-mail list of recipients.

Due to the shortcomings of the length of the variable character
datatype, it may be necessary to account for the length of individual 
e-mail
names along with the accumulated lengths of all e-mail names in order 
to send
multiple e-mail messages. Another alternative is to create an e-mail
distribution list and assign the e-mail names to the distribution list.

The following is a sample script that demonstrates the use of the
@recipients parameter of the xp_sendmail extended system procedure.

CREATE TABLE test_email
(email_name VARCHAR(255) NOT NULL)
GO
INSERT test_email VALUES ('<my email address>')
INSERT test_email VALUES ('<my alternative email address>')
GO
DECLARE @email_list VARCHAR(8000)
SELECT @email_list = @email_list + email_name + ';' FROM test_email
SELECT @email_list
EXEC master..xp_sendmail
    @recipients = @email_list
      ,@query = 'EXEC master..sp_who sa'
    ,@subject = 'Test email',
    @attach_results = 'false',
    @width = 1320,
    @dbuse = 'master',
    @no_header = 'true',
    @message =
'Dear Associate,

This is a test email.

Regards,

Tester
'
GO
DROP TABLE test_email
GO