emailing and moving files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

emailing and moving files

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-17 : 10:16:50

anyone have some code or can get me started on this... i need to browse a directory for all .txt files, email them out, then move them to a different directory.....
thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-17 : 10:19:41

and all this in sql???
why????
do it with .net

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-17 : 10:23:31

See
http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
http://www.nigelrivett.net/ImportTextFiles.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-17 : 10:25:04

don't want to use .net because it has to run on a schedule and all other processes related to this are in sql

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-17 : 10:27:22

how would i loop through the files in a directory and email them using the master..xp_smtp_sendmail

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2004-12-17 : 10:29:47

This one, sends a file, I always put this as the last step on my packages (I work on MIS/Reporting) so it sends the file automatically.
I hope it works.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
HTML = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">"
HTML = HTML & "<html>"
HTML = HTML & "<body>"
HTML = HTML & "<table width=""100%"" border=""0"">"
HTML = HTML & " <tr>"
HTML = HTML & " <td bgcolor=""#00FA9A"">EMAIL HEADER"
HTML = HTML & " </td>"
HTML = HTML & " </tr>"
HTML = HTML & "</table> "
HTML = HTML & ""
HTML = HTML & "COMMENTS "
HTML = HTML & ""
HTML = HTML & "FOOTER"
HTML = HTML & "</body>"
HTML = HTML & "</html>"

strHost = "SMTP SERVER"
strFromName = ""
strFromAddress = ""
strTo = ""
strCC = ""
strSubject = ""
strMessage = HTML
strFile = "FILE PATH"

Dim msMail
strMessage = Replace(strMessage, vbCrLf, " ") 'Converts line breaks to tags.

Dim conf
Set conf = CreateObject("CDO.Configuration")
With conf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strHost
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Update
End With
Set msMail =CreateObject("CDO.Message")
With msMail
Set .Configuration = conf
.From = strFromName & " <" & strFromAddress & ">"
.To = strTo
.CC = strCC
.Subject = strSubject
.AddAttachment(strfile)
.HTMLBody = strMessage
.Send
End With

Set msMail = Nothing

Main = DTSTaskExecResult_Success
End Function

I Love Challenges! That's why I love coding!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-17 : 10:32:18

I think this one will do it. Just put the sendmail in the called sp
http://www.nigelrivett.net/s_ProcessAllFilesInDir.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources