Sending Attachment with Cdosys

  • you HAVE to use the ",@rv out," parameter. if not it wont work

    EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT

    RG.

  • I have resolved this issue. Thanks.

  • Have tried the examples but the attachemnt did not turn up.

    Please could someone help

  • Clive,

    I answered your query on another thread but there seems to be a common problem with cdosys and attachments. Many people have posted their procs on this site and I used one of them and altered it for my own use. If you email me I will send you my proc to try, it works for me.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I hope this turns out to be useful to someone. I found the core of this code from another SQL site (before I found this one )

    If you're in a setup like us and cannot use CDONTS, this is a reasonable alternative, I think, using ASPMail by ServerObjects.com. You also have the ability to specify your own SMPT server, and with a few lines, make the SMTP server a parameter. It defaults to HTML formatting for the body. It also accepts attachments. If you send an attachment, it makes a feeble but reasonable attempt to remove the HTML tags so it can send as regular text (which is a function of the mail component).

    Regards -

    B

     
    
    CREATE Procedure WA_SENDMAIL

    @RecipientName varchar(50),
    @RecipientAddress varchar(50),
    @SenderName varchar(50),
    @SenderAddress varchar(50),
    @CCName VarChar(50),
    @CCAddress VarChar(50),
    @BCCName VarChar(50),
    @BCCAddress VarChar(50),
    @Subject varchar(100),
    @Body varchar(8000),
    @Attachment VarChar(100)

    AS

    Declare @MailBody VarChar(8000)
    Declare @MailServer VarChar(30)
    Set @MailServer = 'mail.db.pvt'

    Declare @Response VarChar(100)

    SET nocount on

    declare @oMail int --Object reference
    declare @resultcode int

    EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

    if @resultcode = 0
    BEGIN
    Set @Body = Convert(Char(8000),@Body)
    EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost',@MailServer
    EXEC @resultcode = sp_OASetProperty @oMail, 'FromName',@SenderName
    EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress',@SenderAddress
    EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL,@RecipientName, @RecipientAddress
    If @CCName <> '' AND @CCAddress <> ''
    Begin
    EXEC @resultcode = sp_OAMethod @oMail, 'AddCC',NULL, @CCName, @CCAddress
    End
    If @BCCName <> '' AND @BCCAddress <> ''
    Begin
    EXEC @resultcode = sp_OAMethod @oMail, 'AddBCC',NULL, @BCCName, @BCCAddress
    End
    EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject

    If @Attachment <> ''
    Begin
    Create Table #MailBody(BODY VarChar(8000))
    INSERT INTO #MailBody(BODY) VALUES(@Body)

    UPDATE #MailBody Set BODY = Replace(BODY, '<br>',CHAR(10))
    CREATE TABLE #html ( tag varchar(30) )
    INSERT #html VALUES ( '<html>' )
    INSERT #html VALUES ( '<head%>' )
    INSERT #html VALUES ( '<title%>' )
    INSERT #html VALUES ( '<link%>' )
    INSERT #html VALUES ( '</title>' )
    INSERT #html VALUES ( '</head>' )
    INSERT #html VALUES ( '<body%>' )
    INSERT #html VALUES ( '<td%>' )
    INSERT #html VALUES ( '</td>' )
    INSERT #html VALUES ( '<tr%>' )
    INSERT #html VALUES ( '</tr>' )
    INSERT #html VALUES ( '<table%>' )
    INSERT #html VALUES ( '</table>' )
    --INSERT #html VALUES ( '<style%</style>' )
    INSERT #html VALUES ( '<link%>' )
    INSERT #html VALUES ( '<style%>' )
    INSERT #html VALUES ( '</style>' )
    INSERT #html VALUES ( '</body>' )
    INSERT #html VALUES ( '</html>' )

    WHILE exists(select * FROM #MailBody JOIN #html on patindex('%' + tag + '%' , BODY ) > 0 )
    UPDATE #MailBody
    SET BODY = stuff(BODY, patindex('%'+tag+'%', BODY),charindex('>', BODY, patindex('%'+tag+'%',BODY )) - patindex('%'+tag+'%', BODY) + 1, '' )
    FROM #MailBody JOIN #html
    ON patindex('%'+tag+'%', BODY) > 0


    Set @Body = (SELECT BODY FROM #MailBody)
    DROP TABLE #MailBody
    DROP TABLE #html
    EXEC @resultcode = sp_OASetProperty @oMail,'BodyText', @Body
    Exec @resultcode = sp_OAMethod @oMail,'AddAttachment', NULL, @Attachment
    End
    Else
    Begin
    EXEC @resultcode = sp_OASetProperty @oMail,'BodyText', @Body
    EXEC @resultcode = sp_OASetProperty @oMail,'ContentType', 'text/html'
    End

    EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

    EXEC sp_OADestroy @oMail

    END


    SET nocount off

    GO
  • I like the suggestions, and the ability to attach a SQL query output to an email.

     

    I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @sqlquery + '" /E''')

     

    But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

     

    I DO NOT want to use XP_SENDMAIL as it uses MAPI . Is there any other way to attach SQL queries.

  • How do I send this attachment as a parameter?

     

     

    I would like to pass the parameter in the job, like

     

    EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT

     

     

    and then in the job have @attachement = 'c:\test.txt'

  • The following is the cdosys mailer .I can send mails but attachment is not working.

    I dont have any clue why the attachement dont works.

    Also if i write

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, 'E:/test/test.txt'

    i.e by passing values directly it doesnt send the attachment.

    actually when i use

    IF @hr 0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Attachment')

    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

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Attachment')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    END

    That is to trap the value.@hr returns -2.17 so the particular syntax

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, 'E:/test/test.txt'

    yields error @hr returns -ve value.

    Could u trace this problem.

    Alter PROCEDURE sp_my_mailobject

    @From varchar(50) ,

    @To varchar(50) ,

    @Subject varchar(50),

    @Body text,

    @Smtp nvarchar(50),

    @User varchar(50),

    @pass varchar(50),

    @BodyType int,

    @file NVARCHAR(200)

    As

    Declare @iMsg int

    Declare @hr int

    Declare @source varchar(255)

    Declare @description varchar(500)

    Declare @output varchar(1000)

    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', @Smtp

    exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', '1'

    exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', @User

    /*-- This is to configure the Server Name or IP address.

    -- Replace password of your SMTP Server.*/

    exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', @pass

    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

    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

    IF @file IS NOT NULL

    BEGIN

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @file

    IF @hr 0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Attachment')

    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

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Attachment')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    END

    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

    IF @hr 0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')

    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

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    /*-- Do some error handling after each step if you have to.

    -- Clean up the objects created.*/

    send_cdosysmail_cleanup:

    If (@iMsg IS NOT NULL) /* -- if @iMsg is NOT NULL then destroy it*/

    BEGIN

    EXEC @hr=sp_OADestroy @iMsg

    /*-- handle the failure of the destroy if needed*/

    IF @hr 0

    BEGIN

    select @hr

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')

    EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

    /*-- if sp_OAGetErrorInfo was successful, print errors*/

    IF @hr = 0

    BEGIN

    SELECT @output = ' Source: ' + @source

    PRINT @output

    SELECT @output = ' Description: ' + @description

    PRINT @output

    INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')

    END

    /*-- else sp_OAGetErrorInfo failed*/

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

    END

    END

    END

    GO

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply