send email

I have a table with email addresses of the customers. How could I send them emails from my tsql script.

TIA..

Answer this question

send email

Kaiser07

> Yes, I know.
> But CDONTS is a lot simpler if you can lay your hands on the library.

Sounds fair. Keep a Windows 2000 install handy, for eternity, to avoid 
three extra lines of code  You don't think it might have been deprecated 
for any good reasons, either, do you

mohamed505080
If you are going to the trouble of periodically executing a VB script using SQLagent you can also use CDONTS. It is sometimes easier than dealing with permission issues, especially in a restrictive network (anal retentive network admin).

It's real easy in VBScript, and you can also specify the return address. That's not so easy to do using xp_sendmail.

These two examples work well in an ActiveX script:

Dim ToAddress
Dim FromAddress
Dim MailSubject
Dim MailText


Set objMail = CreateObject("CDONTS.NewMail")
objMail.BodyFormat = 0
objMail.MailFormat = 0
objMail.Send FromAddress, ToAddress, MailSubject, MailText

Set objMail = nothing

'**********************************************

Alternate Method:

'**********************************************

Dim NewMail, Body
Dim ToAddress
Dim FromAddress
Dim MailSubject
Dim MailText

'create an instance of the NewMail Object

Set NewMail = Server.CreateObject("CDONTS.NewMail")

NewMail.To = ToAddress
NewMail.From = FromAddress
NewMail.Subject = MailSubject
NewMail.Body = MailText
NewMail.Bodyformat = 0
NewMail.Mailformat = 0
NewMail.Send

Set NewMail = Nothing

*********************************************

You may have to find a copy of CDONTS.DLL and register it on your SQL server. But that's generally not too difficult.

Caveat: CDONTS may not be around much longer.
John Aschenbrenner
http://www.aspfaq.com/2403 The article has not been updated for SQL Server 2005's Database Mail, a lighter weight replacement for SQL Mail. You forgot to tell us which version you're using, so I'm not sure if that content would be relevant. wrote in message news:b71ae302-a6c4-4a74-bdb7-5365eacc4002@discussions.microsoft.com... >I have a table with email addresses of the customers. How could I send > them emails from my tsql script. > > TIA.. > >
pheonix2k
Yes, I know.
But CDONTS is a lot simpler if you can lay your hands on the library.

Torunns

> Set NewMail = Server.CreateObject("CDONTS.NewMail")

FWIW, CDO.Message is preferred over CDONTS.NewMail. The latter is 
deprecated and does not ship with Windows XP or Windows Server 2003.

SergeyPV
In SQL Server 2000, you can use xp_sendmail (Uses MAPI). But that may not be scaleable so it will easier to write a VB script for example and run it periodically using SQLAgent. In SQL Server 2005, you can use DBMail which uses SMTP. There are also 3rd party extended stored procedures available that uses SMTP to send email.
AnonymousMe83
Please try the below stored procedure. This sends mail using CDONTS. Hope it helps.

Regards
Sachin

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

exec sp_send_cdontsmail 'someone@example.com','someone2@example.com','Test of CDONTS','It works'
lee d
Hi

If i want to do a HTML email like below what should i do

exec sp_send_cdontsmail 'someone@example.com','someone2@example.com','Test of CDONTS','It <b>works</b>'
send email

Answer this question