select * from msdb.dbo.sysmail_sentitems 

select		sent_date,
			send_request_user,
			subject,
			body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

-- This contains one row for each message that has an unsent or retrying status.
select * from msdb.dbo.sysmail_unsentitems 

select		sent_date,
			send_request_user,
			subject,
			body
from		msdb.dbo.sysmail_unsentitems
order by	sent_date desc 

-- This contains one row for each Database Mail message that has a failed status.
select * from msdb.dbo.sysmail_faileditems 

select		sent_date,
			send_request_user,
			subject,
			body
from		msdb.dbo.sysmail_faileditems
order by	sent_date desc 

---------------------------------------------------
-- msdb.dbo.sp_send_dbmail
---------------------------------------------------

DELETE FROM msdb.dbo.sysmail_sentitems

---------------------------------------------------
-- One recipient
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DefaultMailProfile',
    @recipients =	'ram.kdm@gmail.com' ,
    @body =			'Mail Test 1',
    @subject =		'Automated Mail Message 1' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- More than one recipient
---------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DefaultMailProfile',
    @recipients =	'ram.kdm@gmail.com  ; ramk@naya-tech.co.il' ,
    @body =			'Mail Test 2',
    @subject =		'Automated Mail Message 2' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- Unavailable Mail Address
---------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DefaultMailProfile',
    @recipients =	'ram.kdm@gmail.com  ; ramk@naya-tech.co.il ; MAILSHELOKAYAM@RAM.COM' ,
    @body =			'Mail Test 3',
    @subject =		'Automated Mail Message 3' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @copy_recipients
---------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =		'DefaultMailProfile',
    @recipients =		'ram.kdm@gmail.com' ,
	@copy_recipients =	'ramk@naya-tech.co.il',
    @body =				'Mail Test 4',
    @subject =			'Automated Mail Message 4' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @blind_copy_recipients (hidden mail recipient)
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com' ,
	@blind_copy_recipients =	'ramk@naya-tech.co.il',
    @body =						'Mail Test 5',
    @subject =					'Automated Mail Message 5' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @body_format as HTML
--------------------------------------------------- 

DECLARE @html_message varchar(500) =
'<!DOCTYPE html>
<html>
<body>
	<h1>Some Heading</h1>
	<p>Some paragraph.</p>
</body>
</html>'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com' ,
	@blind_copy_recipients =	'ramk@naya-tech.co.il',
    @body =						 @html_message,
	@body_format=				'HTML',
    @subject =					'Automated HTML Mail Message 6' ;

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @file_attachments
---------------------------------------------------
-- * semicolon-delimited list of file names to attach to the e-mail message.
-- * Files in the list must be specified as absolute paths.
-- * The attachments list is of type nvarchar(max).
-- * By default, Database Mail limits file attachments to 1 MB per file.

EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '2097152' ; --size is in bytes = 2MB

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com' ,
    @body =						'Mail Test',
    @subject =					'Automated Mail Message 7',
	@file_attachments=          'C:DBMailFileToSend.txt'

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @query (Plain Text)
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com' ,
    @body =						'Mail Test 7',
    @subject =					'Automated Mail Message 8',
	@query=						'SELECT * FROM Northwind.dbo.employees'

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @query (As HTML)
---------------------------------------------------

-- 1. Use the following script : SaveTableAsHTML
http://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm

-- 2. SaveTableAsHTML Basic Demo
EXECUTE northwind.dbo.SaveTableAsHTML @DBFetch = 'select * from Northwind.dbo.employees'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com',
    @subject =					'HTML Formated Query 1',
    @body_format =				'HTML',
    @query =	'EXECUTE northwind.dbo.SaveTableAsHTML
				@DBFetch = ''select * from Northwind.dbo.employees'''

---------------------------------------------------
-- @query (As HTML)
---------------------------------------------------

-- 1. Use the following script : SaveTableAsHTML
http://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com',
    @subject =					'HTML Formated Query 2',
    @body_format =				'HTML',
    @query =	'EXECUTE northwind.dbo.SaveTableAsHTML
				@DBFetch = ''SELECT categoryID , AVG(unitPrice) AS AvgPrice
							 FROM products
							 WHERE categoryID <> 3
							 GROUP BY categoryID
							 Having AVG(unitPrice) > 15 '''

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- Using CSS ...
---------------------------------------------------

-- Demo
EXEC SaveTableAsHTML @DBFetch = 'SELECT * FROM northwind.dbo.products WHERE unitPrice > 20',
@CSS =
'table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
 th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
 td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
 tr:hover td{background:#d0dafd;color:#339;}'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com',
    @subject =					'HTML Formated Query 3',
    @body_format =				'HTML',
    @query =
	'EXEC Northwind.dbo.SaveTableAsHTML @DBFetch = ''SELECT * FROM northwind.dbo.products WHERE unitPrice > 20'',
	@CSS =
	''table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
	 th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
	 td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
	 tr:hover td{background:#d0dafd;color:#339;}'''

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc 

---------------------------------------------------
-- @attach_query_result_as_file
-- @query_attachment_filename
---------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =				'DefaultMailProfile',
    @recipients =				'ram.kdm@gmail.com',
    @subject =					'HTML Formated Query 4',
    @body_format =				'HTML',
    @query =
	'EXEC Northwind.dbo.SaveTableAsHTML @DBFetch = ''SELECT * FROM northwind.dbo.products WHERE unitPrice > 20'',
	@CSS =
	''table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
	 th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
	 td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
	 tr:hover td{background:#d0dafd;color:#339;}''',
	@attach_query_result_as_file = 1 ,
	@query_attachment_filename   = 'SQLReport.html'

select		TOP 1 sent_date, send_request_user, subject, body
from		msdb.dbo.sysmail_sentitems
order by	sent_date desc