Formatting the body of Email using CDOSYS


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: Formatting the body of Email using CDOSYS

Hybrid View

  1. #1
    Edwin Guest

    Formatting the body of Email using CDOSYS


    Hi,

    I wonder if you can format the body of an email sent with CDOSYS. There is
    a really good example of an SP that can let you sent attachements (and @from
    which is really why I can't use xp_sendmail since I need to specify who is
    the sender according to some business logic). Anyway, is there a way I can
    format the body of that email so the font is Arial Narrow?

    Here's that neat SP:

    CREATE PROCEDURE [dbo].usp_send_cdosysmail]




    @From varchar(100) ,
    @To varchar(100) ,
    @Subject varchar(100)=" ",
    @Body varchar(4000) =" ",
    @attachment varchar(400) = 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

    Added attachment logic (pwf)

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

    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/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',
    'DELTA'

    -- 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

    --adding an attachment: pwf
    IF @attachment IS NOT NULL
    EXEC @hr = sp_OAMethod @iMsg,'AddAttachment', NULL, @attachment

    -- 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
    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 need to.
    -- Clean up the objects created.
    EXEC @hr = sp_OADestroy @iMsg

    GO

  2. #2
    Michael Guest

    Re: Formatting the body of Email using CDOSYS

    Edwin,

    Look up CDONTS on MSDN. You can set the mailFormat and bodyFormat properties
    to HTML and then use HTML tags in your body content. Then you can use
    whatever biz logic you need to set the FROM, TO, Cc, Bcc, SUBJECT and body
    properties. Including attachments if needed. Very easy to implement CDONTS.

    If you have any delivery issues when testing you just need to look at your
    mailRoot directory in the Intepub directory on your server.

    --

    Michael A Sanchez
    Runtime Web Development
    Dynamic Websites for Demanding Business
    Web: www.RuntimeWebDev.com
    eMail: MSanchez@RuntimeWebDev.com


    "Edwin" <vbedluciano@hotmail.com> wrote in message
    news:3ec910bc$1@tnews.web.devx.com...
    >
    > Hi,
    >
    > I wonder if you can format the body of an email sent with CDOSYS. There is
    > a really good example of an SP that can let you sent attachements (and

    @from
    > which is really why I can't use xp_sendmail since I need to specify who is
    > the sender according to some business logic). Anyway, is there a way I can
    > format the body of that email so the font is Arial Narrow?
    >
    > Here's that neat SP:
    >
    > CREATE PROCEDURE [dbo].usp_send_cdosysmail]
    >
    >
    >
    >
    > @From varchar(100) ,
    > @To varchar(100) ,
    > @Subject varchar(100)=" ",
    > @Body varchar(4000) =" ",
    > @attachment varchar(400) = 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
    >
    > Added attachment logic (pwf)
    >
    > ***********************************************************************/
    >
    > 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/de..._sendusing.asp
    > EXEC @hr = sp_OASetProperty @iMsg,

    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
    ing").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/smtpse
    rver").Value',
    > 'DELTA'
    >
    > -- 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
    >
    > --adding an attachment: pwf
    > IF @attachment IS NOT NULL
    > EXEC @hr = sp_OAMethod @iMsg,'AddAttachment', NULL, @attachment
    >
    > -- 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
    > 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 need to.
    > -- Clean up the objects created.
    > EXEC @hr = sp_OADestroy @iMsg
    >
    > GO




  3. #3
    Join Date
    Oct 2006
    Posts
    7
    The following stored procedure is being used now. It is for use on SQL2000 and later and on Windows 2000 and later.

    CREATE PROCEDURE usr_sp_send_cdosysmail
    @From_Addr VARCHAR(500) ,
    @To_Addr VARCHAR(500) ,
    @Subject VARCHAR(500) ,
    @Body VARCHAR(4000) ,
    @SMTPserver VARCHAR(25) = 'localhost',
    @BodyType VARCHAR(10) = 'textbody'
    AS

    DECLARE @imsg INT
    DECLARE @hr INT
    DECLARE @source VARCHAR(255)
    DECLARE @description VARCHAR(500)

    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', @SMTPserver
    EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL

    EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr
    EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr
    EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject

    -- If you are using html e-mail, use 'htmlbody' instead of 'textbody'.
    EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body
    EXEC @hr = sp_oamethod @imsg, 'send', NULL

    EXEC @hr = sp_oadestroy @imsg

    The script to execute this procedure is as follows:
    EXEC usr_sp_send_cdosysmail
    @from_Addr='me@mycomm.com',
    @to_Addr ='you@youaddr.com',
    @subject ='Formatted email from SQL',
    @body ='<B>Test Email From SQL</B><BR> Please Ignore this <Font Color=red>email.',
    @smtpserver ='yourserver'
    @bodytype ='HTMLBody'
    -- @bodytype ='textbody'

    The 'HTMLBody' option will allow you to format any message as you please within the restrictions of HTML.

  4. #4
    Join Date
    Apr 2007
    Posts
    6

    Question sending attachment using CDOSYS mail

    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 sends mail without any attachment though the file exists.

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

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

    Please help me out.
    Last edited by nikie; 04-02-2007 at 03:34 AM.

  5. #5
    Join Date
    Oct 2006
    Posts
    7
    The following is the procedure that we use. Note The data type of the attachment. It is very particular.

    ALTER PROCEDURE usp_send_cdosysmail
    @From_Addr VARCHAR(500) ,
    @To_Addr VARCHAR(500) ,
    @Subject VARCHAR(500),
    @Body VARCHAR(4000) ,
    @SMTPserver VARCHAR(25) = 'localhost',
    @BodyType VARCHAR(10) = 'textbody',
    @Attachment NVARCHAR(200) = NULL
    AS

    DECLARE @imsg INT
    DECLARE @hr INT
    DECLARE @source VARCHAR(255)
    DECLARE @description VARCHAR(500)
    DECLARE @output VARCHAR(1000)
    DECLARE @errmsg nvarchar(500)

    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', @SMTPserver
    EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL

    EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr
    EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr
    EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject

    IF @BodyType <> 'URL'
    BEGIN
    -- If you are using html e-mail, use 'htmlbody' instead of 'textbody'.
    EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body
    END
    ELSE
    BEGIN
    -- Generate The Body From The Input URL
    EXEC @hr = sp_OAMethod @imsg, 'CreateMHTMLBody', NULL, @body, 0, '',''
    END
    -- Add An Attachment?
    IF @attachment IS NOT NULL
    BEGIN
    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment
    END

    EXEC @hr = sp_oamethod @imsg, 'send', NULL
    EXEC @hr = sp_oadestroy @imsg

  6. #6
    Join Date
    Apr 2007
    Posts
    6

    CdoSys mail attachment

    Dear dvroman

    Thankyou for ur response. But still im facing problem.

    The following is the cdosys mailer .I can send mails but attachment is not working. 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

  7. #7
    Join Date
    Oct 2006
    Posts
    7

    RE: Attachments

    By some chance, is this being run from a DTS package?
    If so the attachment would need to be available to the SQL Server machine. I am having that problem when I forget about where the DTS is running.
    My server is on a different network than the SQL server and although I can see the SQL server, it can't see me.

  8. #8
    Join Date
    Apr 2007
    Posts
    6
    I have checked most of the forums and they have provided the same code.Could u tell me wat is to be done for attachments to work. You had talked abt DTS.Could u tell me wat it is and how it has to be used to make this run.

  9. #9
    Join Date
    Oct 2006
    Posts
    7

    RE: Attachments

    I tested the code. It is correct and works. There are two things that I know can go wrong with this statement.
    1) The SQL Server machine can't see the attachment file.
    2) The SQL Server machine doesn't have permission to read the attachment file.
    After The "EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment" statement Add:
    IF @hr <> 0
    EXEC sp_OAGetErrorInfo @imsg
    END
    to find out what the problem is.

  10. #10
    Join Date
    Apr 2007
    Posts
    6
    When i tried to trap the error by using

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment
    IF @hr <> 0
    @hr= EXEC sp_OAGetErrorInfo @imsg
    END
    In @hr= EXEC sp_OAGetErrorInfo @imsg i am getting the @hr= -2.17 so here it fails.

    If the Sql server is not recognizing the attachment.Have u any idea or suggestions as to how to resolve it. Actually, if this attachment too works well it would be helpful to us.So if u have any suggestions please let me know.

  11. #11
    Join Date
    Apr 2007
    Posts
    6
    When i tried to trap the error by using

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment
    IF @hr <> 0
    @hr= EXEC sp_OAGetErrorInfo @imsg
    END
    In @hr= EXEC sp_OAGetErrorInfo @imsg i am getting the @hr= -2.17 so here it fails.

    If the Sql server is not recognizing the attachment.Have u any idea or suggestions as to how to resolve it. Actually, if this attachment too works well it would be helpful to us.So if u have any suggestions please let me know.

  12. #12
    Join Date
    Oct 2006
    Posts
    7
    When I debugged It I found two errors.
    1) The SQL Server Couldn't Find The File (Try \\server\sharefolder\file) to eliminate this problem.
    2) The SQL Server Doen't have permission to read the file.
    That's Why I put it in a "share" folder. (World read permission)

  13. #13
    Join Date
    Apr 2007
    Posts
    6
    Phew!!! Thanks friend!
    You were rite actually the server was not locating the file.So i created a folder (files) on the server and put my files to it and mapped it to a drive.

    So when in my dotnet application i passed to Cmd.Parameters("@file") = "http://test.company.local/files/test1.doc"

    where test.company.local is the subdomain name in the server and files is the folder containing the attachment file named test1.doc.This worked.

    Thanks a lot once again!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center