Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




Send mail using Sql server

To send the mail using sql server. you need to enable the Object creation permission, because for sending the mail you need to enable the option to execute the storeprocedure to send mail from surface area.

sp_OACreate : This is the system procedure we need to run in sql server if we want to send the mail.

After that Execute the below procedure, Passe the values and Press F5.

Create Procedure sp_sendSMTPMail 
      @SenderName varchar(100),
      @SenderAddress varchar(100),
      @RecipientName varchar(100),
      @RecipientAddress varchar(100),
      @Subject varchar(200),
      @Body varchar(8000),
      @MailServer varchar(100) = 'localhost' 
      AS  
      SET nocount on 
      declare @oMail int --Object reference
      declare @resultcode int 
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT 
      if @resultcode = 0
      BEGIN
            EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress 
            EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName,
           @RecipientAddress 
            EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
            EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body 
            EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL 
            EXEC sp_OADestroy @oMail
      END  
 
      SET nocount off
GO
Share this article   |    Print    |    Article read by 3841 times
Author:
Rohit kakria
I am software developer
Related Articles:
Related Interview Questions: