Send mail if a certain file is in a certain folder.

[bodo62]

Hi.

A have an application that creates different files and saves them into a folder. This can occur on different times during the day depending on what week and week day it is.

To avoid checking manually several times a day, I'd like to have excel or word to check the contents of the mentioned folder and if a certain filename is found, a mail message should be sent to me and the file should be deleted to avoid a loop in sending the message.

Can you please help me getting started...?

Thanks in advance.

 

[lucrahouse]

Here is some code I cut from various projects that I thought would help you accomplish your goal. I didn't verify every variable name, so you have to be careful. Naturally, you will have to modify it to suit your needs. I included all the public variables names and modified others, but your a smart fellow and I'm sure you can step through this code very easily. Also check available references libraries. I have OLE Automation, Microsoft ActiveX Data Objects 2.5 Library, Visual basic for application, and microsoft Excel 9.0 object library and Microsoft Office 9.0 Objects Library checked off. Good luck.

Public MyFullName As String
Public MyFileName As String
Public LenOfMyFullName As Integer
Public LenOfMyFileName As Integer
Public My_Path As String
Public My_OpenFileName As String
Public TempArray() As String
Public MyCounter As Integer
Public strMyDateTime As String
Public SpecList_b As Boolean

Sub GetListOfFiles()
' Gets a list of text files and holds the names in a
' temporary array while we use them in different sub routines
Application.ScreenUpdating = False
MyFileName = ThisWorkbook.Name
MyFullName = ThisWorkbook.FullName
LenOfMyFullName = Len(MyFullName)
LenOfMyFileName = Len(MyFileName)
My_Path = Left(MyFullName, LenOfMyFullName - LenOfMyFileName) MyCounter = 0
MyStem = False
file = Dir(My_Path)

Do While file <> ""
If file <> "." Then
If file <> ".." Then
If Right(file, 3) = "txt" Then
MyCounter = MyCounter + 1
ReDim Preserve TempArray(1 To MyCounter)
TempArray(MyCounter) = file
If MyStem = False Then
MyStartValue = Mid(file, 1, 1)
Do While MyStartValue <> ""
If MyStartValue = "_" Then
' example of file I expect
' filename_20040309-1606.txt
MyDate = Mid(file, start1 + 5, 2) & "/" & Mid(file, start1 + 7, 2) & "/" & Mid(file, start1 + 1, 4)
MyTime = Mid(file, start1 + 10, 2) & ":" & Mid(file, start1 + 12, 2)
strMyDateTime = MyDate & " - " & MyTime
Exit Do
Else
start1 = start1 + 1
MyStartValue = Mid(file, start1, 1)
End If
Loop
MyStem = True
End If
End If
End If
End If
file = Dir()
Loop

End Sub

Sub MoveFilesFromDirectoryToSubDir()

For i = 1 To MyCounter
MyOldFileName = My_Path & TempArray(i)
MyNewFileName = My_Path & "\newdir\" & TempArray(i)
FileCopy MyOldFileName, MyNewFileName
Kill MyOldFileName
Next i

End Sub

Sub Sends_Successful_Email()

Dim fDerEmail1 As String
Dim fDerEmail2 As String
Dim fDer As Double
Dim fDerDesc As String
Dim NbrOfDers As Integer
Dim MyBody As String
Dim MySubject As String

Worksheets("Sheet1").Activate
Range("a1").Select

Set Table1 = ActiveCell.CurrentRegion
Set Email_tbl = Table1.Offset(1, 0).Resize(Table1.Rows.Count - 1, Table1.Columns.Count)
NbrOfDers = Email_tbl.Rows.Count
MySubject = "Changes have been successfully changed"

fDerEmail1 = "EmailAddress1@someplace.com"
fDerEmail2 = "EmailAddress2@someplace.com"
For x = 1 To NbrOfDers
fDer = Email_tbl.Cells(x, 1).Value
fDerDesc = Email_tbl.Cells(x, 2).Value
fOldSISG = Email_tbl.Cells(x, 3).Value
fNewSISG = Email_tbl.Cells(x, 4).Value
MyBody = fDer & " - " & fDerDesc & " has changed from " & fOldSISG & " to " & fNewSISG & vbCrLf & MyBody
Next x

Set MyOlApp = CreateObject("Outlook.Application")
Set MyItem = MyOlApp.CreateItem(olMailItem)
Set MyAttachments = MyItem.Attachments
Set MyRecipients = MyItem.Recipients
MyItem.Recipients.Add fDerEmail1
MyItem.Recipients.Add fDerEmail2

With MyItem
.Subject = MySubject
.Body = MyBody
End With

'MyItem.display
MyItem.send
Set MyOlApp = Nothing
Set MyItem = Nothing
Set MyAttachments = Nothing
Set MyRecipients = Nothing

End Sub

 

[fumei]

1. Look up the use of the FileSystemObject, and in particular, methods that check for if file exists.

2. check in these forums for how to send email messages. You will likely have to manually accept the security checks for sending mail. There are third-party workaround to get through the demand for you to explicitly accept it is OK to send the mail.

3. Consider the fact that unless you want to suck up resources, you will have to call this routine to check deliberately. You can not really have code running that is CONSTANTLY checking for the file. That would be very wasteful, as you would have to keep the code in memory and constantly running. No generally a good thing. So it would have to be a periodic checking that you call.

Gerry

 

[Homersim2600]

Function FindFile(ByVal File As String) As Boolean
Dim fso As FileSystemObject
Dim msg As Boolean
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(File)) Then
msg = True
Else
msg = False
End If
FindFile = msg
End Function

This Function will return a True or False. you must make a reference Microsoft Scripting Runtime under "Projects\references" in order for it to work. to call this function, simply type...

Your Variable here = FindFile("c:\Location to your file")

Then you can encapsulate this code into a IF-THEN statement, if it is true, meaning that the file was found, then send mail, if it is false, then don't...

Hope this helps...

Hint: if you need to see if a folder is present, then change all of the references from "File" to "Folder". It works great...

LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein