Adding CASE statement to flag percentage available less than 5% as red ELSE green USE [TEST]
GO

CREATE PROCEDURE [dbo].[SQLDBA_DatabaseSpaceReport]
AS
BEGIN
DECLARE @dMaxLogDate Datetime = (SELECT TOP 1 LogDate FROM 
SQLDBA_PS_Get_DbaDatabaseSpace ORDER BY LogDate DESC)

DECLARE 
@html NVARCHAR(MAX), 
@table VARCHAR(MAX)

SET @html = --HTML layout--
            '&lt;html&gt;&lt;head&gt;' +
            '&lt;H1 style="color: #000000"&gt;Prod Databases with less than 10% 
            space available&lt;/H1&gt;' +
            '&lt;style&gt;' +
            'td {border: solid black 1px;padding-left:5px;padding-
            right:5px;padding-top:1px;padding-bottom:1px;font-
            size:9pt;color:Black;} ' +
            '&lt;/style&gt;' +
            '&lt;/head&gt;' +
            '&lt;body&gt;&lt;table cellpadding=0 cellspacing=0 border=0&gt;' +
            '&lt;tr bgcolor=33ffff&gt;'+
            '&lt;td align=center&gt;SqlInstance&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;Database&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;File Name&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;FileType&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;Used Space MB&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;Free Space MB&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;File Size MB&lt;/b&gt;&lt;/td&gt;' +  
            '&lt;td align=center&gt;Percent Used&lt;/b&gt;&lt;/td&gt;' +  
            '&lt;td align=center&gt;Auto Growth&lt;/b&gt;&lt;/td&gt;' + 
            '&lt;td align=center&gt;Auto Grow Type&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;';

   SELECT @table = CONVERT(nvarchar(max) ,
(SELECT 
td = [SqlInstance], 
'',
td = [Database],
'',
td = [FileName],
'',
td = [FileType],     
'',
td = CAST([UsedSpaceMB]as varchar(30)), 
'',
td = CAST([FreeSpaceMB]as varchar(30)),  
'',
td = CAST([FileSizeMB]as varchar(30)), 
'',
td = CAST([PercentUsed]as varchar(30)), 
'',
td = CAST([AutoGrowth]as varchar(30)), 
'',
td = [AutoGrowType]

FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace D
INNER JOIN [TEST].[dbo].[Environment] E on D.ComputerName = 
E.VirtualName AND E.ServerUse IN ('PROD','DR')
WHERE ( FileName NOT LIKE '%temp%' and FileName NOT LIKE 'SP%' and FileName 
NOT LIKE 'Share%' )
AND LogDate = @dMaxLogDate and AutoGrowth &gt; 0
ORDER BY [PercentUsed] desc

FOR XML PATH(N'tr'), TYPE));

SET @html = @html + @table + CHAR(10) + 
      N'&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;';

IF LEN(@HTML) &gt; 10

BEGIN

--print @HTML

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Tester@TEST.com',
@body_format =  'HTML',
@body= @HTML,
@subject = 'Alert!! Prod Databases With Less Than 10% Space Available!!',
@profile_name = 'SQL_DBA'

END

END


Something like this: (Not 100% sure about the code below or where to add it)

CASE WHEN PercentUsed &gt; 95 THEN 
'&lt;td align="Center"&gt;&lt;font face="Arial" size="1" color="#FF0000"&gt;&lt;b&gt;' +  
CAST([PercentUsed]as varchar(30)  +'&lt;/font&gt;&lt;/td&gt;' 
ELSE 
'&lt;td align="Center"&gt;&lt;font face="Arial" size="1" color="#40C211"&gt;&lt;b&gt;' +  
CAST([PercentUsed]as varchar(30)  +'&lt;/font&gt;&lt;/td&gt;'  
END +
'&lt;/tr&gt;'                                  
FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace
In this instance replacing td = CAST([PercentUsed]as varchar(30)), with something like:

td = '&lt;span style="color:' + CASE WHEN PercentUsed &gt; 95 THEN '#FF0000' ELSE '#40C211' + '"&gt;'
   + CAST([PercentUsed]as varchar(30))
   + '&lt;/span&gt;'
   ,


is probably what you are looking for. Or if you need to use font tags instead of more modern styling options (perhaps because some of your recipients have truly ancient email readers) then edit as needed.

Your inner query is using the FOR XML hack* to concatenate everything into a string, the column names forming tags (in this case they are all td) and the PATH parameter meaning each row gets wrapped in &lt;tr&gt;/&lt;/tr&gt;. For more information on forming XML document fragments (or in this case, HTML document fragments) this way see the official documentation such as https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server and for various tutorials on using this for string building more generally search for "TSQL FOR XML concatenation" or similar.

* I call it a hack as you are not actually returning an XML typed result, which is what the feature is designed for, it is being used here in a way that makes it behave like a string concatenation aggregate, the most common use for FOR XML in my experience, see articles like https://sqlblog.org/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-youre-looking-for for notes on this and other techniques to achieve the same result.