No way you can only change the sent_status and make the email reprocess and resend. What we need to do is retrieve the fail item and called the Stored Procedure sp_send_dbmail again. Template to resend DB email, you can replace the mail item id accordingly:

DECLARE @to varchar(max),@copy  varchar(max),@title nvarchar(255),@msg nvarchar(max),@bodyformat VARCHAR(20),
@query_result_header BIT, @query varchar(max),@attach_query_result_as_file BIT, @query_result_width INT,@query_result_separator char(1)

SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body, @bodyformat = body_format,
@query_result_header = Query_result_header, @query = query,
@attach_query_result_as_file  = attach_query_result_as_file,
@query_result_width = query_result_width,
@query_result_separator = query_result_separator
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id = 33174

EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@copy_recipients = @copy,
@body = @msg,
@subject = @title,
@body_format = @bodyformat,
@query_result_header= @query_result_header,
@query =@query,
@attach_query_result_as_file =@attach_query_result_as_file,
@query_result_width =@query_result_width,
@query_result_separator= @query_result_separator;

fi_resend

Advertisements