macro to take last name and find file that contains last name

[psrs0810]

How can I take a list of people and in a specific folder, find a report that contains their last name and attach to an e-mail.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If column A has their first name and B has the last name. the file I need to search for will contain their last name plus more information.<o></o>
<o> </o>
Right now my macro will go to a specific path and file name to send out. I would like to change to look at the path and then find the file that contains their last name.
Sub multipleattach()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the E:L column in each row
Set rng = sh.Cells(cell.Row, 1).Range("E1:L1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = cell.Offset(0, 1).Value
.Body = cell.Offset(0, -1).Value & vbCrLf & cell.Offset(0, 10).Value

For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell

.Send 'Or use Display Can change to .Save to put into drafts folder
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "Done!"
End Sub

 

[Ruddles]

The following function takes a folder path and a character string, then returns the name of the first file in the folder whose name contains that string. Insert a new standard module into your VBA project and paste the code into it.

Option Explicit
Option Compare Text
 
Public Function FindFile(ByVal aFolder As String, ByVal aSearch As String) As String
 
  Dim sFilename As String
 
  sFilename = Dir(aFolder)
  Do Until sFilename = "" Or InStr(sFilename, aSearch) > 0
    sFilename = Dir()
  Loop
 
  If InStr(sFilename, aSearch) > 0 Then FindFile = sFilename
 
End Function

Your code's a bit difficult to follow as you posted it without any indenting and in a proportionally-spaced font. If you post your code in CODE tags - the # icon in the editor toolbar - it will be displayed in fixed-width font and with indenting preserved, both of which make it easier to read and this in turn encourages responses.

Where's the surname? Is it in FileCell?

If so, you'd call the function like this:-

AttFilename=FindFile("c:\temp\",FileCell.Value)
If AttFilename <>"" Then .Attachments.Add "c:\temp\" & FileCell.Value