How to send email from SQL Server?

Question

How can I send an email using T-SQL but email address is stored in a table? I want to loop through the table and be able to send email. I cannot find a good example of doing this so far.

Answer 1

Step 1) Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings.

Step 2)

RUN:

sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO 

Step 3)

USE msdb GO EXEC sp_send_dbmail @profile_name='yourprofilename', @recipients='[email protected]', @subject='Test message', @body='This is the body of the test message. Congrates Database Mail Received By you Successfully.' 

To loop through the table

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)  SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]  WHILE @id<=@max_id BEGIN     SELECT @email_id=email_id      FROM [email_adresses]      set @query='sp_send_dbmail @profile_name=''yourprofilename'',                         @recipients='''+@email_id+''',                         @subject=''Test message'',                         @body=''This is the body of the test message.                         Congrates Database Mail Received By you Successfully.'''      EXEC @query     SELECT @id=MIN(id) FROM [email_adresses] where id>@id  END 

Posted this on the following link http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

Answer 2

You can send email natively from within SQL Server using Database Mail. This is a great tool for notifying sysadmins about errors or other database events. You could also use it to send a report or an email message to an end user. The basic syntax for this is:

EXEC msdb.dbo.sp_send_dbmail   @recipients='[email protected]', @subject='Testing Email from SQL Server', @body='<p>It Worked!</p><p>Email sent successfully</p>', @body_format='HTML', @from_address='Sender Name <[email protected]>', @reply_to='[email protected]' 

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure. A database or Exchange admin might need to help you configure this. See http://msdn.microsoft.com/en-us/library/ms190307.aspx and http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server for more information.