问使用SQL Server发送%n封带有预览的HTML电子邮件

EN

--Create Reviews table
IF OBJECT_ID('EMailReviews') IS NOT NULL
    DROP TABLE EMailReviews;

CREATE TABLE EMailReviews
(
    Id              INT NOT NULL IDENTITY(1,1)
    --ProfileName?
    ,Recipients     VARCHAR(MAX)
    --,CopyRecipients       VARCHAR(MAX)
    ,EMailSubject   NVARCHAR(255)
    ,Body           NVARCHAR(MAX)
    ,BodyFormat     VARCHAR(20)
    ,ReplyTo        VARCHAR(MAX)
    ,DateReceived   DATETIME NOT NULL DEFAULT(SYSDATETIME())
    ,DateReviewed   DATETIME
    ,Processed      BIT NOT NULL DEFAULT(0)
);

--Create Procedure to insert the e-mail to review table and send notification to you.
IF OBJECT_ID('P_PutEMailToReviewQueue') IS NOT NULL
    DROP PROCEDURE P_PutEMailToReviewQueue;
GO
CREATE PROCEDURE P_PutEMailToReviewQueue
     @Recipients        VARCHAR(MAX)
    --,@CopyRecipients      VARCHAR(MAX)
    ,@EMailSubject      NVARCHAR(255)
    ,@Body              NVARCHAR(MAX)
    ,@BodyFormat        VARCHAR(20)
    ,@ReplyTo           VARCHAR(MAX)

AS
    DECLARE @Reviewer               VARCHAR(100)='revieweremail@email.com' /*your e-mail to review*/
    DECLARE @ReviewerEMailSubject   NVARCHAR(MAX)=''
    DECLARE @ReviewerEMailBody      NVARCHAR(MAX)=''

    --Put validations if necessary
    INSERT INTO EMailReviews(Recipients,EMailSubject,Body,BodyFormat,ReplyTo)
    VALUES (@Recipients,@EMailSubject,@Body,@BodyFormat,@ReplyTo)

    DECLARE @Id VARCHAR(100)=CAST(SCOPE_IDENTITY()AS VARCHAR)
    SET @ReviewerEMailBody =  'E-Mail Information below:'
                             +'<br>'
                             +'ID='+@Id
                             +'<br>'
                             +'Subject:'+ISNULL(@EMailSubject,'')
                             +'<br>'
                             +'Body:'+ISNULL(@Body,'')
                             +'<br>'
                             +'Recipients:'+ISNULL(@Recipients,'')
                             +'<br>'
                             +'ReplyTo:'+ISNULL(@ReplyTo,'')
                             +'<br>'
                             +'<br>'
                             +'<br>'
                             +'<br>'
                             +'After your review if you want to release the e-mail you need to run following script:'
                             +'<br>'
                             +'P_ReleaseEmail @Id   ='+@Id

    SET @ReviewerEMailSubject = 'Number '+@Id+' e-mail has been recieved. Review required!'

    EXEC msdb.dbo.sp_send_dbmail 
              @recipients   = @Reviewer
            , @subject      = @ReviewerEMailSubject
            , @body         = @ReviewerEMailBody
            , @body_format  ='HTML' 

GO
/*
P_PutEMailToReviewQueue
     @Recipients        ='test@test.com'
    ,@EMailSubject      ='Action Item Test'
    ,@Body              ='BLA BLA'
    ,@BodyFormat        ='HTML'
    ,@ReplyTo           ='noreply@test.com'
*/



--Create procedure to release the e-mail.
IF OBJECT_ID('P_ReleaseEmail') IS NOT NULL
    DROP PROCEDURE P_ReleaseEmail;
GO
CREATE PROCEDURE P_ReleaseEmail
     @Id    INT
AS
DECLARE
     @Recipients        VARCHAR(MAX)
    --,@CopyRecipients      VARCHAR(MAX)
    ,@EMailSubject      NVARCHAR(255)
    ,@Body              NVARCHAR(MAX)
    ,@BodyFormat        VARCHAR(20)
    ,@ReplyTo           VARCHAR(MAX)

    SELECT  @Recipients=Recipients
            ,@EMailSubject=EMailSubject
            ,@Body=Body
            ,@BodyFormat=BodyFormat
            ,@ReplyTo=ReplyTo
    FROM EMailReviews
    WHERE Id=@Id

    EXEC msdb.dbo.sp_send_dbmail 
              @recipients   = @Recipients
            , @subject      = @EMailSubject
            , @body         = @Body
            , @body_format  = @BodyFormat
            , @reply_to     = @ReplyTo

    UPDATE EMailReviews SET DateReviewed=GETDATE()
                           ,Processed=1
    WHERE Id=@Id

GO
/*
P_ReleaseEmail
     @Id    =1;

select * from EMailReviews;
*/