patternsqlModerate
Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2
Viewed 0 times
2008emailneedsqldatabaseformattedviamailserversend
Problem
My requirement is to create the automated email as ( A formatted HTML Email via Database Mail) The basic requirement has been completed where when the Job is triggered it inserts the data into a Table and that information has been scheduled to be sent as an Email,but now the client has requested in this format.
Current Format that I have done
Format Requested By Client in a table like format
This is the SQL Query that I use to Insert the Data in to the Email_Table(_ERROR_MAIL) and this needs to be coveted according to the requirement
```
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ImpCancelledGRN')
BEGIN
DROP PROCEDURE [dbo].[ImpCancelledGRN]
END
GO
CREATE PROCEDURE [dbo].[ImpCancelledGRN]
WITH ENCRYPTION
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
-- Begin transaction
BEGIN TRAN
INSERT INTO _ERROR_MAIL(Recipients, Subject, CreationDate, IsNew, LastModifiedBy, Body)
SELECT 'noreply-Email@Adress.com', 'SAP CANCELLED GRN', GETDATE(), 1, 'sapws',
'PO Date:' + CONVERT(VARCHAR(10),P.Date,120) +
' PO ID:' + P.ID +
' SAP Ref:' + P.ID2 +
' GRN:' + G.ID +
' Dealer ID:' + D.ID +
' Dealer Name:' + D.Name +
' Status:' + CASE WHEN G.SubmittedDate IS NULL THEN 'New' ELSE 'Dealer Submitted' END
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
INNER JOIN Distributor D ON D.UID = G.DistributorUID
INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID AND P.UID = G.POTxnUID
WHERE IsCancelled IS NULL;
UPDATE TxnGRN
SET ExpiryDate = GETDATE()
, SAPCancellationDate = I.Date
, SAPCancelledBy = 'SAP'
Current Format that I have done
PO Date:2014-11-26 PO ID:PO1 SAP Ref:0001106102 GRN:1067 DealerID:045 Dealer Name:ABC(Pvt)Ltd. 2 Status:NewFormat Requested By Client in a table like format
PO Date |PO ID| SAP Ref| GRN|DealerID|Dealer Name|StatusThis is the SQL Query that I use to Insert the Data in to the Email_Table(_ERROR_MAIL) and this needs to be coveted according to the requirement
```
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ImpCancelledGRN')
BEGIN
DROP PROCEDURE [dbo].[ImpCancelledGRN]
END
GO
CREATE PROCEDURE [dbo].[ImpCancelledGRN]
WITH ENCRYPTION
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
-- Begin transaction
BEGIN TRAN
INSERT INTO _ERROR_MAIL(Recipients, Subject, CreationDate, IsNew, LastModifiedBy, Body)
SELECT 'noreply-Email@Adress.com', 'SAP CANCELLED GRN', GETDATE(), 1, 'sapws',
'PO Date:' + CONVERT(VARCHAR(10),P.Date,120) +
' PO ID:' + P.ID +
' SAP Ref:' + P.ID2 +
' GRN:' + G.ID +
' Dealer ID:' + D.ID +
' Dealer Name:' + D.Name +
' Status:' + CASE WHEN G.SubmittedDate IS NULL THEN 'New' ELSE 'Dealer Submitted' END
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
INNER JOIN Distributor D ON D.UID = G.DistributorUID
INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID AND P.UID = G.POTxnUID
WHERE IsCancelled IS NULL;
UPDATE TxnGRN
SET ExpiryDate = GETDATE()
, SAPCancellationDate = I.Date
, SAPCancelledBy = 'SAP'
Solution
Here how can you create HTML body part of your mail.
Step 1:
Step 2: create database email profile
Step 3: Send Email
Step 1:
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = '' ;
SET @TableHead = '' + ''
+ 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
+ '' + '' + '' + 'Report generated on : '
+ CONVERT(VARCHAR(50), GETDATE(), 106)
+ ' '
+ ' PO Date'
+ 'PO ID'
+ 'SAP Ref'
+ 'GRN'
+ 'DealerID'
+ 'Dealer Name'
+ 'Status' ;
SET @Body = ( SELECT td = CONVERT(VARCHAR(10), P.Date, 120), '',
td = P.ID, '',
td = P.ID2, '',
td = G.ID, '',
td = D.ID,'',
td = D.Name,'',
td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
ELSE 'Dealer Submitted'
END, ''
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
INNER JOIN Distributor D ON D.UID = G.DistributorUID
INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
AND P.UID = G.POTxnUID
WHERE IsCancelled IS NULL
FOR XML RAW('tr'),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTailStep 2: create database email profile
Step 3: Send Email
EXEC sp_send_dbmail
@profile_name='DatabaseEmailProfile',
@copy_recipients ='aasc@stackexchange.com',
@recipients='aa.sc@outlook.com',
@subject='Query Result',
@body=@Body ,
@body_format = 'HTML' ;Code Snippets
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
+ '</style>' + '</head>' + '<body>' + 'Report generated on : '
+ CONVERT(VARCHAR(50), GETDATE(), 106)
+ ' <br> <table cellpadding=0 cellspacing=0 border=0>'
+ '<tr> <td bgcolor=#E6E6FA><b>PO Date</b></td>'
+ '<td bgcolor=#E6E6FA><b>PO ID</b></td>'
+ '<td bgcolor=#E6E6FA><b>SAP Ref</b></td>'
+ '<td bgcolor=#E6E6FA><b>GRN</b></td>'
+ '<td bgcolor=#E6E6FA><b>DealerID</b></td>'
+ '<td bgcolor=#E6E6FA><b>Dealer Name</b></td>'
+ '<td bgcolor=#E6E6FA><b>Status</b></td></tr>' ;
SET @Body = ( SELECT td = CONVERT(VARCHAR(10), P.Date, 120), '',
td = P.ID, '',
td = P.ID2, '',
td = G.ID, '',
td = D.ID,'',
td = D.Name,'',
td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
ELSE 'Dealer Submitted'
END, ''
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
INNER JOIN Distributor D ON D.UID = G.DistributorUID
INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
AND P.UID = G.POTxnUID
WHERE IsCancelled IS NULL
FOR XML RAW('tr'),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTailEXEC sp_send_dbmail
@profile_name='DatabaseEmailProfile',
@copy_recipients ='aasc@stackexchange.com',
@recipients='aa.sc@outlook.com',
@subject='Query Result',
@body=@Body ,
@body_format = 'HTML' ;Context
StackExchange Database Administrators Q#83776, answer score: 15
Revisions (0)
No revisions yet.