VBA - Mac - Find file in directory based on cell reference then attach onto an email? - UPDATED

[AdamPickard]

Hello guys,

I am by far no <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51);">VBA</acronym> expert and have managed this far by copying code from elsewhere.

Basically I need my <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51);">VBA</acronym> coding to locate an invoice .docx file within a specific file location (Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices based on a cell value ("C8") which is an invoice number that is included as part of the saved file name. There will only ever be one file to locate as the invoice number can only be saved within the filename once.

Currently everything works in terms of the email creation, however I cannot seem to get the file location and attachment parts working... any help would be greatly appreciated!

Here is what I have so far:

Sub Email_Invoice()'For Excel 2011 for the Mac and Apple Mail
'Note: The workbook must be saved once
Dim wb As Workbook


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 " & Range("C8").Value, _
toaddress:=Range("C9").Value, _
ccaddress:="", _
bccaddress:="Adam_Pickard1@hotmail.com", _
attachment:="Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices:", _ 'I NEED THE FILE LOCATION HERE THEN TO ATTACH
displaymail:=True
End With
Set wb = Nothing


End Sub

I have found the below code which will locate the correct file but it will not display the full file name and I actually want it to attach the file to the email I created above ^. In addition to this I am unsure how I would then get this code within the 'attachments' line above.

Sub AFilesSearch()'
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("Macintosh HD:Users:AdamPickard:Dropbox:InvoicesReceipts:Invoices:")
While (file <> "")
If InStr(file, Range("C8")) > 0 Then
MsgBox "found " & file
Exit Sub
End If
file = Dir
Wend
End Sub

I appreciate any helps with this!

Many thanks,

Adam

 

[AdamPickard]

Bump - Still puzzled