Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Stored Procedure that Emails In HTML

    I presently have a Access macro that emails conditionals in a report to users in web format HTML. I would like to turn that into a stored procedure, but having some difficutly can anyone out there assit me please??

  2. #2
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Hi,
    I use this stored procedure to send mail from sql 2000
    hope it's usefull for u

    Code:
    CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
       @From varchar(100) ,
       @To varchar(100) ,
       @Subject varchar(100)=" ",
       @Body varchar(4000) =" "
    /*********************************************************************
    
    This stored procedure takes the parameters and sends an e-mail. 
    All the mail configurations are hard-coded in the stored procedure. 
    Comments are added to the stored procedure where necessary.
    References to the CDOSYS objects are at the following MSDN Web site:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
    
    ***********************************************************************/ 
       AS
       Declare @iMsg int
       Declare @hr int
       Declare @source varchar(255)
       Declare @description varchar(500)
       Declare @output varchar(1000)
    
    --************* Create the CDO.Message Object ************************
       EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
    
    --***************Configuring the Message Object ******************
    -- This is to configure a remote SMTP server.
    -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
       EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    -- This is to configure the Server Name or IP address. 
    -- Replace MailServerName by the name or IP of your SMTP Server.
       EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.server.com' 
    
    --Loginconfiguratie SMTP server
      EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/senduser").Value', 'LOGINname' 
      EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'PAssword' 
    
    -- Save the configurations to the message object.
       EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    
    -- Set the e-mail parameters.
       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, 'HTMLBody', @Body
       EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
    
    -- Sample error handling.
       IF @hr <>0 
         select @hr
         BEGIN
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               RETURN
             END
         END
    
    -- Do some error handling after each step if you have to.
    -- Clean up the objects created.
       EXEC @hr = sp_OADestroy @iMsg

    Cheers Wimmo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •