Monday, August 24, 2009

Send mail Using SQL CDOSYS in 2005

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

No comments:

Post a Comment