I just wrote this small SPROC which builds the list of email address and separates them with a comma.
CREATE PROCEDURE [usp_GetEmailList]
@EmailList nvarchar(200) OUTPUT
AS
DECLARE @Email nvarchar(50)
--DECLARE @EmailList nvarchar(200)
SET @EmailList = ''
DECLARE Email_Cursor CURSOR FOR
SELECT Email FROM tblPerson
OPEN Email_Cursor
FETCH NEXT FROM Email_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Email_Cursor
INTO @Email
SET @EmailList = @EmailList + @Email + ','
END
SET @EmailList = SUBSTRING(@EmailList,0,LEN(RTRIM(@EmailList)))
CLOSE Email_Cursor
DEALLOCATE Email_Cursor
GO
You can view the complete article
Generating Email List using SPROC