xp_sendmail and HTML

[Shaunt]

hi,

is there any option for xp_sendmail to generate html email.


exec master..xp_sendmail @recipients='info@ipbx.com' ,@message='line1 <br> line2' ,@subject='New Matter Notification >',@attach_results='false',@width =140
html format S'line1 <br> line2'


many thanks


S

[Rawhide]

No it doesn't, but xp_smtp_sendmail seems to be a popular option for sending HTML mail through SQL Server.

It's worth checking out: http://sqldev.net/xp/xpsmtp.htm

[MAK]

--Usage
exec dbo.sp_send_cdosysmail
@From = 'MAK',
@To ='mak_999@yahoo.com',
@Subject ='Test HTML',
@Body ='This is a test message',
@BodyType ='TEXTBODY',
@MailServerName = 'SMTPMailserver'



--Procedure

Create PROCEDURE dbo.sp_send_cdosysmail
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@BodyType varchar(100) =" ", --HTMLBODY or TEXTBODY
@MailServerName varchar(100)

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'

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

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

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

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

[MAK]

--Usage - for HTML
exec dbo.sp_send_cdosysmail
@From = 'MAK',
@To ='mak_999@yahoo.com',
@Subject ='Test HTML',
@Body ='This is a test message',
@BodyType ='HTMLBODY',
@MailServerName = 'SMTPMailserver'

[Shaunt]

Thanks MAK

I applied the SP and ran

exec dbo.sp_send_cdosysmail
@From = 'sqlcsfirm',
@To ='khaldtiance, shaunt',
@Subject ='Test HTML',
@Body ='This is a test message',
@BodyType ='HTMLBODY',
@MailServerName = 'MS Exchange Settings'


and I'm getting

Source: CDO.Message.1
Description: The transport failed to connect to the server.

as a message and it return a value of -2147220973 but it doesn't send any email out.

ber in mind the to and from addresses are local within the exchange server.

Thanks

[MAK]

'MS Exchange Settings' should be replaced by your SMTP server name. Ask your system admin or network admin for the SMTP server name.

[Shaunt]

I have tried

exec dbo.sp_send_cdosysmail
@From = 'sqlcsfirm',
@To ='khaldtiance, shaunt',
@Subject ='Test HTML',
@Body ='This is a test message',
@BodyType ='HTMLBODY',
@MailServerName = 'CSEXCL01'


--Usage - for HTML
exec dbo.sp_send_cdosysmail
@From = 'sqlcsfirm',
@To ='khaldtiance, shaunt',
@Subject ='Test HTML',
@Body ='This is a test message',
@BodyType ='testBODY',
@MailServerName = 'CSEXCL01'

but this time I am only getting a message and it does say:




Source:
Description:


only??? and I am not recieveing any emails!


thanks

[Shaunt]

fantastic,
it does work fine but I have to enter the full address like 'xxxx@mmm.nnn' if I just enter the name which is quialified with in the exchange locally it won't work. is there any way arround the like I will specifie 'khaldtiance, shaunt' rather that the full email address

[MAK]

not when you use SMTP.

[Shaunt]

thanks it does the job, I'm happy

[had1980]

do xp_smtp_sendmail sends the result of a query the way xp_sendmail do?

[Rawhide]

No, not in the same manner. But with a couple of extra steps you could do the query separately and either write the results to the email body or to a file that you attach to it.