GP Past Due Notice

I created this stored procedure so that I can run a job bi weekly to email past due customers. its a work in progress and I will update it as I Go

First code using a cursor

GO
/****** Object:  StoredProcedure [dbo].[cp_SendEmails]    Script Date: 2/9/2018 4:00:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[cp_SendEmails] 
AS 
      DECLARE       @Customer_Name  NVARCHAR(max)
	  DECLARE       @Customer_Num   NVARCHAR(max)
      DECLARE       @Email          NVARCHAR(max)
      DECLARE       @body           NVARCHAR(max) 
      DECLARE       @31_to_60_days  numeric(19,2)
	  DECLARE		@Balance        numeric(19,2)
      DECLARE       @tableHTML      varchar(max)
	  DECLARE       @contacts       as Cursor
      DECLARE       @Table          VARCHAR(MAX) = N''

SELECT @Table = @Table +'<tr>' +

'<td>' + [RM_Doc_Descr]+ '</td>' +
'<td>' + [Doc_Num]+ '</td>' +
'<td>' + [Description]+ '</td>' +
'<td>' + CAST([Post_Date] as varchar(12))+ '</td>' +
'<td>' + '$' + isnull(CAST(left([Original_Order_Value],len([Original_Order_Value]) - 3) as varchar(max)), '0.00')+ '</td>' +
'<td>' + '$' + CAST([Amount_Due] as varchar(15))+ '</td>' +
'</tr>'
FROM spvCustomerARDetail as ard where Amount_Due > 0 
and customer_num = 
'MARATH6339' 


SET @tableHTML = 
N'<H3><font color="Black">Customer Statement</H3>' +
N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:center;" >' +
N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">
<th>Type</th>
<th>Doc Number</th>
<th>Description</th>
<th>Date</th>
<th>Total</th>
<th>Amount Due</th></tr>' + @Table + N'</table>' 

			 
    set @contacts = CURSOR local fast_forward read_only FOR 
 
    SELECT   topw.customer_name, 
             bottom.email, [31_to_60_days], Balance, bottom.Customer_Num
      FROM   spvcustomeraddr AS bottom, 
             spvcustomer AS topw 
             LEFT JOIN spcvcustomerarbal AS cb 
                    ON cb.customer_id = customer_num 
      WHERE  topw.primary_bill_to_addr_code = bottom.address_code 
             AND topw.customer_num = bottom.customer_num 
             AND (total_due > 0 and [31_to_60_days] > 0)

			 and topw.Customer_Num = 'MARATH6339'

            
   
		  		 

    OPEN @contacts; 

	

    WHILE 1 = 1 
      BEGIN 
          FETCH next FROM @contacts INTO @Customer_Name, @Email, @31_to_60_days, @balance, @Customer_Num; 

		  

          IF @@FETCH_STATUS = -1 
            BREAK; 
         
          SET @body = '<!doctype html> <html> <head> <meta charset="utf-8"> <title>Untitled Document</title> </head> <body> <p>Dear ' + @Customer_Name + ', </p> <p>Our records indicate that payment on your account is overdue in the amount of $' +  CAST(@31_to_60_days as varchar(15)) + '. If the amount has already been paid, please disregard this notice. Your total account balance is $'  +  CAST(@Balance as varchar(15)) + ' </p>' + @tableHTML + ' <p>**Please note our remittance address<br>   1239 South 700 West<br>   Salt Lake City UT  84104 **</p> <p>Have a great day!</p> <p>Paula Oliver<br>   Jorgenson Companies<br>   AR-Credit Manager<br>   paulao@jorgensoncompanies.com<br>   1239 South 700 West<br>   Salt Lake City, UT  84104<br>   PH: 801.214.7353<br>   FX: 801.493.0158</p></body> </html>' 

          EXECUTE msdb.dbo.Sp_send_dbmail 
            @profile_name = 'Send Mail', 
            @subject = 'Past Due Notice', 
           -- @recipients = @Email, 
			@blind_copy_recipients ='jason@jorgensoncompanies.com',
			--@copy_recipients ='paulao@jorgensoncompanies.com',
            @body = @body, 
            @body_format ='HTML'; 

			   END 
	
		CLOSE @contacts; 

		 

    DEALLOCATE @contacts; 

    RETURN; 

I found that I had to do it a different way this sample works 100%.

USE [MHTRN]
GO
/****** Object:  StoredProcedure [dbo].[cp_SendEmails]    Script Date: 2/16/2018 10:14:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[cp_SendEmails] 
AS
--DROP TABLE #the_table 
--DROP TABLE #emails 

--select * from #emails 
--select * from #the_table 
CREATE TABLE #the_table 
  ( 
     [rm_doc_descr2]         VARCHAR(50), 
     [doc_num2]              VARCHAR(50), 
     [description2]          VARCHAR(50), 
     [post_date2]            DATETIME, 
     [original_order_value2] VARCHAR(50), 
     [amount_due2]           VARCHAR(50), 
     [customer_name2]        VARCHAR(50), 
     [31_to_60_days2]        VARCHAR(50), 
     [balance2]              VARCHAR(50), 
     [email]                 VARCHAR(50), 
     [customer_num]          VARCHAR(50), 
     [date]                  DATETIME, 
     [amount]                DECIMAL(12, 2) 
  ) 

INSERT INTO #the_table 
SELECT ard.rm_doc_descr, 
       ard.doc_num, 
       ard.description, 
       ard.post_date, 
       ard.original_order_value, 
       ard.amount_due, 
       ard.customer_name, 
       [31_to_60_days], 
       balance, 
       bottom.email, 
       bottom.customer_num, 
       ard.due_date, 
       ard.amount_due 
FROM   mhtrn..spvcustomeraddr AS bottom, 
       mhtrn..spvcustomer AS topw 
       LEFT JOIN mhtrn..spcvcustomerarbal AS cb 
              ON cb.customer_id = customer_num 
       LEFT JOIN spvcustomerardetail AS ard 
              ON ard.customer_num = topw.customer_num 
WHERE  topw.primary_bill_to_addr_code = bottom.address_code 
       AND topw.customer_num = bottom.customer_num 
       AND ( total_due > 0 
             AND [31_to_60_days] > 0 ) 
       AND ( topw.customer_num = 'MARATH6339' 
              OR topw.customer_num = 'STAPLE6662' ) 

--get a list of email addresses 
SELECT email, 
       Row_number() 
         OVER( 
           ORDER BY [email]) AS [Counter] 
INTO   #emails 
FROM   #the_table 
GROUP  BY email 

DECLARE @html VARCHAR(8000) 
DECLARE @Recipient VARCHAR(100) 
DECLARE @Customer_num VARCHAR(100) 
DECLARE @row INT = 1 

WHILE @row <= (SELECT Count(*) 
               FROM   #emails) 
  BEGIN 
      SET @Recipient= (SELECT [email] 
                       FROM   #emails 
                       WHERE  [counter] = @row) 

      --start building the html string 
      SELECT @html = '<!doctype html> <html> <head> <meta charset="utf-8"> <title>Untitled Document</title>  </head> <body> <p>Dear ' 

      SELECT @html = @html + [customer_name2] 
      FROM   #the_table 
      WHERE  [email] = @Recipient 
      GROUP  BY customer_name2 

      SELECT @html = @html 
                     + ',</p> <p>Our records indicate that payment on your account is overdue in the amount of $' 

      SELECT @html = @html 
                     + LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY, [31_to_60_days2] 
                     ), 
                     1), 
                     Len( 
                            CONVERT(VARCHAR(20), CONVERT(MONEY, [31_to_60_days2] 
                     ), 
                     1)) 
                     -0) 
      FROM   #the_table 
      WHERE  [email] = @Recipient 
      GROUP  BY [31_to_60_days2] 

      SELECT @html = @html 
                     + '. If the amount has already been paid, please disregard   this notice. Your total account balance is $' 

      SELECT @html = @html 
                     + LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY, [balance2]), 1), 
                     Len( 
                     CONVERT( 
                            VARCHAR(20), CONVERT(MONEY, [balance2]), 1))-0) 
      FROM   #the_table 
      WHERE  [email] = @Recipient 
      GROUP  BY [balance2] 

      SELECT @html = @html + '</p>' 
                     + N'<H3><font color="Black">Customer Statement</H3>' 
                     + 
N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:center;" >' 
        + N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;"> <th>Type</th> <th>Doc Number</th> <th>Description</th> <th>Date</th> <th>Invoice Total</th> <th>Amount Due</th></tr>' 

    -- SELECT 
    --     @html =  
    --         @html +  
    --         (' 
    --' + CONVERT(VARCHAR(6), [Date])  
    --+ '$' + LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY,Amount),1), LEN(CONVERT(VARCHAR(20), CONVERT(MONEY,Amount),1))-3) 
    --+ '$' + LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY,Amount),1), LEN(CONVERT(VARCHAR(20), CONVERT(MONEY,Amount),1))-3) 
    --+ '') 
    SELECT @html = @html + '<tr>' + '<td>' + [rm_doc_descr2] + '</td>' 
                   + '<td>' + [doc_num2] + '</td>' + '<td>' 
                   + [description2] + '</td>' + '<td>' 
                   + Cast([post_date2] AS VARCHAR(12)) 
                   + '</td>' + '<td>' + '$' 
                   + Isnull(LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY, 
                          [original_order_value2]), 1), Len(CONVERT(VARCHAR(20), 
                   CONVERT( 
                          MONEY, [original_order_value2]), 1))-0), '0.00') 
                   + '</td>' + '<td>' + '$' 
                   + LEFT(CONVERT(VARCHAR(20), CONVERT(MONEY, [amount_due2]), 1) 
                   , 
                   Len( 
                          CONVERT(VARCHAR(20), CONVERT(MONEY, [amount_due2]), 1) 
                   )-0) 
                   + '</td>' + '</tr>' 
    FROM   #the_table 
    WHERE  [email] = @Recipient 

    SELECT @html = @html + '</table></p>' 

    --finish building the html string  
    SELECT @html = @html 
                   + 
' <p>**Please note our payment address<br>   1239 South 700 West<br>   Salt Lake City UT  84104 **</p> <p>Have a great day!</p> <p>Paula Oliver<br>   Jorgenson Companies<br>   AR-Credit Manager<br>   paulao@jorgensoncompanies.com<br>   1239 South 700 West<br>   Salt Lake City, UT  84104<br>   PH: 801.214.7353<br>   FX: 801.493.0158</p></body> </html>' 

    DECLARE @mySubject VARCHAR(100) 

    SET @mySubject = 'Past Due Notice' 

    EXEC msdb..Sp_send_dbmail 
      @recipients = @Recipient, 
      @subject = @mySubject, 
      @profile_name = 'Send Mail', 
      @body_format = 'html', 
      @body = @html 

    SET @Row = @Row + 1 
END 
    RETURN;