Check Failed Emails and ReSend Emails Script using DataBaseMail in SQL Server

Check Failed Emails and ReSend Emails  Script using DataBaseMail in SQL Server:

Failed Email Details are saved in below bolded Table in SQL Server

SELECT  mailitem_id,recipients,subject,body,send_request_date FROM msdb.dbo.sysmail_faileditems
where convert(varchar(10),send_request_date,121) Between convert(varchar(10),getdate()-2,121) and convert(varchar(10),getdate(),121)

order by send_request_date desc

So we can see below Script to find and resend Failed Emails using dbmail.

Script:

SET NOCOUNT ON;
GO

DECLARE @EmailFailedTable TABLE(id INT IDENTITY,mailitem_id int,recipients varchar(max),subject varchar(max),body varchar(max))

INSERT INTO @EmailFailedTable (mailitem_id,recipients,subject,body)

SELECT  mailitem_id,recipients,subject,body FROM msdb.dbo.sysmail_faileditems
where convert(varchar,send_request_date,121) between convert(varchar,getdate()-7,121) and convert(varchar,getdate(),121)


DECLARE @Rec VARCHAR(MAX),@Sub varchar(MAX),@body varchar(max)

DECLARE curFailedEmail CURSOR DYNAMIC FOR

SELECT recipients,subject,body  FROM @EmailFailedTable

OPEN curFailedEmail

BEGIN

FETCH NEXT FROM curFailedEmail INTO @Rec,@Sub,@body

WHILE @@FETCH_STATUS = 0
 
BEGIN

PRINT 'Rec : '+ convert(varchar(100),@Rec)+', Sub : '+ convert(varchar(max),@Sub)+ ', body : '+convert(varchar(max),@body)

 EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'Intimate',
@recipients = @Rec,
@body = @body,
@subject = @Sub

FETCH NEXT FROM curFailedEmail INTO @Sub,@body,@Rec
    END
END
CLOSE curFailedEmail

DEALLOCATE curFailedEmail