Berikut adalah cara send email dengan SQL Server :
Format :
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @from_address = ] 'from_address' ]
[ , [ @reply_to = ] 'reply_to' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @query_result_no_padding = ] @query_result_no_padding ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
Contoh :
1. Email Sederhana
Exec msdb.dbo.sp_send_dbmail@profile_name='PT. XYZ',@recipients='xyz@gmail.com',
@from_address = 'admin@gmail.com',
@subject='Judul Email',@body='Isi Email';
2. Email dengan multi penerima
Exec msdb.dbo.sp_send_dbmail@profile_name='PT. XYZ',@recipients='xyz@gmail.com',
@copy_recipients = 'atasan@gmail.com; wakil@gmail.com;',
@blind_copy_recipients = 'bos@gmail.com',
@from_address = 'admin@gmail.com',
@subject='Judul Email',@body='Isi Email';
3. Email dengan attachments File
Exec msdb.dbo.sp_send_dbmail@profile_name='PT. XYZ',@recipients='xyz@gmail.com',@from_address = 'admin@gmail.com',@subject='Judul Email',@body='Isi Email'@file_attachments = 'd:\file.txt';
4. Email dengan file yang dikirim dalam bentuk HTML.
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<style>' +
N'p{color: blue;}'+
N'.test{font-family: Arial; color: green;}'+
N'</style>'+
N'<H1 class="belajar">Mengirim Email Dengan bentuk HTML</H1>'+
N'<h4 style="font-family : Arial">Pengiriman Email menggunakan SQL Server</h4>'+
N'<table border = "1"> '+
N'<tr>' +
--1st Header
N'<th>'+
N'PLAN_ID'+
N'</th>' +
--2nd Header
N'<th>'+
N'Average'+
N'</th>' +
N'</tr>' +
--query start here
CAST((SELECT td=PLAN_ID, ' ',
td = AVERAGE, ' '
FROM HTSdb.dbo.TBL_KCT_7DF_PLAN
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table> '+
N'<h3 class="belajar"> Belajar Mengirim Email dengan SQL Server </h3>'+
N'<p>Pemula</p>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name='PT. XYZ',@recipients='xyz@gmail.com',
@from_address = 'admin@gmail.com',@subject='Judul Email',
@body=@tableHTML,@body_format='HTML';
