Automated email

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.Use the following syntax to send email
SendEMail 'softdev@.knoahindia.com','prasad@.knoahindia.com', '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_OAGetErrorInfo @.Object, @.Src OUT, @.Desc OUT
SELECT hr=convert(varbinary(4),@.Hr), Source=@.Src, Description=@.Desc
Return
End
--set sEndusing property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sEndusing")', '2'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set port property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/SEndUsingMethod")', '25'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set smtp server property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @.SMTPServer
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--Update the configuration
Exec @.Hr = sp_OAMethod @.Object, 'Configuration.Fields.Update', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set TO property
Exec @.Hr = sp_OASetProperty @.Object, 'To', @.To
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set FROM property
Exec @.Hr = sp_OASetProperty @.Object, 'From', @.From
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set Subject property
Exec @.Hr = sp_OASetProperty @.Object, 'Subject', @.Subject
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set HTMLBody property
Exec @.Hr = sp_OASetProperty @.Object, 'HTMLBody', @.Body
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--SEnd the mail
Exec @.Hr = sp_OAMethod @.Object, 'SEnd', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--No memory leaks!
Exec @.Hr = sp_OADestroy @.Object
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End|||

Quote:

Originally Posted by prasadgelli

Use the following syntax to send email
SendEMail 'softdev@.knoahindia.com','prasad@.knoahindia.com', '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_OAGetErrorInfo @.Object, @.Src OUT, @.Desc OUT
SELECT hr=convert(varbinary(4),@.Hr), Source=@.Src, Description=@.Desc
Return
End
--set sEndusing property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sEndusing")', '2'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set port property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/SEndUsingMethod")', '25'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set smtp server property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @.SMTPServer
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--Update the configuration
Exec @.Hr = sp_OAMethod @.Object, 'Configuration.Fields.Update', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set TO property
Exec @.Hr = sp_OASetProperty @.Object, 'To', @.To
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set FROM property
Exec @.Hr = sp_OASetProperty @.Object, 'From', @.From
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set Subject property
Exec @.Hr = sp_OASetProperty @.Object, 'Subject', @.Subject
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set HTMLBody property
Exec @.Hr = sp_OASetProperty @.Object, 'HTMLBody', @.Body
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--SEnd the mail
Exec @.Hr = sp_OAMethod @.Object, 'SEnd', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--No memory leaks!
Exec @.Hr = sp_OADestroy @.Object
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.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 .