Automated email

[priya0123]

Hi,

I want to send automated emails. I want to send emails to some persons in the table at the end of the day like 10.00 pm every day. How do I do that?
I am using sql server 2000 and asp.net.

Thanks in advance for the reply.

[prasadgelli]

Use the following syntax to send email
SendEMail 'softdev@knoahi ndia.com','pras ad@knoahindia.c om', 'Sending email through Stored Procedure', 'Body : Giving TO, FROM, SUBJECT, BODY, SMTP - Address - 192.168.0.5, An Email can be sent through stored procedure... and this is SAMPLE', '192.168.0.5'

Here is the storedprocedure
Create Procedure dbo.SendEmail
@To VarChar(500), @From VarChar(100), @Subject VarChar(200), @Body VarChar(1000), @SMTPServer VarChar(50)
AS
Declare @Object int, @Hr int, @Src VarChar(255), @Desc VarChar(255)
--Create Message object
Exec @Hr = sp_OACreate 'CDO.Message', @Object OUT
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object, @Src OUT, @Desc OUT
SELECT hr=convert(varb inary(4),@Hr), Source=@Src, Description=@De sc
Return
End
--set sEndusing property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/sEndusing")', '2'
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set port property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/SEndUsingMethod ")', '25'
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set smtp server property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/smtpserver")', @SMTPServer
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--Update the configuration
Exec @Hr = sp_OAMethod @Object, 'Configuration. Fields.Update', Null
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set TO property
Exec @Hr = sp_OASetPropert y @Object, 'To', @To
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set FROM property
Exec @Hr = sp_OASetPropert y @Object, 'From', @From
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set Subject property
Exec @Hr = sp_OASetPropert y @Object, 'Subject', @Subject
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set HTMLBody property
Exec @Hr = sp_OASetPropert y @Object, 'HTMLBody', @Body
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--SEnd the mail
Exec @Hr = sp_OAMethod @Object, 'SEnd', Null
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--No memory leaks!
Exec @Hr = sp_OADestroy @Object
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End

[shahnawaz shaikh]

Use the following syntax to send email
SendEMail 'softdev@knoahi ndia.com','pras ad@knoahindia.c om', 'Sending email through Stored Procedure', 'Body : Giving TO, FROM, SUBJECT, BODY, SMTP - Address - 192.168.0.5, An Email can be sent through stored procedure... and this is SAMPLE', '192.168.0.5'

Here is the storedprocedure
Create Procedure dbo.SendEmail
@To VarChar(500), @From VarChar(100), @Subject VarChar(200), @Body VarChar(1000), @SMTPServer VarChar(50)
AS
Declare @Object int, @Hr int, @Src VarChar(255), @Desc VarChar(255)
--Create Message object
Exec @Hr = sp_OACreate 'CDO.Message', @Object OUT
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object, @Src OUT, @Desc OUT
SELECT hr=convert(varb inary(4),@Hr), Source=@Src, Description=@De sc
Return
End
--set sEndusing property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/sEndusing")', '2'
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set port property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/SEndUsingMethod ")', '25'
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set smtp server property
Exec @Hr = sp_OASetPropert y @Object, 'Configuration. Fields.Item("ht tp://schemas.microso ft.com/cdo/configuration/smtpserver")', @SMTPServer
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--Update the configuration
Exec @Hr = sp_OAMethod @Object, 'Configuration. Fields.Update', Null
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set TO property
Exec @Hr = sp_OASetPropert y @Object, 'To', @To
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set FROM property
Exec @Hr = sp_OASetPropert y @Object, 'From', @From
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set Subject property
Exec @Hr = sp_OASetPropert y @Object, 'Subject', @Subject
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--set HTMLBody property
Exec @Hr = sp_OASetPropert y @Object, 'HTMLBody', @Body
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--SEnd the mail
Exec @Hr = sp_OAMethod @Object, 'SEnd', Null
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End
--No memory leaks!
Exec @Hr = sp_OADestroy @Object
If @Hr <> 0
Begin
Exec sp_OAGetErrorIn fo @Object
Return
End

hi,
i am shahnawaz
i use this stored procedure but it give error
"The "SendUsing" configuration value is invalid. "
how can i resolve this problem .

[ehotbid]

i want to create a stored procedure to get automated email from my ms sql 2000 database

when a new user register I would like to get a email automatically,
normally all the registered user information is store in user table

please let me know how to do this..

from address shold be the registered member email address


please let me know how do this

Looking to hear from you thanks!

[ehotbid]

iam willing to pay even a small fee if some can get me a correct solution,

All I want is when a new cutomer register I want to get a email send,

from the database,

Iam using MS sql 2000 data is stores in user table

Thanks
Rafi

[Susain]

HI Friends,

I have used the above procedure and working fine.

How can I use that procedure to My reqiurement i.e., Members need to get membership expiry emails for 3 times (30 days before, on the expiry date and at 60 days) and need to move the member to inactive list after 90 days.

Please Help !

Thanks