alter PROCEDURE [dbo].[SendMyMails]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @recipients varchar(20),
@subject varchar(50),
@body varchar(1000),
@custid varchar (8),
@firstname varchar (100),
@lastname varchar(100),
@ccexpdate varchar(8),
@bmonthlyactive varchar(1),
@dtmonthlynextpay varchar (50),
@dtmonthlypaid varchar (50),
@iemailoptin varchar (8),
@recipientemail varchar (80),
@sbarcode varchar (50),
@monthlyaccountid varchar (50),
@monthlyaccountdescription varchar (250),
@monthlyamount varchar(12),
@bodyformat varchar(12)
DECLARE Mail_Cursor CURSOR FOR
select C.lcustomerid, c.sFirstName,c.sLastName,c.sCCExpDate,c.bMonthlyActive,c.dtMonthlyNextPay,c.dtMonthlyPaid,c.iEmailOptIn,c.sEmail,c.sBarcode,c.lMonthlyAccountTypeId,v.sDescription,v.dblAmount
from Custtable as c
join AccountTypes as v on v.laccounttype = c.lMonthlyAccountTypeId
where bMonthlyActive = 1 and dtMonthlyPaid < Convert(datetime, Convert(int, GetDate())) and iEmailOptIn = '1'
OPEN Mail_Cursor;
FETCH NEXT FROM Mail_Cursor
INTO @custid, @firstname, @lastname, @ccexpdate, @bmonthlyactive, @dtmonthlynextpay, @dtmonthlypaid, @iemailoptin, @recipientemail, @sbarcode, @monthlyaccountid, @monthlyaccountdescription,@monthlyamount ;
declare @tmp_subject varchar(1000)
declare @tmp_body varchar(1000)
WHILE @@FETCH_STATUS = 0
BEGIN
set @tmp_subject = 'Your '+@monthlyaccountdescription+' Program at the Acme Company'
set @tmp_body = '<p>Dear '+@firstname+' '+@lastname+',</p></p>
<p>Your credit card ending with an expiration date of '+SUBSTRING(ltrim(rtrim(@ccexpdate)),1,2)+'/'+SUBSTRING(ltrim(rtrim(@ccexpdate)),3,2)+' has been charged in the amount of $'+@monthlyamount+' for the '+@monthlyaccountdescription+'.</p>
<p>Thank you for your business.</p>'
--Sending Mail
EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
@subject = @tmp_subject,
@body = @tmp_body,
@body_format = 'HTML';
FETCH NEXT FROM Mail_Cursor
Problem here --->> INTO @recipients, @subject, @body ;
END;
CLOSE Mail_Cursor;
DEALLOCATE Mail_Cursor;
END
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.