Sending an email from a table

Question

Sending an email from a table

tedo

SSCrazy

Points: 2229
More actions
January 3, 2019 at 6:36 am

#416598

All, I am trying to send an email per email address from a table, however, where I have mulipul email address I only want to send all the records in that table once to the user. currently an email per record is going.
would you know how to help with this.
This is the table below
Accountname Firstname Lastname EmployeeNumber ReportsTo Reports To Email SENT_FLAG
SQLA-terry Terry T1234 Terry Jago Terry@one.xxx 0 SQLA-Graham Graham N T12345 Terry Terry@one.xxx0 SQLA-Ryan Ryan T T123456 Ted Terry.test@test.com 0 SQLA-Kelvin Kelvin M T123456 Ted Terry.test@test.com 0
BEGIN
    -- Error handling variables     DECLARE @err_number int;     DECLARE @err_line int;     DECLARE @err_message varchar(2048);     DECLARE @err_procedure varchar(2048);
    -- ** Error Handling - Start Try **     BEGIN TRY
    -- No counting of rows     SET NOCOUNT ON;
    -- Declare variables declare @reportsto nvarchar(200) ,@username nvarchar(200) ,@firstname nvarchar(200) ,@lastname nvarchar(200)
    DECLARE @VAR_ADDRESS VARCHAR(64);     DECLARE @VAR_SUBJ VARCHAR(64);     DECLARE @VAR_BODY varchar(max); DECLARE @xml NVARCHAR(MAX)     -- Get email list   DECLARE VAR_CURSOR CURSOR FOR         select reportsto,firstname,lastname,Accountname,[Reports To Email] from #test         WHERE SENT_FLAG = 0 order by [Reports To Email] desc;
    -- Open cursor     OPEN VAR_CURSOR;
    -- Get first row     FETCH NEXT FROM VAR_CURSOR     INTO @reportsto,@firstname,@lastname,@Username, @VAR_ADDRESS;
    -- While there is data     WHILE (@@fetch_status = 0)     BEGIN   SET @xml = CAST((select Firstname as 'td',' ',Lastname as 'td',' ', Accountname as 'td'   from #test where reportsto = @reportsto and [Reports To Email] = @VAR_ADDRESS order by ReportsTo desc   FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
  set @VAR_SUBJ = 'SOC Q4 Account Audit'   SET @VAR_BODY = '<html><body><H3>Dear ' + @reportsto + '</H3>
text
  <table border = 1>   <tr>   <th> Firstname </th> <th> Lastname </th> <th> Accountname </th></tr>'      SET @VAR_BODY = @VAR_BODY + @xml +'</table></body></html>'         -- Send the email         EXEC msdb.dbo.sp_send_dbmail    @profile_name='DBAAlert',             @recipients = @VAR_ADDRESS,             @subject = @VAR_SUBJ,             @body = @VAR_BODY,             @body_format = 'HTML' ;
   delete from #test where ReportsTo = @reportsto
   --- update table     UPDATE #test     SET SENT_FLAG = 1     WHERE SENT_FLAG = 0 and reportsto = @reportsto;
        -- Grab the next record         FETCH NEXT FROM VAR_CURSOR             INTO @reportsto,@firstname,@lastname,@Username, @VAR_ADDRESS;     END
    -- Close cursor     CLOSE VAR_CURSOR;
    -- Release memory     DEALLOCATE VAR_CURSOR;
    -- Update the table as processed   UPDATE #test   SET SENT_FLAG = 1   WHERE SENT_FLAG = 0 and reportsto = @reportsto;
    -- ** Error Handling - End Try **     END TRY
    -- ** Error Handling - Begin Catch **     BEGIN CATCH
      -- Grab variables       SELECT           @err_number = ERROR_NUMBER(),           @err_procedure = ERROR_PROCEDURE(),           @err_line = ERROR_LINE(),           @err_message = ERROR_MESSAGE();
      -- Raise error       RAISERROR ('An error occurred within a user transaction.                   Error Number        : %d                   Error Message       : %s                   Affected Procedure : %s                   Affected Line Number: %d'                   , 16, 1                   , @err_number, @err_message, @err_procedure, @err_line);
    -- ** Error Handling - End Catch **        END CATCH
END
currently its sending 4 emails I only want it to send 2.
ie I want one email sent out for these two email addresses
SQLA-terry Terry T1234 Terry Jago Terry@one.xxx 0
SQLA-Graham Graham N T12345 Terry Terry@one.xxx0
to Terry@one.xxx
please see enclosed accounts
SQLA-terry Terry
SQLA-Graham Graham N
Terry
and one email sent out for this
SQLA-Ryan Ryan T
SQLA-Kelvin Kelvin M
ie
to Terry.test@test.com
please see enclosed accounts
SQLA-Ryan Ryan T
SQLA-Kelvin Kelvin M
ted
Thanks for the help in advance.
Terry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Buecker SSChasing Mays Points: 638 More actions · Answer 1

It looks like you may need to refine the query to something like:
SELECT DISTINCT manager_email FROM table

Then the body of the email may need an addition CURSOR to build the table for each employee.
Or is there something else going on?

tedo SSCrazy Points: 2229 More actions · Answer 2

at the moment I get 4 emails two are correct and two are blank I thought with the delete I would only get one email per email address.
I would like one email with this

to Terry.test@test.com
please see enclosed accounts
SQLA-Ryan Ryan T
SQLA-Kelvin Kelvin M
ted
and one with this

to Terry@one.xxx
please see enclosed accounts
SQLA-terry Terry
SQLA-Graham Graham N
Terry

Rob Buecker SSChasing Mays Points: 638 More actions · Answer 3

Just some pseudo-code here:

DECLARE curEmail CURSOR FOR
SELECT DISTINCT manager_email FROM table
OPEN & FETCH into @email
-- create body
SET @body = 'please see enclosed accounts <br><html><table>'
SELECT @body = @body + '<tr><td>' + ISNULL(t.username, '') + ' ' + ISNULL(t.name, '') + '</td></tr>'
FROM table AS t

very quick and dirty but may get the job done. I have limited experience using XML method that is why I showed it using some basic HTML.

tedo SSCrazy Points: 2229 More actions · Answer 4

fixed it I just had too many columns in the CURSOR I removed them like so and it works now.

CURSOR FOR
select distinct reportsto,[Reports To Email] from #test

FETCH NEXT FROM VAR_CURSOR
INTO @reportsto, @VAR_ADDRESS;