Reading a text file for an email body (CDOSYS)
-
Hi there, I have a script that writes (and formats) output from a query to a text file. What I want to do is use that text file as the body of an email that will be sent out. I have written the following script (bits taken from around the internet) but the problem I have is that is sends out seperate emails per line of the text file.
So far
declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(4000)
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\textfile.txt', 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
exec @blnEndOfFile = sp_OAMethod @objFile, 'ReadLine', @strLine out
print @strLine
declare @out varchar(8000)
declare @From varchar(200)
declare @To varchar(200)
declare @subject varchar(300)
declare @SMTPServer varchar(100)
declare @minimumspace int
declare @body Varchar(8000)
set @minimumspace = 15
set @SMTPServer ='My SMTP server'
set @From ='my email address'
set @To ='my email address'
set @subject = 'my subject'
set @body = @strLine
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec usp_send_cdosysmail @From ,@To ,@Subject,@Body,@SMTPServer
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys
-
I don't know much about OA but there is a script about SMTP mail here: http://www.sqlservercentral.com/scripts/contributions/510.asp
Maybe there is something in it that will help.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Another thought - why not set the variable @body = <query>?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
The problem is that I use cursors to print out the contents of a table into a text file.
The table is set up like this
Col1 Col2 Col3
---- ---- ----
ABC 123 456
ABC 476 987
DEF 456 987
So the text file comes out like this:
ABC
---
123 456
476 987
DEF
---
456 987
That is how I want the body of the email to be like.
-
xp_smtp_sendmail from http://www.sqldev.net has a parameter that allows you to use a text file for the body.
--------------------
Colt 45 - the original point and click interface
-
Cheers Phil, I did see that and it is spot on. However trying to get clients to be happy with a 3rd party component installed on their servers is a different matter altogether.
Even if it is generally regarded in our community as an excellent piece of work.
-
i cannot find the parameter for using a textfile in a body?
i want to shown the line of a textfile in the body of the e-mail.
🙁
-
The parameter is called @messagefile
Check http://www.sqldev.net/xp/xpsmtp.htm#Parameters
--------------------
Colt 45 - the original point and click interface -
thanks for the info, but alway get following error message:
Error: connecting to server smarthost
smtp server is correct & working.
regard, mario
-
Error: connecting to server smarthost
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 134, Level 15, State 1, Line 20
The variable name '@rc' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 34
The variable name '@rc' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 49
The variable name '@rc' has already been declared. Variable names must be unique within a query batch or stored procedure.
-
i have found the error. it works.
thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply