Here I create a procedure for send automatic mail to somebody using CDOsys with attachment.
I used BCP command for export data to file and i save it some location and I use in that procedure as attachment mail.We can create a job for this email then it will automatically send without our help.
Here the statement:
create proc [procname](@FromDate varchar(20),@ToDate varchar(20),@val varchar(20))
as
Begin
Begin
Try
declare @sql_waster varchar(8000),@sql_receiver varchar(8000),@FilenameWaster varchar(200),@FilenameReceiver varchar(200)
set
@FilenameWaster='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112)+'.xls'
set
@FilenameReceiver='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112) +'.xls'
set
@sql_receiver = 'bcp "Select query ('''+ @FromDate+''','''+
@ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec
master..xp_cmdshell @sql_receiver
set @sql_waster = 'bcp "Select query ('''+ @FromDate+''','''+ @ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec master..xp_cmdshell @sql_waster
Declare @iMsg int , @hr int, @source varchar(255),@description varchar(500),@output varchar(1000),@To varchar(100),@CC varchar(100),@BC varchar(100), @Subject varchar(100), @Body varchar(4000)
SET @Subject = 'put ur sub' + ' - '+ CONVERT(VARCHAR(11),cast(@FromDate as datetime),103) +' '+ 'to' + ' '+ CONVERT(VARCHAR(11),cast(@ToDate as datetime),103)
SET @Body = 'Sir ,'+ 'Please find the attached files '+''+
'Thanks & Regards, This Email is computer generated. Please dont reply.'
set @To='
' );
//-->\n
xxx@xyz.com
' );
//-->
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->
'
set
@CC='
' );
//-->\n
yyy@xyz.com
' );
//-->
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->
'
set
@BC ='
' );
//-->\n
zzz@xyz.com
' );
//-->
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->
'
--***** Create the CDO.Message Object *****
EXEC
@hr = sp_OACreate 'CDO.Message', @iMsg OUT
if
@hr 0
print
'message object creation failed'
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
EXEC
@hr = sp_OASetProperty @iMsg, 'Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
if
@hr 0
print
'configuration failed'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC
@hr = sp_OASetProperty @iMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpservername'----your smtp servername like bizmail.yahoo.com
if
@hr 0
print
'IP setting failed'
EXEC
@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', ' mailid'--your smtp loginid
EXEC
@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'pwd' --your smtp password
EXEC
@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
-- Save the configurations to the message object.
EXEC
@hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC
@hr = sp_OASetProperty @iMsg, 'To', @To
if
@hr 0
print
'to failed'
EXEC
@hr = sp_OASetProperty @iMsg, 'CC', @CC
EXEC
@hr = sp_OASetProperty @iMsg, 'BCC', @BC
EXEC
@hr = sp_OASetProperty @iMsg, 'From', '
' );
//-->\n
some@company.com
' );
//-->
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->
'
if
@hr 0
print
'from failed'
EXEC
@hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC
@hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC
@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameReceiver --this is attachment
EXEC
@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameWaster
EXEC
@hr = sp_OAMethod @iMsg, 'Send', NULL
if
@hr 0
print
'Q Send failed'
IF
@hr 0
BEGIN
EXEC
@hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF
@hr = 0
BEGIN
SELECT
@output = ' Source: ' + @source
PRINT
@output
SELECT
@output = ' Description: ' + @description
PRINT
@output
END
ELSE
BEGIN
PRINT
' sp_OAGetErrorInfo failed.'
RETURN
END
END
EXEC
@hr = sp_OADestroy @iMsg
End
Try
Begin
catch
select Error_Message()
END CATCH
End
Monday, August 24, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment