Resending sysmail emails

Oops, mail server went down you determine. Then you think to yourself, that is not good for my sysmail I use with SQL. You realize sysmail in SQL 2005 retries and you feel better. Oh, it gets worse sysmail gave up after so many tries. Users didn't receive the emails as expect. Below is a handy stored proc to send them out again using the information in the sysmail_log table. Warning: This stored procedure does NOT include all fields. It only include recipients, subject, body, and uses the original mail profile that was used to send the email. Things like attachments, bcc, and other items are not included, but could easily be added. The stored procedure below takes the MailItemID from the msdb.dbo.sysmail_mailItems table as a parameter. Calling this stored procedure will resend the email with the specified MailItemID. create proc ResendEmailItem(@MailItemID as int) as Declare @MyMailProfile as nvarchar(128) Declare @MyRecipients as varchar(4000) Declare @MySubject as nvarchar(255) Declare @MyBody as nvarchar(4000) select distinct @MyMailProfile = p.name, @MyRecipients = i.recipients, @MySubject = i.subject, @MyBody = i.body from msdb.dbo.sysmail_log l join msdb.dbo.sysmail_mailitems i on (l.mailitem_id = i.mailitem_id) join msdb.dbo.sysmail_profile p on (i.profile_id = p.profile_id) where l.mailitem_id = @MailItemID EXEC msdb.dbo.sp_send_dbmail @profile_name = @MyMailProfile, @recipients = @MyRecipients, @subject = @MySubject, @body = @MyBody Example Usage: ResendEmailItem 3698 -- 3698 is the MailItemID