CODEWITHSUNDEEP
vbaoutlookwildcard
Nikunj
Nikunj

Reputation: 25

Wildcard for file path for adding attachments

I want to add attachments from a specific folder. I specified the file's path and two keywords which are fixed.

There are more characters to complete the file path after 'filename2' and before 'pmonth' which are not fixed and hence I need to use wildcard (*).

The code gives

'Couldn't find file'

I have gone through various threads and tried solutions. None works for what I want.

For ctr = 2 To lastrow

    filename1 = Cells(ctr, 1).Value
    filename2 = Cells(ctr, 3).Value
    
    Set OutMail = OutApp.CreateItemFromTemplate(str_template)
    
    path = "C:\Users\nikunj.v.tripathi\Desktop\" & filename1 & "_" & filename2 & " -" & "*" & pmonth & " " & syear & ".xlsx"

    With OutMail
  
        .Attachments.Add path           ' <----- this line gives error

        .To = Cells(ctr, 10).Value
        .cc = Cells(ctr, 11).Value
        .htmlbody = Replace(.htmlbody, "#Month#", smonth)
        .htmlbody = Replace(.htmlbody, "#CLIENT NAME#", Cells(ctr, 1).Value

        .Save
    End With
Next ctr

Upvotes: 1

Views: 798

Answers (2)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66215

Of course - Attachments.Add adds a single attachment and returns the Attachment object. How can it possibly add multiple attachments?

You can use Scripting.FileSystemObject to loop through all files in a folder and add one attachment at a time. See, for example https://devblogs.microsoft.com/scripting/how-can-i-get-a-list-of-all-the-files-in-a-folder-and-its-subfolders/

Upvotes: 0

Sam
Sam

Reputation: 381

To use the Dir function effectively in this case, you'll need the path and the file name as two separate variables. Assuming you add another variable called filename, you could then utilise the following code...

...

path = "C:\Users\nikunj.v.tripathi\Desktop\"
filename = filename1 & "_" & filename2 & " -" & "*" & pmonth & " " & syear & ".xlsx"

...

filename = Dir(path & filename) ' Dir returns the filename of the first file matching
                                ' the criteria, or returns an empty string if no match.

Do Until filename = ""
    .Attachments.Add path & filename
    filename = Dir ' Using Dir again returns the next file matching
                   ' the criteria, or returns an empty string if no match.
Loop

Upvotes: 1

Related Questions