SQL 2008 Database Mail Resend

Try this query to resend failed items in the Database Mail queue.

declare @mailItem int
declare @mailRequest nvarchar(max)
declare @mailRecipient nvarchar(max)
declare @mailSubject nvarchar(255)
declare @mailSendDate datetime
declare @statusMsg nvarchar(400)
declare @rc int

declare curFailedMail cursor fast_forward for
  SELECT [mailitem_id]
  FROM [msdb].[dbo].[sysmail_faileditems]

open curFailedMail
fetch next from curFailedMail into @mailItem

while @@fetch_status = 0 
begin -- fetch loop
    -- Create request xml
    SET @mailRequest = '<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) + N'</MailItemId></requests:SendMail>'

    -- put the request on the queue.
    EXEC @rc = msdb..sp_SendMailQueues @mailRequest
    IF @rc = 0
    BEGIN -- resend success
        set @statusMsg = N'Mailitem "' + convert(nvarchar(20), @mailItem) + '" '
            + N', was added to the queue for re-sending.'
        RAISERROR(@statusMsg, 10, 1) WITH NOWAIT
    END -- resend success
    ELSE
    BEGIN -- resend failure
         RAISERROR(14627, 16, 1, @rc, 'send mail') WITH LOG
    END -- resend failure
    
    fetch next from curFailedMail into @mailItem

end -- fetch loop

close curFailedMail
deallocate curFailedMail