Database Mail with HTML formatting

Question

I am working on sending information to our customers about the checks they may expect in mail soon. I wanna know how do I put check num information in the body of the email as right now I am just generating a table which gives them remittance information.

Basically I want the body of the email to say

" Check num : 1234567 has been issued to you. So far I have the following query

 declare @docnum   nvarchar(50) 
declare @numatcard  nvarchar(50) 
declare @checknum nvarchar(50) 
declare @checkAmt nvarchar(100)
declare @EMaIL     nvarchar(200)
declare @Date      nvarchar(200)
declare @table    nvarchar(max)
declare @message   nvarchar(Max)
declare @cardcode  nvarchar(Max)
select 
 @docnum   = t2.docnum,
 @Cardcode=t3.cardcode,  
 @numatcard  = t2.numatcard,
@checknum = t5.checknum,
@Checkamt= T5.CheckSum,
@EMAIL= T3.E_Mail ,
@Date=Convert(Nvarchar(12),T0.Docdate,101) 


from
SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
left JOIN SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
where T0.[DocDate]=CAST(getdate()-1 as date)
--and 
--T5.CheckNum=32770
--T0.[DocDate]=CAST(getdate()as date)and t3.cardcode='CHIEF'

set @message ='<th><td>Dear Vendor,</th></td></br> 
Check num : ' + @checknum + ' in the amount of ' + @checkAmt+  ' has been processed and will be mailed within 48 hours. Please see remittance information below.</br> 

If you have any questions or concerns regarding this payment please contact a member of our Accounts Payable staff.</br></br>

Regards,</br>
</br>
Accounts Payable Department</br>
</br>
</br>
</p>'

DECLARE EmailCursor CURSOR FOR
Select  @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date
from SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
left JOIN Solitude.SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
where T0.[DocDate]=CAST(getdate()-1 as date)

OPEN EmailCursor

FETCH NEXT FROM EmailCursor INTO @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date


WHILE (@@FETCH_STATUS = 0)

BEGIN

EXEC msdb.dbo.sp_send_dbmail 
      @profile_name = 'abc',
      @recipients=@email,
    @subject = 'Remittance',
    @body = @message,
    @body_format = 'HTML'
FETCH NEXT FROM EmailCursor INTO @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date

END
Close EmailCursor
Deallocate EmailCursor

Ian Kenney · Accepted Answer · 2013-06-17T16:15:27.400

0

Something like :

 SELECT @body = 'Check num : ' + convert(varchar(50),t5.checknum) +' has been issued to you' 
        FROM       [dbo].[OVPM]  T0 
        INNER JOIN [dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
        LEFT JOIN  [dbo].VPM1 T5 on T0.DocEntry=T5.docnum
        INNER JOIN [dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
        LEFT JOIN  [dbo].OCRD T3 on T3.CardCode=T0.CardCode

obviously make the from clause return the correct check num

EDIT

maybe this simplified example will be a little clearer

declare @docnum   nvarchar(50) 
declare @numcard  nvarchar(50) 
declare @checknum nvarchar(50) 
declare @table    nvarchar(max)
declare @message   nvarchar(200)

select  @docnum   = t0.docnum, 
        @numcard  = t2.numcard
        @checknum = t5.checknum
from    .......

set @message = 'Check num : ' + @checknum + ' has been issued to you' 
set @table   = '<table><tr><th>Docnum</th><th>numcard</th><th>checknum</th</tr>' +
                '<tr><td>' + @docnum + '</td>' +
                '<td>' + @numcard + '</td>' +
                '<td>' + @checknum + '</td></tr></table>'

edited Jun 17 '13 at 16:15

answered Jun 17 '13 at 13:26

Ian Kenney

6,376
1
25
44

How do I make it return same check num which I am pulling for the table? Thanks for your reply. – user2363530 Jun 17 '13 at 13:57
Well assuming that there is only one cheque number returned in the table, using the same where clause would do it ? – Ian Kenney Jun 17 '13 at 14:01
In the real query I am not going to provide check num in the where clause. Each customer will get information about their own check num and remittance info accordingly. – user2363530 Jun 17 '13 at 14:05
Thankyou, this certainly helps . – user2363530 Jun 18 '13 at 12:18
How would I modify it to send email to all of the customers who got checks issued on a certain date. right now it only sends to just one customer whomever is on top of the list. – user2363530 Jun 18 '13 at 20:14
I would use a [cursor](http://msdn.microsoft.com/en-us/library/ms180169.aspx) for that – Ian Kenney Jun 18 '13 at 20:29
How do I tweak it so it doesn't send multiple emails to same vendor? I have change it to use cursor. – user2363530 Jun 19 '13 at 15:31
what do you want to send where the same vendor has multiple checknum ? – Ian Kenney Jun 19 '13 at 16:15
every vendor should get one email per check which was issued to them. actually query was also pulling information about the invoices which were taken in account to issue the check, so every row would have invoice num and same check num for and particular vendor and the db mail is sending one email per each row which is being pulled for any customer. Thank you for helping me. – user2363530 Jun 19 '13 at 16:26
probably worth starting a new question, with your current code – Ian Kenney Jun 19 '13 at 17:51
Ian, I have started new question on the follwing link, please have a look, if you may. http://stackoverflow.com/questions/17345498/using-cursor-in-dbmail-in-sql-server-2008 – user2363530 Jun 27 '13 at 15:08

Database Mail with HTML formatting

1 Answers1