SQL Server Performance

how to send attachments through CDOSYS

Discussion in 'General Developer Questions' started by rajeev_id, Jul 20, 2004.

  1. rajeev_id New Member

    Hi
    This is working fine only attachments does not work.
    so how to send attachments through CDOSYS

    Here is my code:
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO



    ALTER PROCEDURE [dbo].[sp_send_cdosysmail]
    @From varchar(100) ,
    @To varchar(100) ,
    @Cc varchar(4000)=null ,
    @Bcc varchar(4000)=null ,
    @Subject varchar(100)=" ",
    @Body varchar(8000) =" ",
    @Attachments varchar(4000) =Null,
    @HTMLBodyFlg int=Null

    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar(255)
    Declare @description varchar(500)
    Declare @output varchar(1000)

    IF IS_SRVROLEMEMBER ('sysadmin') = 0
    BEGIN
    return 0
    END

    If @HTMLBodyFlg is Null
    Begin
    set @HTMLBodyFlg=0
    End


    --************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.ccipl.com'

    -- 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
    --New added
    EXEC @hr = sp_OASetProperty @iMsg, 'cc',@Cc
    EXEC @hr = sp_OASetProperty @iMsg, 'bcc',@Bcc


    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    IF @HTMLBodyFlg=0
    BEGIN
    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
    End
    Else
    BEGIN
    EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
    End
    --set @Attachments=null

    EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachments

    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
    IF @output is null
    RETURN 1
    Else
    RETURN 0
    END
    ELSE
    BEGIN
    --PRINT ' sp_OAGetErrorInfo failed.'
    RETURN 0
    END
    END

    -- Do some error handling after each step if you have to.
    -- Clean up the objects created.
    EXEC @hr = sp_OADestroy @iMsg
    RETURN 1




    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO



    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--
  2. Twan New Member

    Attachments should work ok, they work ok for me <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> I'll check my proc against yours when I get back into work in the morning...<br /><br />but they need to be specified relative to the SQL Server and accessible by the service account that is running SQL Server... so c: est.txt is the c drive on the server and not the workstation...<br /><br />Cheers<br />Twan
  3. Twan New Member

    Have just checked it against my proc and yes the call looks right, although if you want to attach multiple files, then you have to split it up into multiple AddAttachment calls

    the code from my proc is here, ignore the is_on_winnt bit, as I still have to support Windows NT hosted SQL Servers too

    Code:
    /*********************************************************************
     Add attachments
    *********************************************************************/ 
    Set @lastaction = 'AddAttachment'
    
    while datalength( @attachments ) > 0
    begin
    if charindex( '|', @attachments ) > 0
    begin
    set @attachment = left( @attachments, charindex( '|', @attachments ) - 1 )
    set @attachments = right( @attachments, datalength( @attachments ) - charindex( '|', @attachments ) )
    end
    else
    begin
    set @attachment = @attachments
    set @attachments = NULL
    end
    
    -- you can't seem to get a return code...
    if( @is_on_winnt = 1 )
    begin
    EXEC sp_OAMethod @msg_id, 'AttachFile', null, @attachment
    end
    else
    begin
    EXEC sp_OAMethod @msg_id, 'AddAttachment', null, @attachment
    
    -- kludgy way to get error messages in cases such as file not found, etc.
    EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
    IF @source = 'CDO.Message.1'
    goto error
    end
    end
    {/code]
    
    Cheers
    Twan 
  4. rajeev_id New Member

    Thanks Actually i was using my workstation path.Now it is runing fine.And thanks for the multiple Attachment Script.

    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--
  5. nikie New Member

    I have used CDOSYS to send mail it works fine.But attachment is a problem.I have added the following lines in my stored procedure.But it send s mail without ant attachment though the file exists.

    EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @file

    where @file = 'c: est est.xls' a parameter passed from the code to the sp.

    Please help me out.
    Edit/Delete Message
  6. nikie New Member

    I have used CDOSYS to send mail it works fine.But attachment is a problem.I have added the following lines in my stored procedure.But it send s mail without ant attachment though the file exists.

    EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @file

    where @file = 'c: est est.xls' a parameter passed from the code to the sp.

    Please help me out.
  7. MohammedU New Member

Share This Page