CODEWITHSUNDEEP
exceloutlookvba
Graham Chandler
Graham Chandler

Reputation: 193

Excel VBA: attach files to email without full file name

I have to distribute files by email to hundreds of people on a monthly basis. I'm attempting to write a macro that will do this automatically for me. So far, i've got it to create the emails, automatically fill in the body, subject line, and the receipients. I need to be able to get it to automatically attach each persons file to the email. The file names contain the last 6 digits of their account number. I get a report of all of the account numbers i need to send files out to. Is there anyway that i can use those 6 digits to search for the file, and attach it using VBA?

For Example:

say my account numbers are S0001234, S0005678, S0009012. my file names are "ABC - 001234", "DEF - 005678", and "GHI - 009012".

I would reference the entire filename manually but they arent always consistent. I can use the full path to the files though. Can i attach the file somehow by only referencing these 6 digits in VBA? For instance, search for any files containing these 6 digits? any help would be appreciated.

Upvotes: 0

Views: 3246

Answers (1)

luke_t
luke_t

Reputation: 2975

You can use the Dir() function within VBA to achieve what you're after.

Sub getFileName()
    Dim strFileName As String
    Dim strCustDigits As String
    strCustDigits = "123456"
    strFileName = Dir("C:\Users\lturner\Documents\*" & strCustDigits & "*")
End Sub

strFileName will return the the full name (including file format extension) of your workbook.

The asterisk either side of the strCustDigits variable denotes that the filename can have anything either side of the digits; it will return the first file it finds within the provided folder, which has the strCustDigits variable within it's name.

EDIT: Updated for additional question asked in comments.

If you have multiple files, you can loop through each file in a folder (which contains the specified digits) by using the below.

Sub getFileNameLoop()
    Dim strFileName As String, strCustDigits As String
    strCustDigits = "123456"
    strFileName = Dir("C:\Users\lturner\Documents\*" & strCustDigits & "*")
    Do While Len(strFileName) > 0
        ' Perform action with file which contains the provided digits
        Debug.Print strFileName
        strFileName = Dir
    Loop
End Sub

For further reading on how the Dir() function works, please see here.

Upvotes: 1

Related Questions