Sometimes emails from SQL Server go missing, especially when you share an inbox with colleagues. On most occasions it doesn’t always matter as the job that generated the email can simply be re ran to produce the email once again, but what about those emails that contain time specific information? we cannot simple just kick off the job again as it may be coded to evaluate right now and not provide us with the information that was sent 2 hours ago.
I was faced with exactly this issue the other day – i needed the information from the email that was sent at that given time so I wrote a bit of code to get the job done then decided that if I tidy this code up I could make it reusable so here is what I come up with:
Find the email you want to resend using one of the following views in msdb:
Within these views there are a lot of columns at your disposal to narrow down your search such as:
Once you have found the email you want to resend make a note of the mailitem_id and use the following code to resend the email:
--Set mail item id you want to re send DECLARE @mailitemID INT = -- MailItem_Id here e.g 2041 DECLARE @profilename NVARCHAR(128); DECLARE @recipients VARCHAR(MAX); DECLARE @copy_recipients VARCHAR(MAX); DECLARE @blind_copy_recipients VARCHAR(MAX); DECLARE @subject NVARCHAR(510); DECLARE @body VARCHAR(MAX); DECLARE @body_format VARCHAR(20); DECLARE @importance VARCHAR(6); DECLARE @sensitivity VARCHAR(12); DECLARE @query BIT; DECLARE @attachment BIT; --Get the information for the email SELECT @profilename = [profiles].[name], @recipients = [mail].[recipients], @copy_recipients = [mail].[copy_recipients], @blind_copy_recipients = [mail].[blind_copy_recipients], @subject = [mail].[subject], @body = [mail].[body], @body_format = [mail].[body_format], @importance = [mail].[importance] , @sensitivity = [mail].[sensitivity], @query = CASE WHEN [mail].[query] IS NULL THEN 0 ELSE 1 END, @attachment = CASE WHEN [mail].[file_attachments] IS NULL THEN 0 ELSE 1 END FROM msdb.dbo.sysmail_allitems mail INNER JOIN msdb.dbo.sysmail_profile profiles ON mail.profile_id = profiles.profile_id WHERE mailitem_id = @mailitemID; IF @query = 0 AND @attachment = 0 BEGIN --Re send the email EXEC msdb.dbo.sp_send_dbmail @profile_name = @profilename, @recipients = @recipients, @copy_recipients = @copy_recipients, @blind_copy_recipients = @blind_copy_recipients, @subject = @subject, @body = @body, @body_format = @body_format, @importance = @importance, @sensitivity = @sensitivity END ELSE BEGIN RAISERROR('The mailitem_id specified (%d) uses a query or attachments, unfortunately this script cannot send your email',11,0,@mailitemID) END
This will send the email exactly how it was sent originally, there are a couple of gotchas – This script will not handle attachments , i am sure that it is possible but I had no need for attachments but feel free to code that in yourself if you need it
Thanks for reading.