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
PO Date:2014-11-26 PO ID:PO1 SAP Ref:0001106102 GRN:1067 DealerID:045 Dealer Name:ABC(Pvt)Ltd. 2 Status:New
Format Requested By Client in a table like format
PO Date |PO ID| SAP Ref| GRN|DealerID|Dealer Name|Status
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'
, IsCancelled = 1
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
WHERE IsCancelled IS NULL;
-- Commit transaction
COMMIT TRAN
END TRY
BEGIN CATCH
-- Get error message, severity and satus information
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
-- Rollback transaction
ROLLBACK TRAN;
-- Log error message details
INSERT INTO _ERROR_LOG(Module, SubModule, Text, Date)
VALUES('SAP', '[ImpCancelledGRN]', @ErrorMessage, GETDATE());
-- Raise error
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0;
END
SET NOCOUNT OFF
GO
Best Answer
Here how can you create HTML body part of your mail.
Step 1:
Step 2: create database email profile
Step 3: Send Email