SQL Cursor and sp_send_dbmail

I'm have been able to send a View that display's multiple records in a nice table based on certain criteria. I use sp_send_dbmail and MSSQL 05

What I can't figure out, is how to send to the email address associated with those records, not a static one, then loop through and send the next result set to the next recipient.

In the code below, i've declared a static @recipients...but i want it to be pulled from the joined table, and i want it to loop to the next record set. So in the joined tables, 1 email will be associated with, let's say, 10 records...etc.

I think this is a cursor thing...not sure. Any ideas?

DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'</tr>' +
    CAST ( ( SELECT td = m.ln,       '',
                    td = m.Lot, '',
                    td = m.Br, '',
                    td = m.Baf, '',
                    td = m.Lp, ''
              FROM mydb.dbo.table as m
              WHERE subd = 'village grove'
              ORDER BY lp ASC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
 
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'local',
	@recipients='myemail@gmail.com',
    @subject = 'Current Homesites',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Select allOpen in new window