Excel 2010 Application.FileSearch replacement

[mrdoyle]

Hi all, I’m new to the forum and a bit rusty with my VB. It’s been a good few years since I've used it and boy have things changed.

I have a VB script in an Excel spread sheet that uses the data found in some of its cell references to search for Outlook Contacts of the same name, and then email them files found in a folder on a network drive. In the folder are additional folders only some of which will contain files. Each day is different. Once it sends the emails it then moves the files to an archive folder.

My problem is that we have just upgraded from Office 2003 to 2010 and application.filesearch is no longer supported. Does anyone know how I can get around this?

One of the file search locations that are failing is:

'Add attachments to the message.
Set fs = Application.FileSearch
With fs
.LookIn = sAttachPath
.Filename = "*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
Dim sAttatch(40) As String
'Limited to 40 files per folder I think
For i = 1 To .FoundFiles.Count
sAttatch(i) = .FoundFiles(i)
b = b + 1
Next i
Else
MsgBox "There were no files found in the folder - " & Cells(a, 1)
End If
End With
For c = 1 To b
Set objOutlookAttach = .Attachments.Add(sAttatch(c))
Next c
If b <> 0 Then .Send
b = 0
End With

Any help would be greatly appreciated.

 

[Bong]

Can you use the dir(pathname) function. As I've used it in the past, the first call is with a pattern (d:\abc\*.txt) as an argument and it finds the first match. I think it actually builds an iterator since subsequent calls with no argument, dir(), return further matches to the same pattern.

_________________
Bob Rashkin