Resending the failed mails in DBmail

We had several failed mail items that were supposed to be sent out overnight by some SQL Jobs.
The following query was run to find the failed items

use msdb
GO

declare @StartDate datetime,
            @EndDate datetime


select * from dbo.sysmail_faileditems where last_mod_date >=@StartDate  and last_mod_date < @EndDate

--all the returned rows had sent_status as failed.

Here is a very cool script that will resend the failed items that I found in sqlservercentral.com. It worked like a charm!!!

declare @mailitem_id int, @sendmailxml
varchar(max), @rc
int,@StartDate datetime, @EndDate
datetime-- get the dates to use in the queryset @StartDate = convert(datetime, convert(char(8), GetDate(), 112))set @EndDate = @StartDate + 1declare cFailedMail cursor forSELECT
mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDateopen cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0
begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'-- Send the send request on queue.EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0BEGIN
 RAISERROR(14627, 16, 1, @rc, 'send mail')END

fetch next from cFailedMail into @mailitem_idend
close cFailedMail
deallocate cFailedMail