Formatting the body of Email using CDOSYS

[Edwin]

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

[Michael]

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

[dvroman]

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.

[nikie]

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.

[dvroman]

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

[nikie]

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

[dvroman]

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.

[nikie]

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.

[dvroman]

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.

[nikie]

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.

[nikie]

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.

[dvroman]

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)

[nikie]

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