Solved Attaching file to outlook email from Access

[miacino]

Hi folks!
I'm trying to attach a file to an outlook email from Access. However, I don't always have the exact file name.
How can I call out the filename to include anything starting with something?

FileName = "G:\CLASP\CLASP Tools\RG_" & [Guideline] & ".docx"

I want it to pull anything in that folder that starts with RG_GuidelineName

So could be:

RG_Guideline1 large.docx
RG_Guideline1 small.docx

There would only be 1 file in that folder that would match.

I tried with *, but that didn't work:
FileName = "G:\CLASP\CLASP Tools\RG_" & [Guideline] & "*.docx"

Not sure if this is possible, but thanks for any insight!

 

[theDBguy]

You could try using the Dir() function. For example.

FileName = Dir("G:\CLASP\CLASP Tools\RG_" & [Guideline] & "*.docx")

Hope that helps...

 

[bastanu]

Here is what I use, I like to check if the file exists and keep the folder and file names separate, but otherwise similar to previous post:

  Dim sPattern As String, sFolder As String, sFile As String,sSourceFile as String
    
    sFolder = "G:\CLASP\CLASP Tools"
    sPattern = "RG_" & [Guideline] & "*.docx"   
    sFile = Dir(sFolder & "\" & sPattern)
        If sFile = "" Then
            MsgBox "NOT FOUND: " & sFile & " in " & sFolder & "!"
            Exit Sub
        End
    End If
    sSourceFile = sFolder & sFile

Cheers,

 

[arnelgp]

Path = "G:\CLASP\CLASP Tools\"
FileName = Dir$(Path & "RG_" & [Guideline] & "*.docx")
With olMail
    Do Until Len(FileName) = 0
        .Attachments.Add Path & FileName
        FileName = Dir$
    Loop
    
    'other codes here
    '.To =
    '.Subject =
    '.HtmlBody =
    '.Send
End With

 

[miacino]

Thank you all for your suggestions. Still does not seem to work. If I name the file just RG_ and guideline name (i.e., RG_Anemia), it finds the file, however if the file is named "RG_Anemia 8.17.22", it does find it.

 

[miacino]

I got it to work!
strPath = "G:\CLASP\CLASP Tools\" & [Guideline] & "\"
strFilter = "RG_*.docx"
strFile = Dir(strPath & strFilter)

 

[miacino]

But on that same note, wondering if one of the file paths has a date?

The file path is = "G:\CLASP\CLASP Committee\2022 meetings\8-8-2022"
The field [PH-ISAC] is a date field 8-8-2022.
I have the date field in access formatted exactly as the file path above, but somehow it is not accepting it...

strPath2 = "G:\CLASP\CLASP Committee\2022 meetings\" & [PH_ISAC] & "\"
strFilter2 = "Agenda*.docx"
strFile2 = Dir(strPath2 & strFilter2)

 

[Gasman]

Format is just that, a format. It does not change the data.
So apply that format again for the file path.

 

[bastanu]

Try to wrap the field in the Format () function:
Format([PH_ISAC], "m-d-yyyy") & "\"

 

[miacino]

Try to wrap the field in the Format () function:
Format([PH_ISAC], "m-d-yyyy") & "\"

That didn't seem to work either:

    strPath2 = "G:\CLASP\CLASP Committee\2022 meetings\" & Format([PH_ISAC], "m-d-yyyy") & "\"

 

[miacino]

Format is just that, a format. It does not change the data.
So apply that format again for the file path.

Do you mean to mirror change the name of the path to mirror how Access would read it? Access reads dates as long format and you cannot name a path with "/"

 

[Gasman]

Dates are not in Long data type. They are just an integer of another data type that allows decimals? Double perhaps?
Bastanu has already shown you the format?

Debug.Print strPath2 and see what that shows you.
Always check what you have, do not rely on what you think you have.

 

[bastanu]

As suggested use CTrl+G to open the immediate window and inspect the value of the strPath2 variable. What is the format, mine was a guess, maybe you have it "d-m-yyyy", can't really say by looking at 8-8-2022.....

 

[miacino]

Thank you all. My formatting was off. Worked as such.

    strPath2 = "G:\CLASP\CLASP Committee\2022 meetings\" & Format([PH_ISAC], "m-d-yyyy") & "\"

 

[bastanu]

You're very welcome, good luck with your project!
Cheers,