title: Sending Mail Using SQL Server 2000
body: <p>Duplicate of:
  <a href="https://stackoverflow.com/questions/1620508/automated-smtp-not-mapi-emails-using-sql-server-job-scheduler">Automated SMTP (not MAPI) emails using SQL Server Job Scheduler</a></p>
  
  <hr>
  
  <p>I want to send mail using Stored Procedure using SQL Server 2000 using windows smtp component. I don't want third party component.My SMTP server name ='mail.met.net'.how to send mail using the sql server 2000?</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_send_mail]
    @from varchar(500) ,
    @password varchar(500) ,
    @to varchar(500) ,  
    @subject varchar(500),
    @body varchar(4000) ,
    @bodytype varchar(10),
    @output_mesg varchar(10) output,
    @output_desc varchar(1000) output
  AS
  DECLARE @imsg int
  DECLARE @hr int
  DECLARE @source varchar(255)
  DECLARE @description varchar(500)
  
  EXEC @hr = sp_oacreate 'cdo.message', @imsg out
  
  --SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
  EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
  
  --SMTP Server
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 
    'mail.met.net' 
  
  --UserName
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', 
    @from 
  
  --Password
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 
    @password 
  
  --UseSSL
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value', 
    'True' 
  
  --PORT 
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value', 
    '465' 
  
  --Requires Aunthentication None(0) / Basic(1)
  EXEC @hr = sp_oasetproperty @imsg, 
    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 
    '1' 
  
  EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
  EXEC @hr = sp_oasetproperty @imsg, 'to', @to
  EXEC @hr = sp_oasetproperty @imsg, 'from', @from
  EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject
  
  -- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
  
  EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
  EXEC @hr = sp_oamethod @imsg, 'send', null
  
  SET @output_mesg = 'Success'
  
  -- sample error handling.
  IF @hr &lt;&gt;0 
   SELECT @hr
   BEGIN
    EXEC @hr = sp_oageterrorinfo null, @source out, @description out
    IF @hr = 0
    BEGIN
     --set @output_desc = ' source: ' + @source
     set @output_desc =  @description
    END
   ELSE
   BEGIN
    SET @output_desc = ' sp_oageterrorinfo failed'
   END
   IF not @output_desc is NULL
     SET @output_mesg = 'Error'
  END
  EXEC @hr = sp_oadestroy @imsg
  GO
  </code></pre>