I use the following code (inside a scheduled job) to extract the data and email it in HTML table format:
(disclaimer - this is my version of similar code that was found online)
-- ************************************************************************************************-- Returns a list of scheduled jobs that ran the previous day, and includes:
-- status (success or failure), last run date and name of scheduled job
-- ************************************************************************************************DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
-- get scheduled job data ------------------------------------------------------
SET @xml =CAST(( SELECT
-- status
LTRIM(RTRIM(CASE WHEN jh.run_status = 1 THEN 'Succeeded' Else 'Failed' END)) AS 'td'
,'' -- formatting spacer
-- last run date
,LTRIM(RTRIM(RIGHT(jh.run_date,2) +' '+
DATENAME(MONTH, CONVERT(DATETIME, LEFT(jh.run_date,4) +'-'+
RIGHT(LEFT(jh.run_date,6),2)+'-'+
RIGHT(jh.run_date,2), 102))+' '+
LEFT(jh.run_date,4) )) AS 'td'
,'' -- formatting spacer
-- job name
,LTRIM(RTRIM(j.[name])) AS 'td'
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j
ON j.job_id = jh.job_id
INNER JOIN msdb..sysjobschedules js
ON j.job_id = js.job_id
WHERE
run_date >= DATEPART(YEAR, GETDATE()-1)*10000+
DATEPART(MONTH, GETDATE()-1)*100+
DATEPART(DAY, GETDATE()-1) -- yesterday's scheduled jobs
AND step_id = 0
ORDER BY jh.run_status
,[name]
,j.job_id
, jh.step_id
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
-- highlight any failed jobs with red background ------------------------------
SELECT @xml = REPLACE(@xml,'<td>Failed</td>','<td bgcolor=#FF3333>Failed</td>')
-- get name of SQL Server -----------------------------------------------------
DECLARE @server VARCHAR(5)
SELECT @server = [server] FROM msdb..sysjobhistory
-- send email with results in table format ------------------------------------
SET @body ='<html><H1><FONT color="blue">'+ @server +' - Scheduled Job Status</FONT></H1>
<body bgcolor="white">
<table bgcolor=#CCFFCC border = 1>
<tr bgcolor=#99FFCC>
<th>Last Run Status</th>
<th>Date Last Run</th>
<th>Job Name</th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'admin@xyz.com'-- for multiple reipients, use ";" as a separator
,@body = @body
,@body_format ='HTML'
,@subject ='Scheduled Jobs Status'
,@profile_name ='myMailProfileName'
hope it's useful 🙂