VBScript to find Specific file in directory then E-mail file 1

EditionX · Apr 25, 2012

Hi everyone, I'm new on here and quiet recently new to VBScript.
I have the following code which is used to find a file in a directory.
I need to modify it so that when it finds the file it will E-mail it to me as an attachment. Is this possible, any help would be much appreciated.

Thanks in advance.

<code>
Const startDir = "C:\Documents and Settings\2336178\Desktop\Previous"
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFileName = "index.css"

Set oFolder = oFSO.GetFolder(startDir)
Recurse(oFolder)

Sub Recurse(oFldr)
If IsAccessible(oFolder) Then
For Each oSubFolder In oFldr.SubFolders
Recurse oSubFolder
Next

For Each oFile In oFldr.Files
If LCase(oFile.Name) = sFileName Then WScript.Echo sFileName, "exists."
Next
End If
End Sub

Function IsAccessible(oFolder)
On Error Resume Next
IsAccessible = (oFolder.SubFolders.Count >= 0)
End Function
</code>

tootiredtocare · Apr 25, 2012

Since your code finds the file, all you should need to do is launch outlook as a command with the appropriate switches

I found the following Microsoft page which should help with the switches

http://office.microsoft.com/en-us/outlook-help/command-line-switches-HP001003110.aspx

EditionX · Apr 26, 2012

Thanks for your reply,
I found this code online and have modified if slightly, however, when it find the file I just a message box saying that it exists.
Also on the server I am using it does not have outlook!
I have used the following code before to send an attachment via e-mail:

Set objEmail = CreateObject("CDO.Message")
objEmail.From = "SFTP_alert@dfpni.gov.uk"
objEmail.To = "My_Email"
objEmail.Subject = "Subject"
objEmail.Textbody = "Some Text"
objEmail.AddAttachment "Attachment location"
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "smtp_server"
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objEmail.Configuration.Fields.Update
objEmail.Send

I'm just not currently sure how to combine these to pieces of code to get the result I want.

As I've said I am very new to Vb Script and am learning as I go along at the minute.

Thanks again

EditionX · Apr 27, 2012

I have managed to solve the above problem myself. However I have now run into a new problem.

I wish to search to a file with a time and date stamp on it, however i only want to search for the first part of the file name.

For example:
If the file i was searching for is called "file.20120427.1037.result"
I want to search for the file name and the date just.
e.g "file.20120427" plus any text here ".result"

I'm pretty sure there is some sort of symbol (%,*(But not these))
where you can say:
Search for a file that starts with "file.20120427" and has extension ".result"

Here is my code for what I have so far:
The file I'm searching for is: ap_invtrans_CM_del.20120427.060004.723000.20120427.060024.083000.result

Code:

Const startDir = "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get"
Set oFSO   = CreateObject("Scripting.FileSystemObject")

if Month(date()) >= 1   Then
	if Month(date()) <= 9 Then
		DateToday = Year(Date()) & "0" & Month(date()) & Day(Date())
	else
		DateToday = Year(Date()) & Month(date()) & Day(Date())
	End if
End if

sFileName  = "ap_invtrans_CM_del." & DateToday & ".result"
Set objEmail = CreateObject("CDO.Message")
Set oFolder = oFSO.GetFolder(startDir)
Recurse(oFolder)
sendEmail(sFileName)

Sub Recurse(oFldr)
    If IsAccessible(oFolder) Then
        For Each oSubFolder In oFldr.SubFolders
             Recurse oSubFolder
        Next

        For Each oFile In oFldr.Files
            If LCase(oFile.Name) = sFileName Then WScript.Echo sFileName, "exists."
        Next
    End If
End Sub

Function IsAccessible(oFolder)
  On Error Resume Next
  IsAccessible = (oFolder.SubFolders.Count >= 0)
End Function

Sub sendEmail(oFile)
	
	objEmail.From = "Some E-mail"
	objEmail.To = "My E-mail"
	objEmail.Subject = "Some Subject" 
	objEmail.Textbody = "Some Text."
	objEmail.AddAttachment "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get\"& sFileName
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "smtpServer" 
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
	objEmail.Configuration.Fields.Update
	objEmail.Send
End Sub

guitarzan · Apr 27, 2012

You can use the Left() and Right() functions, something like this:

Code:

[COLOR=blue]sFileNameStart = "ap_invtrans_CM_del." & DateToday
sFileNameEnd = ".result"[/color]

Set objEmail = CreateObject("CDO.Message")
Set oFolder = oFSO.GetFolder(startDir)
Recurse(oFolder)
sendEmail(sFileName)

Sub Recurse(oFldr)
    If IsAccessible(oFolder) Then
        For Each oSubFolder In oFldr.SubFolders
             Recurse oSubFolder
        Next

        For Each oFile In oFldr.Files
            [COLOR=blue]If Left(LCase(oFile.Name), Len(sFileNameStart)) = LCase(sFileNameStart) And _
            	Right(LCase(oFile.Name), Len(sFileNameEnd)) = LCase(sFileNameEnd) Then
            	WScript.Echo oFile.Name, "exists."
            End If[/color]
            	
        Next
    End If
End Sub

jges · Apr 27, 2012

Here's an alternative that uses regular expressions:

Code:

[COLOR=green]'[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/ms974570.aspx[/URL][/color]
[COLOR=green]'[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/yab2dx62.aspx[/URL][/color]
[COLOR=green]'[URL unfurl="true"]http://www.regular-expressions.info/index.html[/URL][/color]
[COLOR=blue]set[/color] re [COLOR=blue]=[/color] [COLOR=blue]new[/color] regexp  
[COLOR=blue]dim[/color] strInput  

strInput [COLOR=blue]=[/color] "file.20120427.1037.result"  

re.Pattern [COLOR=blue]=[/color] "^file\.20120427.*\.result"  
re.IgnoreCase [COLOR=blue]=[/color] [COLOR=blue]true[/color]  
re.Global [COLOR=blue]=[/color] [COLOR=blue]true[/color]  

[COLOR=blue]Set[/color] Matches [COLOR=blue]=[/color] re.Execute(strInput)  
	wscript.echo("Matches.Count: " [COLOR=blue]&[/color] Matches.Count)  
		  
[COLOR=blue]if[/color]  Matches.Count >= 1 [COLOR=blue]then[/color]  
	value1 [COLOR=blue]=[/color] Matches.Item(0)  
[COLOR=blue]else[/color]  
	wscript.echo("No matches found")  
end [COLOR=blue]if[/color]  
wscript.echo(value1)

The pattern is what matters in the regexp
^ is an anchor that means the pattern must match the beginning of the line of text tested. "^file" matches "file123" but not "some_file123".
The backslash is an escape character to make sure we are looking for a literal dot after "file". The dot by itself is a wildcard character meaning "any character". The asterisk (or star) is a wildcard character meaning "match zero or more of the previous character or group". So the pattern looks for a line of text that starts with "file.20120427" followed by any number of any character followed by the literal string ".result".

As an aside, the following bit of your code:

Code:

if Month(date()) >= 1   Then
    if Month(date()) <= 9 Then
        DateToday = Year(Date()) & "0" & Month(date()) & Day(Date())
    else
        DateToday = Year(Date()) & Month(date()) & Day(Date())
    End if
End if

can be replaced with the following line:

Code:

DateToday = Year(Date()) & Right("0" & Month(Date()), 2) & Day(Date())

It is a neat little trick I picked up from this forum. To see it at work, run this code:

Code:

month1 [COLOR=blue]=[/color] 1  
month2 [COLOR=blue]=[/color] 10  
dateToday [COLOR=blue]=[/color] Year(Date()) [COLOR=blue]&[/color] "-" [COLOR=blue]&[/color] Right("0" [COLOR=blue]&[/color] month1, 2) [COLOR=blue]&[/color] "-" [COLOR=blue]&[/color] Day(Date())  
wscript.echo("month1 [COLOR=blue]=[/color] " [COLOR=blue]&[/color] month1 [COLOR=blue]&[/color] vbcrlf [COLOR=blue]&[/color] dateToday)  

dateToday [COLOR=blue]=[/color] Year(Date()) [COLOR=blue]&[/color] "-" [COLOR=blue]&[/color] Right("0" [COLOR=blue]&[/color] month2, 2) [COLOR=blue]&[/color] "-" [COLOR=blue]&[/color] Day(Date())  
wscript.echo("month2 [COLOR=blue]=[/color] " [COLOR=blue]&[/color] month2 [COLOR=blue]&[/color] vbcrlf [COLOR=blue]&[/color] dateToday)

I added in a separator so you can see the month value returned more easily.

EditionX · May 2, 2012

Thanks guitarzan & jges for your responses.

I have tried both these methods and have ran into problems with both.

I'll go over guitarzan's first:

I have changed my code as you have advised using the Left() and Right() function. However when i run the code it does not find any file! And just returns, "No file exists".
Here is how I have modified my code, it could be a simple mistake I've made, however I cant seem to find any myself!

* Note: The file I am Searching for is:
ap_invtrans_CM_del.20120502.060004.723000.20120427.060024.083000.result

Code:

Const startDir = "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get"
Set oFSO   = CreateObject("Scripting.FileSystemObject")

DateToday = Year(Date()) & Right("0" & Month(Date()), 2) & Right("0" & Day(Date()), 2)

sFileNameStart  = "ap_invtrans_CM_del." & DateToday
sFileNameEnd = ".result"
Set objEmail = CreateObject("CDO.Message")
Set oFolder = oFSO.GetFolder(startDir)
Recurse(oFolder)
sendEmail(sFileName)

Sub Recurse(oFldr)
    If IsAccessible(oFolder) Then
        For Each oSubFolder In oFldr.SubFolders
             Recurse oSubFolder
        Next

        For Each oFile In oFldr.Files
			
            If Left(LCase(oFile.Name), Len(sFileNameStart)) = LCase(sFileNameStart) And _
			   Right(LCase(oFile.Name), Len(sFileNameEnd)) = LCase(sFileNameEnd) Then 
				sFullFileName = oFile.Name
				WScript.Echo sFullFileName, "exists."
			else
			WScript.Echo "No File Exists."
			WScript.Quit
			End if
        Next
    End If
End Sub

Function IsAccessible(oFolder)
  On Error Resume Next
  IsAccessible = (oFolder.SubFolders.Count >= 0)
End Function

Sub sendEmail(oFile)
	
	objEmail.From = "From E-mail"
	objEmail.To = "My E-mail"
	objEmail.Subject = "Some Subject" 
	objEmail.Textbody = "Some Text."
	objEmail.AddAttachment "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get\"& sFullFileName
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "SMTP Server" 
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
	objEmail.Configuration.Fields.Update
	objEmail.Send
End Sub

jges I have tired your solution also, although I do find it harder to understand. Also thank you for your added info regarding the dates, it proved very useful.

With your solution it seem to find the file just fine, however I get an error when it tries to add it as an attachment! Here my code for your solution.

* Note: The file I am Searching for is:
ap_invtrans_CM_del.20120502.060004.723000.20120427.060024.083000.result

Code:

set re = new regexp
dim strInput

DateToday = Year(Date()) & Right("0" & Month(Date()), 2) & Right("0" & Day(Date()), 2)
strInput = "ap_invtrans_CM_del." & DateToday & ".result"

re.Pattern = "^ap_invtrans_CM_del\." & DateToday & ".*\.result"
re.IgnoreCase = true
re.Global = true

Set objEmail = CreateObject("CDO.Message")
Set Matches = re.Execute(strInput)
	wscript.echo("Matches.Count: " &Matches.Count)

if Matches.Count >= 1 then
	value1 = Matches.Item(0)
else
	wscript.echo("No matches found")
end if

wscript.echo(value1)
sendEmail(sFileName)

Sub sendEmail(oFile)
	
	objEmail.From = "From E-mail"
	objEmail.To = "My E-mail"
	objEmail.Subject = "Some Subject" 
	objEmail.Textbody = "Some Text."
	objEmail.AddAttachment "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get\"& value1
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "SMTP Server" 
	objEmail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
	objEmail.Configuration.Fields.Update
	objEmail.Send
End Sub

Thanks to both of you again

PHV · May 2, 2012

What about this ?

Code:

Const startDir = "C:\Program Files\SFTPPlus\inbox\testAccount_sftp_get"
Set oFSO = CreateObject("Scripting.FileSystemObject")
DateToday = Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2)
sFileNameStart = "ap_invtrans_CM_del." & DateToday
sFileNameEnd = ".result"
Set objEmail = CreateObject("CDO.Message")
Set oFolder = oFSO.GetFolder(startDir)
sFullFileName = ""
Recurse oFolder
If sFullFileName <> "" Then
    sendEmail
Else
    WScript.Echo "No File Exists."
End If

Sub Recurse(oFldr)
For Each oSubFolder In oFldr.SubFolders
    Recurse oSubFolder
Next
For Each oFile In oFldr.Files
    If Left(LCase(oFile.Name), Len(sFileNameStart)) = LCase(sFileNameStart) And _
       Right(LCase(oFile.Name), Len(sFileNameEnd)) = LCase(sFileNameEnd) Then
        sFullFileName = oFile.Name
        Exit Sub
    End If
Next
End Sub

Sub sendEmail()
objEmail.From = "From E-mail"
objEmail.To = "My E-mail"
objEmail.Subject = "Some Subject"
objEmail.Textbody = "Some Text."
objEmail.AddAttachment startDir & "\" & sFullFileName
With objEmail.Configuration.Fields
    .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
    .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "SMTP Server"
    .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
    .Update
End With
objEmail.Send
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

EditionX · May 2, 2012

Thank you so much, that worked first time.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

VBScript to find Specific file in directory then E-mail file 1

EditionX

Technical User

tootiredtocare

Technical User

EditionX

Technical User

EditionX

Technical User

guitarzan

Programmer

jges

Technical User

EditionX

Technical User

PHV

MIS

EditionX

Technical User

Similar threads

Part and Inventory Search

Sponsor