Sql-server – Send Email To Multiple Recipients

database-mailsql serversql-server-2008-r2t-sql

Creating a group or mailing list is not an option, and this may not be possible, but I need a way of scanning EmailMe and pulling the email address(es) to email from there. If two email addresses have the same `vendorID ONE email should be sent not multiple. Below is DDL – and using this as an example purple and green should both be the recipient on one email and blue should be sent it's own email.

How can I perform this? I am going to use dbsendmail to send the message.

Create Table EmailMe
(
  ID  int IDENTITY(1,1) PRIMARY KEY NOT NULL
  ,name varchar(100)
  ,vendorid int
  ,email varchar(500)
)

Insert Into EmailMe (ID, name, email) VALUES
('purple', 12, 'purple@hsbcglobal.com')
,('green', 12, 'green@hsbcglobal.com')
,('blue', 1, 'blue@hsbcglobal.com')

Best Answer

You can break down the vendors using a CTE, prior to using the COALESCE function to get a list of emails, with one per vendor.

CREATE TABLE EmailMe
    (
      ID INT IDENTITY(1, 1)
             PRIMARY KEY
             NOT NULL ,
      name VARCHAR(100) ,
      vendorid INT ,
      email VARCHAR(500)
    );

INSERT  INTO EmailMe
        ( name, vendorid, email )
VALUES  ( 'purple', 12, 'purple@hsbcglobal.com' )
,       ( 'green', 12, 'green@hsbcglobal.com' )
,       ( 'blue', 1, 'blue@hsbcglobal.com' );

DECLARE @EmailRecipients VARCHAR(MAX);

;
WITH    cteIds
          AS ( SELECT   MAX(ID) AS Id
               FROM     EmailMe
               GROUP BY vendorid
             )
    SELECT  @EmailRecipients = COALESCE(@EmailRecipients + ';', '') + email
    FROM    EmailMe e
            JOIN cteIds c ON c.Id = e.ID;

SELECT  @EmailRecipients;

Results are:

blue@hsbcglobal.com;green@hsbcglobal.com
Related Question