sqlsql-servert-sqlsp-send-dbmail

How to send email from SQL Server?


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.


Solution

  • 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