Sending Variable Via E-Mail

[Chuckles123]

My code is below. It checks each file in a specified folder for the
'last saved date'; if there are any files that do not have the current
date, it sends an e-mail; if all of the files are updated, it sends an
e-mail to that effect. I have a 'SendEmail' subroutine (not shown, but
referred to in my code) that uses 'Microsoft CDO for Windows 2000
Library' as a "Reference"; it sends e-mail from an SMTP Server on our
network.

My question: is there any way to send the value of n (or the
string value of sn) via the e-mail sent? I was thinking about sending
the name(s) of the file(s) that was/were not updated, but that seems
more difficult, and probably not necessary.

Thanks for your assistance.
Chuckles123


Dim Dte As Date, DtePlusTime As Date
Dim sFilename As String, sNotUpdatedFilename As String
Dim sPath As String
Dim n As Integer, sn As String

n = 0
sPath = "\\DATA\BATCH FILE REPORTS\"
sFilename = Dir(sPath)

Do While sFilename <> ""
DtePlusTime = FileDateTime(sPath & sFilename)
Dte = DateValue(DtePlusTime)

If Dte <> Date Then
'THIS FILE WAS NOT UPDATED TODAY
'CURRENTLY, DOING NOTHING WITH VALUE OF sNotUpdatedFilename
sNotUpdatedFilename = sFilename
n = n + 1
'CURRENTLY, DOING NOTHING WITH VALUE OF sn OR n
sn = WorksheetFunction.Text(n, 0)

Else
'DO NOTHING
End If

'SELECT NEXT FILE IN 'sPath'
sFilename = Dir()

Loop

If n > 0 Then

'PARAMETERS: strTo, strCopy, strSub, strText
SendEmail "(e-mail address removed)", "", _
"ONE OR MORE FILES HAVE NOT BEEN UPDATED: ", ""

Else
SendEmail "(e-mail address removed)", "", _
"ALL FILES HAVE TODAY'S DATE AS 'LAST SAVE DATE'", ""

End If

'Close Microsoft EXCEL
Application.Quit
End

End Sub

 

[Ron de Bruin]

Do you want to send one mail with all workbook names that are not updated today to one or more people?

Do I understand you correct

 

[Chuckles123]

I managed to come up with code to send the string 'sn' as part of th
'strSub' parameter that is sent to 'SendEmail'. I have decided that
would like to send the filenames that have not been updated as well.
Any help is appreciated.

Chuckles12

 

[Ron de Bruin]

Try it like this

Sub Example2()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long

'Fill in the path\folder where the files are
'MyPath = "C:\Data" or on a network :
MyPath = "\\DATA\BATCH FILE REPORTS"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If


'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

If DateValue(FileDateTime(MyPath & MyFiles(Fnum))) <> Date Then
strbody = strbody & MyFiles(Fnum) & vbNewLine
End If

Next Fnum
End If

MsgBox strbody

'your mailcode here that use strbody
'.TextBody = strbody
'http://www.rondebruin.nl/cdo.htm

End Sub

 

[Chuckles123]

Ron,
Many Thanks. I had to make a few adjustments, but it does
everything that I want.

Chuckles123