CODEWITHSUNDEEP
vbaexcel
andimac
andimac

Reputation: 39

Trying to add an attachment to an email from excel where only the first part of the file name is known

I have a macro which I am using to attach an automatically generated file to an email on a daily basis.

The filename is required to be a certain format which includes the date and time, and as this is automatic, only the date will be known inherently (without manually checking the file).

I have tried to substitute * like I saw on a forum but it where it seemed to work in that example it is not working for me. Am I missing something?

.Attachments.Add ("G:\AML, CFT & Sanctions\Sanctions\KYC6 Person & Organsation Reports\" & Format(Date, "yyyy") & "\" & Format(Date, "mmmm") & "\65436546_Test_" & Format(Date, "yyyymmdd") & "*.csv")

Upvotes: 0

Views: 1479

Answers (2)

ivan_pozdeev
ivan_pozdeev

Reputation: 35998

As @Kostas suggested, you can find files by a glob with the Dir function. Note that it only returns filename, without a path.

Do handle the case when nothing or more than one file matches the pattern. (My code produces an error in these cases; error codes are taken from http://www.halfile.com/vb.html .)

Dim date_ As Date, pattern, dir_, filename As String: date_ = Date

dir_ = "C:\Users\Ivan\Documents\test & test\" & _
       Format(date_, "yyyy\\mmmm\\")
pattern = "65436546_Test_" & Format(date_,"yyyymmdd") & "*.csv"

filename = Dir(dir_ & pattern)
If Len(filename) = 0 Then Error 53 'File not found
If Len(Dir()) <> 0 Then Error 58 'More than one matching file

<email>.Attachments.Add(dir_ & filename)

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

Build the file path first, test it and attach it if it's valid. As advised in comments, you need to supply a concrete file name, wildcards are not allowed.

Dim path_ As String, name_ As String, file_ As String

path_ = "C:\Some folder\"
name_ = "*.csv"

file_ = Dir(strPath & name_)

If Len(Dir(path_ & file_)) > 0 Then
    .Attachments.Add path_ & file_
End If

Upvotes: 1

Related Questions