Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Red face Unanswered: HELP!!! CDONTS Attachment in SQL server

    Hi Everybody,
    This is my first post in the forum.
    Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

    I have been trying several methods and i'm stuck

    Right now, I'm using the next SP:

    DROP PROCEDURE ST_S_SendMail
    GO
    CREATE PROCEDURE ST_S_SendMail
    (@FROM NVARCHAR(255),
    @TO NVARCHAR(255),
    @SUBJECT NVARCHAR(255),
    @BODY NVARCHAR(4000))
    AS
    DECLARE @Object int
    DECLARE @Hresult int
    DECLARE @ErrorSource varchar (255)
    DECLARE @ErrorDesc varchar (255)
    DECLARE @V_BODY NVARCHAR(4000)

    DECLARE @hr int
    DECLARE @src varchar(255), @desc varchar(255)

    EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT

    IF @Hresult = 0 begin
    --SET SOME PROPERTIES

    SET @V_BODY = '' + @BODY
    EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
    EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
    EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
    EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY

    EXEC @Hresult = sp_OASetProperty @Object, 'MailFormat', 0

    --CALL ATTACHMENT METHOD
    EXEC @Hresult = sp_OAMethod @Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

    --CALL SEND METHOD
    EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL

    --DESTROY THE OBJECT
    EXEC @Hresult = sp_OADestroy @Object
    end
    else
    begin
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    end




    Thanks in advance for your help

  2. #2
    Join Date
    Nov 2003
    Posts
    1

    Thumbs up

    Have you tried the following code for sending the attachment mate???

    -- Check for multiple attachments separated by a semi-colon ';'.
    If @vcAttachments is not null
    Begin
    If right(@vcAttachments,1) <> ';'
    Select @vcAttachments = @vcAttachments + '; '
    Select @iPos = CharIndex(';', @vcAttachments, 1)
    While @iPos > 0
    Begin
    Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1)))
    Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos)
    EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @vcAttachment
    IF @iHr <> 0
    Begin
    EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
    Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) +
    char(13) + char(10) + 'Error adding attachment: ' +
    char(13) + char(10) + @vcErrSource + char(13) + char(10) +
    @vcAttachment
    End
    Select @iPos = CharIndex(';', @vcAttachments, 1)
    End
    End

    -------------------------------

    you can find more info about this at www.sqlservercentral.com

    Hope this helps....

    have a good one

  3. #3
    Join Date
    Nov 2003
    Location
    denver
    Posts
    11

    Re: HELP!!! CDONTS Attachment in SQL server

    Originally posted by saulo70
    Hi Everybody,
    This is my first post in the forum.
    Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

    I have been trying several methods and i'm stuck

    Right now, I'm using the next SP:

    DROP PROCEDURE ST_S_SendMail
    GO
    CREATE PROCEDURE ST_S_SendMail
    (@FROM NVARCHAR(255),
    @TO NVARCHAR(255),
    @SUBJECT NVARCHAR(255),
    @BODY NVARCHAR(4000))
    AS
    DECLARE @Object int
    DECLARE @Hresult int
    DECLARE @ErrorSource varchar (255)
    DECLARE @ErrorDesc varchar (255)
    DECLARE @V_BODY NVARCHAR(4000)

    DECLARE @hr int
    DECLARE @src varchar(255), @desc varchar(255)

    EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT

    IF @Hresult = 0 begin
    --SET SOME PROPERTIES

    SET @V_BODY = '' + @BODY
    EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
    EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
    EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
    EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY

    EXEC @Hresult = sp_OASetProperty @Object, 'MailFormat', 0

    --CALL ATTACHMENT METHOD
    EXEC @Hresult = sp_OAMethod @Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

    --CALL SEND METHOD
    EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL

    --DESTROY THE OBJECT
    EXEC @Hresult = sp_OADestroy @Object
    end
    else
    begin
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    end




    Thanks in advance for your help

    Here;s a sp which will make your life easier ...


    CREATE PROCEDURE [dbo].[sp_CDOMail]
    @To varchar(1000),
    @From varchar(100),
    @Subject varchar(100),
    @Body varchar(4000),
    @CC varchar(100) = null,
    @BCC varchar(100) = null,
    @FilePath varchar(100) = Null, --inlcude last slash
    @FileName varchar(100) = Null

    /************************************************** *******************

    This stored procedure takes the above parameters and sends an e-mail.
    All of the mail configurations are hard-coded in the stored procedure.
    Comments are added to the stored procedure where necessary.
    Reference to the CDOSYS objects are at the following MSDN Web site:
    http://msdn.microsoft.com/library/de..._messaging.asp


    DATE By Descript
    ------------------- ------------------ --------------------------------------------------
    11/01/03 SP --Modified to Send Attachments from the original query designed by Microsoft.

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

    Declare @attachment varchar(200)

    set @attachment = @FilePath + @FileName

    --************* 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/de..._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', 'Mozart'

    -- 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
    EXEC @hr = sp_OASetProperty @iMsg, 'CC', @CC
    EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC

    --Add Attachment if exists.
    if @Attachment <> ''
    EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment , @FileName

    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @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
    SELECT @output = @output + '
    Description: ' + @description
    Select @output
    END
    ELSE
    BEGIN
    PRINT ' sp_OAGetErrorInfo failed.'
    RETURN
    END
    END

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


    GO

    IF you still have problems email me @ shaileshpatangay@hotmail.com


    ---shailesh

  4. #4
    Join Date
    Nov 2003
    Posts
    2

    Smile

    Thanks a lot for your help

    It's working 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
  •