SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Resend an email sent from SQL Server using TSQL

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:



2018-06-05 19_52_48

Within these views there are a lot of columns at your disposal to narrow down your search such as:


recipients copy_recipients







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 @copy_recipients VARCHAR(MAX);
DECLARE @blind_copy_recipients VARCHAR(MAX);
DECLARE @subject NVARCHAR(510);
DECLARE @body_format VARCHAR(20);
DECLARE @importance VARCHAR(6);
DECLARE @sensitivity VARCHAR(12);
DECLARE @attachment BIT;

--Get the information for the email
@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

--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

RAISERROR('The mailitem_id specified (%d) uses a query or attachments, unfortunately this script cannot send your email',11,0,@mailitemID)

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.

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.


Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...