1

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, '[email protected]')
,('green', 12, '[email protected]')
,('blue', 1, '[email protected]')
2
  • 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
    Commented Oct 11, 2016 at 20:02
  • 1
    @DougLane - good catch, that is what I meant to do Commented Oct 11, 2016 at 20:06

2 Answers 2

2

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, '[email protected]' )
,       ( 'green', 12, '[email protected]' )
,       ( 'blue', 1, '[email protected]' );

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:

[email protected];[email protected]
4

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
3
  • 1
    I never thought of using a semicolon-delimited string, what an ingenious answer! Commented Oct 11, 2016 at 19:00
  • This will actually not clear out the previous entries. For example, iteration one is [email protected];[email protected] then iteration 2 is [email protected];[email protected];[email protected] If I try to set @EmailRecipients to ''; before the select statement it adds a ; before the email addresses Commented Oct 11, 2016 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
    Commented Oct 11, 2016 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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