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
  ,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')
  • 1
    The INSERT statement above will try to insert to an identity column. I think you meant: INSERT INTO EmailMe (name, vendorid, email) VALUES
    – Doug Lane
    Oct 11 '16 at 20:02
  • 1
    @DougLane - good catch, that is what I meant to do Oct 11 '16 at 20:06

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.

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

        ( 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:


You can select from your table into a semicolon-delimited string. Pass that string variable to the SQL send mail procedure.

DECLARE @EmailRecipients VARCHAR(MAX);

select @EmailRecipients = COALESCE(@EmailRecipients + ';', '') + Email
from EmailMe

SELECT @EmailRecipients
  • 1
    I never thought of using a semicolon-delimited string, what an ingenious answer! Oct 11 '16 at 19:00
  • This will actually not clear out the previous entries. For example, iteration one is purple@hsbcglobal.com;green@hsbcglobal.com then iteration 2 is purple@hsbcglobal.com;green@hsbcglobal.com;blue@hsbcglobal.com If I try to set @EmailRecipients to ''; before the select statement it adds a ; before the email addresses Oct 11 '16 at 20:09
  • 3
    I don't understand the rules of your query. Are you trying to send one email to every vendor grouped by vendor ID? My example query above pulls all email addresses, regardless of vendor ID. If you want to call sp_send_dbmail for every vendor, then you will likely need to resort to using a cursor for multiple calls to send_dbmail. I'm not sure if this applies, but a good tip for sending bulk email without exposing email addresses is to use BCC.
    – Gary
    Oct 11 '16 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.