Send all files in folder - VB ActiveX script

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

Development Tools

Other Development Tools

Send all files in folder - VB ActiveX script

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-05-26 : 15:57:21

Guys -
I have this script below, that will send an email and attach 1 file. Can someone tell me how to modify this code to attach all files within the folder

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

Function Main()

theday = Day(dtsglobalvariables("TodaysDate"))
theprevday = Day(dtsglobalvariables("YesterdaysDate"))
themonth = Month(dtsglobalvariables("TodaysDate"))
theyear = Year(dtsglobalvariables("TodaysDate"))
If Len(theday) = 1 Then theday = "0" & theday
If Len(theprevday) = 1 Then theprevday = "0" & theprevday
If Len(themonth) = 1 Then themonth = "0" & themonth
filetoget1 = "Report.xls"
filetoget2 = "Report_"& theyear & themonth & theday &".xls"

HTML = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">"
HTML = HTML & "<html>"
HTML = HTML & "<body>"

HTML = HTML & ""
HTML = HTML & " If any of the data appears invalid please contact My Company @ email@mycompany.com."
HTML = HTML & " Sig Line 1 "
HTML = HTML & "Sig Line 2"
HTML = HTML & " "
HTML = HTML & " "
HTML = HTML & "</body>"
HTML = HTML & "</html>"

strHost = "192.168.1.1"
'strHost = "192.168.1.2"
strFromName = "Igor"
strFromAddress = "igor@mycompany.com"
strTo = "users@mycompany.com"
strCC = ""
strSubject = "Report 1 (Generated on - " & dtsglobalvariables("TodaysDate") & ")"
strMessage = HTML
strFile = "C:\Folder1\Report1\" & filetoget2

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

---

Thanks!
Igor.

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-05-26 : 18:55:25

Look into the FileSystemObject (fso). It has some classes that will allow you to do a "For each File in Directory"

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources