Sending Variable Via E-Mail

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 "[email protected]", "", _
"ONE OR MORE FILES HAVE NOT BEEN UPDATED: ", ""

Else
SendEmail "[email protected]", "", _
"ALL FILES HAVE TODAY'S DATE AS 'LAST SAVE DATE'", ""

End If

'Close Microsoft EXCEL
Application.Quit
End

End Sub

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Chuckles123" <[email protected]> wrote in message
news:[email protected]...
>
> 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 "[email protected]", "", _
> "ONE OR MORE FILES HAVE NOT BEEN UPDATED: ", ""
>
> Else
> SendEmail "[email protected]", "", _
> "ALL FILES HAVE TODAY'S DATE AS 'LAST SAVE DATE'", ""
>
> End If
>
> 'Close Microsoft EXCEL
> Application.Quit
> End
>
> End Sub
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=565285
>

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

Chuckles123

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Chuckles123" <[email protected]> wrote in message
news:[email protected]...
>
> I managed to come up with code to send the string 'sn' as part of the
> 'strSub' parameter that is sent to 'SendEmail'. I have decided that I
> would like to send the filenames that have not been updated as well.
> Any help is appreciated.
>
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=565285
>

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

Chuckles123