Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: How do I send email with SQL 2005 Express through SMTP?

    Is it possible? Coz I heard only the standard edition and above have this function.

    If not, is there any workaround or 3rd party solution to do the job with just the express edition?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Haven't tested it for a while, but you see the idea:
    Code:
    if object_id('dbo.sp_SMTPMail') is not null
       drop procedure dbo.sp_SMTPMail
    go
    create procedure dbo.sp_SMTPMail (
       @SenderName       varchar(100), 
       @SenderAddress    varchar(100), 
       @RecipientName    varchar(100), 
       @RecipientAddress varchar(100), 
       @Subject          varchar(200), 
       @Body             varchar(8000))
    as
       set nocount on
       declare @oMail int --Object reference 
       declare @resultcode int  
       exec @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail out  
       if @resultcode = 0 begin 
          exec @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress 
          if @resultcode != 0 begin
             raiserror ('Failed to set property FROM (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
          exec @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress  
          if @resultcode != 0 begin
             raiserror ('Failed to set property TO (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
          exec @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject  
          if @resultcode != 0 begin
             raiserror ('Failed to set property SUBJECT (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
          exec @resultcode = sp_OASetProperty @oMail, 'Body', @Body   
          if @resultcode != 0 begin
             raiserror ('Failed to set property BODY (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
          exec @resultcode = sp_OAMethod @oMail, 'Send', NULL   
          if @resultcode != 0 begin
             raiserror ('Failed to execute method SEND (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
          exec sp_OADestroy @oMail 
          if @resultcode != 0 begin
             raiserror ('Failed to destroy oMail object (Error: %d)', 15, 1, @resultcode)
             return (1)
          end
       end else begin
          raiserror ('Failed to create CDONTS.NewMail object (Error: %d)', 15, 1, @resultcode)
          return (1)
       end
       return (0)
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    thanks! I will try it out later!

    In the mean time, I found a work around using this interesting program called mailsend http://www.muquit.com/muquit/softwar.../mailsend.html

  4. #4
    Join Date
    Oct 2006
    Posts
    115
    hi guys, I encountered a problem. When I try to run the mailsend programme through a variable via "exec xp_cmdshell", I will hit error like this:
    "Could not open tmp file "./mailsenda03520" for writing (for ABC.XLS)"

    where ABC.XLS is the file I want to send out. Anybody also use the mailsend program to send out mail?

    Going to try out rdjabarov's method now......

Posting Permissions

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