File search returning multiple files

majsparky · May 13, 2005

Need some help from the Pros. I have been wrestling with a problem for a while now and have not been able to find the solution. The problem is to find a specific file and then attach it to an e-mail. The section of code below works fine except if, for example I am trying to find a QuoteNo file which is 101010.dwg. It finds and attaches the 101010.dwg but it also finds and attaches 93101010.dwg or 31010103.dwg. What am I missing in the coding that will only find and attach the exact file I want.

Would be greatful for any help.

'**************************************************************************************************************
'Send E-mail Routine
'**************************************************************************************************************
Dim DrwMsg As String
Dim PdfMsg As String
DrwMsg = ""
PdfMsg = ""
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = Me![cust e-mail]
.Subject = Me!Quote
'.Body = "Please find attached Quote and/or drawings associated with " & Me!Quote & "." & Chr(13) & Chr(13)
If DirChk <> "" Then
.Attachments.Add DirChk
End If

'*********************************************************************
'Search for Drawings
'*********************************************************************
d2 = QuoteNo
FS.NewSearch
FS.LookIn = "F:\Group\Quotes\" & Environ("Name") & "\"
FS.SearchSubFolders = True
FS.FileName = d2 & ".dwg"
FS.MatchTextExactly = True
If FS.Execute > 0 Then
Dim Msg3, Style3, Title3, Help3, Ctxt3, Response3, adddrw3
Msg3 = "There are Drawing associated with this Quote Number." & _
" Do you want to add these drawing to the e-Mail?"
Style3 = vbYesNo + vbInformation + vbDefaultButton1
Title3 = "Drawing Files Found"
Response3 = MsgBox(Msg3, Style3, Title3, Help3, Ctxt3)

If Response3 = vbYes Then
adddrw3 = "Yes"
Else
adddrw3 = "No"
End If

If adddrw3 = "Yes" Then
DrwMsg = "Also attached are AutoCad Drawings associated with this Quotation."
ReDim DArray(FS.FoundFiles.count)
For i = 1 To FS.FoundFiles.count
DArray(i) = FS.FoundFiles(i)
Next i
For i = 1 To FS.FoundFiles.count
Draw1 = DArray(i)
.Attachments.Add Draw1
Next i
End If
End If

MarkK · May 14, 2005

I believe the intent of the FileSearch object is to return a collection of files, and my read on the MatchTextExactly Property is that it returns matches of properties and text within the file, not the file name iteself. Here's some code that might work...

Code:

Dim varLoop as Variant

Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
   .To = Me![cust e-mail]
   .Subject = Me!Quote
   .Body = "Please find attached Quote and/or drawings associated with " &         Me.Quote & "." & vbcrlf & vbcrlf
   If DirChk <> "" Then .Attachments.Add DirChk
end with

fileName = QuoteNo & ".dwg"
with fs
   .NewSearch
   .LookIn = "F:\Group\Quotes\" & Environ("Name") & "\"
   .SearchSubFolders = True
   .FileName = fileName
   If .Execute > 0 Then
      If MsgBox("There is a Drawing associated with this Quote Number." & _
         "Do you want to add it to the e-Mail?", _
         vbYesNo + vbInformation, _
         "Drawing File Found") = _
      vbYes Then
[COLOR=Green]         'loop thru files returned by fs, and keep the one that matches
[/COLOR]         For Each varLoop in .FoundFiles
            If cstr(varLoop) = fileName then 
               With objMessage
                  .Attachments.Add cstr(varLoop)
                  .Body = .Body & "Also attached is an AutoCad Drawing associated with this Quotation."
               End With
               Exit For
            End if
         Next varLoop
      End If 'msgbox

   Else
      MsgBox "No Drawing found."
   End If

End With  'fs

I doubt this code will work first try but it might give you some more choices about how to proceed.
Mark

File search returning multiple files

majsparky

Registered User.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread