You would need to use a UNION ALL to generate a row containing your column headers, like this:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'OPC@DOMAIN.COM',
@subject = N'test',
@body = N'test',
@query = N'SET NOCOUNT ON;
SELECT x.Name
FROM (
SELECT ''Name'' AS name,
1 AS OrderColumn
UNION ALL
SELECT ''-----------------------------'' AS name,
2 AS OrderColumn
UNION ALL
SELECT name,
3 AS OrderColumn
FROM sys.tables
) x
ORDER BY x.OrderColumn',
@execute_query_database = 'master',
@attach_query_result_as_file = 1,
@query_result_width = 500,
@query_no_truncate = 1;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato