June 30, 2004 at 6:37 am
I have this code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
On Error Resume Next
Dim objSendMail
Set objSendMail = CreateObject("CDO.Message")
objSendMail.From="me.com"
objSendMail.To = "me.com"
objSendMail.AddAttachment "C:\Temp_error.xls"
objSendMail.Subject = "LD_Anomally_Report"
objSendMail.TextBody = "Excel Spreedsheet"
objSendMail.Send
'Set objSendMail = nothing
if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function
it give an error saying Function Not Found.... any ideas ?? thanks
June 30, 2004 at 9:22 am
There are lots of other ways also to send email...
Since you have tried CDO Messaging.....try this...
try creating this store procedure and see....
CREATE PROCEDURE send_cdomail
@From varchar(100),
@To varchar(100),
@cc varchar(50),
@Subject varchar(100),
@Body varchar(4000),
@Attachment varchar(150)
AS
DECLARE @iMsg int,
@Att_Methode_Object varchar(50)
SET @Att_Methode_Object = 'AddAttachment ' + '("' + @Attachment + '")'
EXEC sp_OACreate 'CDO.Message', @iMsg OUT
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSendUsingMethod)', 2 -- use remote SMTP server
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSMTPServerName)', 'CINMLVEM08' -- name of your SMTP mail server
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSMTPConnectionTimeout)', 10
EXEC sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC sp_OASetProperty @iMsg, 'To', @To
EXEC sp_OASetProperty @iMsg, 'From', @From
EXEC sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC sp_OAMethod @iMsg, @Att_Methode_Object
EXEC sp_OAMethod @iMsg, 'Send', NULL
EXEC sp_OADestroy @iMsg
GO
create a connection object...
set conn = CreateObject("ADODB.Connection")
conn.Open = "Provider='SQLOLEDB';Data Source='ServerName';Initial Catalog='DatabaseName';Integrated Security='SSPI';"
Using that connection execute this store Procedure and send the email
strEmail = "send_cdomail @From = '" & strFROM & "', @To = '" & strTO & "', @cc = '" & strCC & "', @Subject = '" & strSubject & "', @Body = '" & strBody & "' , @Attachment = '" & strAttachment &"'"
connTransData.Execute strEmail
Hope this helps!!!
June 30, 2004 at 10:06 am
Hi I did this way:
CREATE PROCEDURE send_cdomail
AS
declare @From varchar(100)
declare @to varchar(100)
declare @cc varchar(50)
declare @Subject varchar(100)
declare @Body varchar(500)
declare @Attachment varchar(150)
DECLARE @iMsg int,
@Att_Methode_Object varchar(50)
SET @Att_Methode_Object = 'AddAttachment ' + '("' + @Attachment + '")'
select @From = 'sql_agent@mtnsat.com'
select @to = 'Nelson.Viggiani@mtnsat.com'
select @Subject ='LD Anomaly Report LINK'
select @Body ='\\mirfnp01\common\circuits\temp_error.xls'
EXEC sp_OACreate 'CDO.Message', @iMsg OUT
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSendUsingMethod)', 2
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSMTPServerName)', '10.200.82.15'
EXEC sp_OASetProperty @iMsg, 'Configuration(cdoSMTPConnectionTimeout)', 30
EXEC sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC sp_OASetProperty @iMsg, 'To', @To
EXEC sp_OASetProperty @iMsg, 'From', @From
EXEC sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC sp_OAMethod @iMsg, @Att_Methode_Object
EXEC sp_OAMethod @iMsg, 'Send', NULL
EXEC sp_OADestroy @iMsg
GO
execute send_cdomail
The command(s) completed successfully. BUT NO EMAIL
July 2, 2004 at 9:09 am
Check the settings in the email program on your PC to see if the smtp you specified needs 'Authentication' (ie a user ID and password). If so, I think you have to add:
EXEC sp_OASetProperty @iMsg, 'Configuration(cdosmtpauthenticate)', 2
EXEC sp_OASetProperty @iMsg, 'Configuration(cdosendusername)', 'CorrectUserName'
EXEC sp_OASetProperty @iMsg, 'Configuration(cdosendpassword)', 'CorrectPassword'
I am not sure about the correct name of the constants as I use the values directly.
I also have the following variation:
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
Where @attachment is the parameter passed to the proc.
Hope this helps
Peter
July 2, 2004 at 9:36 am
Thanks for the Tip but I add those 3 lines to the code
and it execute sucessfully BUT STILL NO E-MAIL
the e-mail application is outlook
Nelson
July 2, 2004 at 10:39 am
Try executing the stored procedure directly from SQLServer, in Query Analyser. The procedure uses CDO that comes with IIS, which is automatically installed with SQLServer. Check that CDO hasn't been disabled on your server (I am not sure how, but I expect the procedure would raise an error creating the CDO object)
Your SQLServer server needs to have access to the smtp server across the network. There might be a firewall in between. Try pinging the smtp server from a DOS session on the SQLServer server.
Make sure both your 'To' and 'From' address are valid email addresses. This is important if you have an Exchange server.
Check with your Outlook support team to see if there is some email checking software running that Quarantines/deletes suspect emails. If so check that your subject line/ email body would not get blocked.
Hope this helps
Peter
July 2, 2004 at 12:15 pm
Yes I can ping the server form SqlServer
I'm getting this error now:
Active X scripting function not found:
Multiple step OLE DB operation generated errors.Check each OLEDB status value,if available
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply