VBA - Excel 2011 - Mac - Searching for file within a folder

[AdamPickard]

Hello guys,

I am having an issue when trying to search for a file (based on a cell reference) within a specific directory using the MacID function.

The code I am currently using will attach only the entire folder and not the specific file within the folder:

Function getFileName(sSearchTerm As String) As String

getFileName = Dir("Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices:", MacID("TEXT"))


'Also tried MacID("DOCX")
'Also tried Dir("Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices:") & sSearchTerm & MacID("TEXT") - This returned no files


End Function




Sub Email_Invoice()
'For Excel 2011 for the Mac and Apple Mail
Dim wb As Workbook

'TEST BELOW
Dim sSearchTerm As String
Dim sFileName As String


sSearchTerm = Range("C8").Value

sFileName = getFileName(sSearchTerm)


If Val(Application.Version) < 14 Then Exit Sub


Set wb = ActiveWorkbook
With wb
MailFromMacWithMail bodycontent:="Dear " & Range("C6").Value & "," & vbNewLine & vbNewLine & "Please find attached your Invoice." & vbNewLine & vbNewLine & "Kind Regards," & vbNewLine & vbNewLine & vbNewLine & "The Element 8 team" & vbNewLine & vbNewLine, _
mailsubject:="Element 8 - Outstanding Invoice - Invoice Number - " & Range("C8").Value, _
toaddress:=Range("C9").Value, _
ccaddress:="", _
bccaddress:="", _
attachment:="Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices:" & sFileName, _
displaymail:=True
End With
Set wb = Nothing


End Sub

The cell "C8" is an invoice number which can only be located within 1 saved file name.

Any suggestions?

Thanks,