Email query output as HTML table – SQL Server

Create a stored procedure that converts query output to HTML table

USE [databasename]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
    DECLARE @borderColor char(7) = '#cccccc'

  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + '''' + name + '''' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = '''' + @headerRow + '''';

    SET @html = '''' + @headerRow + @html + ''
''; '; EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT END GO

Create a table that holds email recipients

USE [databasename]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[errorsRecipients](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [recipient] [varchar](512) NOT NULL,
    [enabled] [bit] NULL,
 CONSTRAINT [PK_errorsRecipients] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[errorsRecipients] ADD  DEFAULT ((1)) FOR [enabled]
GO

Insert some data in the recipients table

USE [databasename]
GO

INSERT INTO [dbo].[errorsRecipients]
           ([recipient]
           ,[enabled])
     VALUES
           ('some.emial.address@some.domain', 
            1)
GO

Execute email send

DECLARE @html            NVARCHAR(MAX)
DECLARE @emailRecipients NVARCHAR(MAX) 

SELECT @emailRecipients = COALESCE(@emailRecipients + '; ', '') + recipient 
    FROM errorsRecipients 
        WHERE enabled = 1

EXEC    [dbo].[spQueryToHtmlTable]
        @query = 'SELECT QUERY HERE',
        @html = @html OUTPUT

EXEC msdb.dbo.sp_send_dbmail  
@recipients=@emailRecipients,
@subject='The subject',
@body=@html,
@body_format='HTML',
@from_address='SENDER NAME <youraddress@yourdomain.com>',
@reply_to='noreply@somedomain.com';