-
xp_sendmail and HTML
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
-
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
-
--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
-
--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'
-
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
-
'MS Exchange Settings' should be replaced by your SMTP server name. Ask your system admin or network admin for the SMTP server name.
-
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
-
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
-
-
thanks it does the job, I'm happy
-
xp_smtp_sendmail with queries
do xp_smtp_sendmail sends the result of a query the way xp_sendmail do?
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|