Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 7, 2005

Formatted emails from SQL Server

By Muthusamy Anantha Kumar aka The MAK

Collaboration Data Objects, also called CDO, is designed to simplify writing programs that create or manipulate Internet messages. CDO for Windows 2000 is an integral part of the Windows 2000 and higher series of operating systems. It is easy to send SMTP email from SQL Server using CDOsys. It is an alternate method to SQLMail. This article illustrates how to use CDOSys to send formatted emails from Query Analyzer and SQL Server Alerts.


a.  Your email client should be capable of handling and showing HTML messages

b.  Operating System of the SQL Server box should be windows 2000 or later.

c.  SQL Server box can connect to the internet.

d.  Usp_Send_cdosysmail (Refer below)


a.  Please change the email Id used in this article to your email ID.

b.  Please change the SMTP server used to your SMTP server.

Create the stored procedure "usp_send_cdosysmail" as shown below.

use master
if exists (select * from dbo.sysobjects 
  where id = object_id(n'[dbo].[usp_send_cdosysmail]') and 
  objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[usp_send_cdosysmail]
create procedure usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10) 
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,

exec @hr = sp_oasetproperty @imsg, 

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
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
print ' sp_oageterrorinfo failed.'
exec @hr = sp_oadestroy @imsg
set quoted_identifier off 
set ansi_nulls on 

Download the stored procedure from here.

Send simple formatted emails from Query Analyzer

Execute the code below, as shown in Fig 1.0, in order get email as shown in Fig 1.1

exec master..usp_send_cdosysmail
@to ='mak_999@yahoo.com',
@subject ='Formatted email from MAK',
@body ='<B>Test Email From MAK</B><BR> Please Ignore this <Font Color=red>email.',
@smtpserver ='optonline.net',
@bodytype ='HTMLBody'

Parameters Explained

@From = 'Sender of the Email'

@To= 'Receiver of the Email'

@Subject = 'Subject of this Email'

@Body = 'Body of this Email. The body of the message accepts HTML tags'

@SMTPServer = 'SMTP server'

@BodyType = 'Type of body: It can be HTMLBody or TEXTBody'

[Fig 1.0]

[Fig 1.1]

Send query results as formatted emails from Query Analyzer

Create and execute the code below, as shown in Fig 1.2, in order to receive email as shown in Fig 1.3

declare @body1 varchar(1000)
set @body1='<table border="2" cellspacing="2" cellpadding="2"> 
  <TR><td colspan=2 
  style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left>'
set @body1=@body1+'Tables </TD></TR>'
select @body1=@body1 +'<TR><TD>'+ name +'</TD></TR>' from sysobjects
print @body1

exec master..usp_send_cdosysmail
@to ='mak_999@yahoo.com',
@subject ='Formatted email from MAK',
@body =@body1,
@smtpserver ='optonline.net',
@bodytype ='HTMLBody'

Download Query.txt

[Fig 1.2]

[Fig 1.3]

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM