Nice article and it made me look back at some stuff I had done a while back and re-jig it to fit in with this.

I took a slightly different take on generating the mail batches. Also I was not sure why you needed the @query in there twice rather than generating beforehand.

My other different approach is to curse through each batch and semi-colon separate all the addresses into one addresses line:

--declare the tables for use

DECLARE @tempemails TABLE(emailID INT IDENTITY(1,1), emailAddress NVARCHAR(100))

DECLARE @emailBatches TABLE(batchID INT IDENTITY(1,1), startID INT, endID INT)

DECLARE @batchSize INT

DECLARE @totalMails INT

DECLARE @startID INT

DECLARE @endID INT

DECLARE @emailAddresses NVARCHAR(4000)

DECLARE @firstName NVARCHAR(100)

DECLARE @mainBody NVARCHAR(MAX)

SELECT @mainBody = '<p>Welcome to the lovely batch mail we have here</p>

<p>I think you will like it very much</p>

<p>Bye then</p>

<div id="Mysignature">'

SELECT @batchSize = 8

--test Data

INSERT @tempemails(emailAddress)

SELECT 'fred@fred.com' UNION

SELECT 'bill@bill.com' UNION

SELECT 'jane@jane.com' UNION

SELECT 'norman@norman.com' UNION

SELECT 'weeble@weeble.com'

/*

You might of course do

INSERT @tempemails(emailAddress)

SELECT DISTINCT emailAddress

FROM myTable

WHERE emailAddress IS NOT NULL

*/

--see how many mails there are

SELECT @totalMails = MAX(emailID) FROM @tempemails

/**

* compile the list of batches.

* OK so the endID may go over the MaxID

* but this with the below cursor this will not matter in the least

**/

INSERT @emailBatches(startID, endID)

SELECT @batchSize*(t1.emailID-1)+1, @batchSize*(t1.emailID-1)+@batchSize

FROM @tempemails t1

WHERE @batchSize*(t1.emailID-1)+1 <= @totalMails

DECLARE mailBatchCursor CURSOR FOR

SELECT startID, endID

FROM @emailBatches

ORDER BY batchID

OPEN mailBatchCursor

FETCH NEXT FROM mailBatchCursor

INTO @startID, @endID

WHILE (@@fetch_status = 0)

BEGIN

--semi-colon separate the email addresses in the batch

SELECT @emailAddresses = STUFF((SELECT ';' + emailAddress FROM @tempemails WHERE emailID BETWEEN @startID AND @endID FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Profile Name',

@recipients=@cs_application_email,

@blind_copy_recipients =@emailAddresses,

@subject ='Subject Of The EMail',

@body = @query,

@body_format = @mainBody ;

WAITFOR delay '00:10:00'

FETCH NEXT FROM mailBatchCursor

INTO @startID, @endID

END

CLOSE mailBatchCursor

DEALLOCATE mailBatchCursor

Using FOR XML again you can easily make a table out of any query to add:

SELECT @table1 = '<h1>Look at the results below</h1><table><tr><th>col1</th><th>col2</th><th>col3</th></tr>' +

(SELECT '<tr>' + '<td>' + col1 + '</td><td>' + col2 + '</td><td>' + col3 + '</td></tr>' FROM mytable WHERE myid = 5 FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)')

+ '</table>'