Wild card VBA for file attachment

[Access Beginner]

Hi All,

I found this bit of code on the internet (thanks ron) to send an email from Excel and attach a file. This works fine when I specify the path and file name (cell C16). In C16 I'll have the following 'Z:\ISB Data\Data Request Documents\Email files\Text.xlsx'. I have tried having 'Z:\ISB Data\Data Request Documents\Email files\*.xlsx', so it would attach any xlsx file in there ( my intention is to only have one file there at a time and place new files there as they are needed etc) but this did not work.

 Sub EmailBasedOnCells3()
    
'For Tips see: [URL]http://www.rondebruin.nl/win/winmail/Outlook/tips.htm[/URL]
'Working in Office 2000-2013
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strbody As String
    Dim rngAttach As Range
    
    'Set range for file attachment, cell should contain the path and file name
    With ActiveSheet
    Set rngAttach = .Range("c16")
    End With
    'Body of email is based on cells E2 to E60 or whatever range we end up using
     For Each cell In Range("E2:E60")
         strbody = strbody & cell.Value & vbNewLine
     Next
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "D").Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .CC = [EMAIL="someemail@blahblah.com"]someemail@blahblah.com[/EMAIL]
                .Subject = ThisWorkbook.Sheets("Email to Director").Range("c3").Value
                .Body = "Dear " & Cells(cell.Row, "B").Value & vbNewLine & vbNewLine & strbody
                'You can add files also like this
               .Attachments.Add rngAttach.Value
                .Display  'Or use Send, Send will send the email without you seeing it, safer to use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Any help or guidance is much appreciated

Cheers
Haydn

 

[Andrew Poulsom]

You can use Dir with wildcards to return the file name and assign it to a variable. Then use that variable in place of rngAttach.Value.

 

[Norie]

Haydn

You need the full path and filename for the attachment.

To get that you can use Dir, something like this.

strFileName = Dir(rngAttach.Value)

' other code

.Attachments.Add Replace(rngAttach.Value, "*.*","") & strFileName

' rest of code

 

[Access Beginner]

Thanks for the response guys, I will give it a go and let you know how it went.

Cheers
Haydn

 

[Access Beginner]

Thanks Norrie, got it working