How to send email from a table in SQL Server

Create a new table named System Generated Email in SQL Server Database:

 CREATE TABLE [dbo].[B$System Generated Email](  
      [No] [int] NOT NULL,  
      [To Address] [varchar](250) NOT NULL,  
      [From Address] [varchar](100) NOT NULL,   
      [From Address Title] [varchar](250) NOT NULL,
      [CC Address] [varchar](250) NOT NULL,    
      [Email Subject] [varchar](MAX) NOT NULL,  
      [Email Body] [varchar](MAX) NOT NULL,  
      [Sent] [tinyint] NOT NULL,  
      [Email Sending Date] [datetime] NOT NULL,  
      [Email Sending Time] [datetime] NOT NULL,  
 CONSTRAINT suppliers_pk PRIMARY KEY (No)  
 )  

This table will contains all system generated emails. Now we need to write a SQL Procedure which will create emails from System Generated Email Table and send through the SQL email profile.

Email profile can be found in Database Mail option:

SQL code for sending Email:

 USE msdb  
 GO  
 DECLARE @email_id varchar(250), @from_mail varchar(250), @cc_mail varchar(MAX),   
           @title varchar(250),@subject varchar(250), @mail_body varchar(MAX),  
           @No BIGINT, @max_id BIGINT, @Error Int, @lineNo int  
 DECLARE db_cursor CURSOR FOR   
 SELECT No        
 FROM [dbo].[System Generated Email]   
 WHERE [Sent] = 0  
 OPEN db_cursor    
 FETCH NEXT FROM db_cursor INTO @No    
 WHILE @@FETCH_STATUS = 0    
 BEGIN   
      SELECT   
         @lineNo = [No],  
            @email_id = [To Address],  
            @from_mail = [From Address Title] + '<' +[From Address] +'>',  
            @cc_mail = [CC Address1],  
            @subject = [Email Subject],  
            @mail_body = [Email Body1]   
      FROM [dbo].[System Generated Email]   
      WHERE [Sent] = 0 AND [No] = @No;  
      UPDATE      [dbo].[System Generated Email]   
      SET [Sent] = 1, [Email Sending Date] = CONVERT (date, SYSDATETIME()),   
                     [Email Sending Time] = '1754-01-01 ' + CAST(CONVERT (time(3), SYSDATETIME()) AS VARCHAR)  
      WHERE      [No] = @No  
      SELECT @Error = @@ERROR  
      print @Error  
      if @Error = 0   
      BEGIN  
           EXEC sp_send_dbmail @profile_name='TEST',  
                               @recipients= @email_id,  
                               @from_address=@from_mail,  
                               @copy_recipients = @cc_mail,  
                               @body_format='HTML',  
                               @subject=@subject,  
                               @body=@mail_body  
      END  
      ELSE   
      BEGIN  
           SET @subject = 'Error occurred for entry No ' + @lineNo  
           EXEC sp_send_dbmail @profile_name='TEST',  
                               @recipients= 'email@email.com',  
                               @from_address=@from_mail,  
                               @copy_recipients = @cc_mail,  
                               @body_format='HTML',  
                               @subject= @subject,  
                               @body='Error Ocured for sending mail'       
      END  
   FETCH NEXT FROM db_cursor INTO @No   
 END    
 CLOSE db_cursor    
 DEALLOCATE db_cursor  

here we take only not sent email records then create and send emails accordingly. We also update the Sent column with true so that email sent only once when run the procedure.

This procedure can be add to SQL schedule so system can send email automatically.