Send Files from access with wildcards

[EliseFreedman]

Hi

I am currently getting to grips with our new email system. I use the system to send various files on a daily basis and I am trying to write a program in access to automatically create the emails
The program works as long as I know the filename. However one of the files to be send has an extension which changes each day. for example
upsales.525 one day
upsales.526 the next day

The following is the code that I am using to send the file where the name is the same each day.

With .Attachments.Add("C:\sales info data\upsales.525&quot
.DisplayName = "upsales"
End With

Ideally I would like to set it to attach any file starting with upsales regardless of the extension.

I have tried upsales.* and I have also tried getting the exact filename using Dir, putting the filename into a string and then sending that as shown below


Dim sFile As String

sFile = Dir("c:\sales info Data\upsales.*&quot
If sFile <> &quot;&quot; then
debug.print sfile
Else
MsgBox &quot;Can't Find File&quot;
End If

With .Attachments.Add(&quot;sfile&quot
.DisplayName = &quot;upsales&quot;
End With

However, both methods have failed. I get a message telling me that the system cant find the file specified

Does Anyone have any other ideas

 

[SIG357]

The FileSystemObject and it's methods will let you enumerate all the files in a specified folder. Using fso you should be able to test the name of each file you encounter and attach the ones you want.

 

[EliseFreedman]

Thanks for your help in pointing me in the right direction.

Would you be able to post some sample code for searching for a file using fso as I have never come across this before

Thanks for your assistance

Elise

 

[SIG357]

Dim fso as FileSystemObject
Dim fld as Folder
Dim fl as File

Set fso = New FileSystemObject
Set fld = fso.GetFolder(&quot;C:\sales info data&quot
For Each fl In flr.Files
If Left$(fl.Name, 8) = &quot;upsales.&quot; Then
With .Attachments.Add(&quot;fl.Path&quot
.DisplayName = &quot;upsales&quot;
End With
End If
Next
Set fld = Nothing
Set fso = Nothing

Hope this helps. Lemme know if you need more!

 

[EliseFreedman]

Hi Sig

Ive now had a chance to try the code.

However it blows up at the line
With .Attachments.Add(&quot;fl.Path&quot

I get the same error as before &quot;the sytem cannot find the file specified.&quot;
When I placed my cursor over fl.path it came up with
fl=&quot;c:\sales info Data\upsales.530&quot;.
I have checked and that file is in the directory.

Have you any suggestions

 

[SIG357]

Maybe the .Add Method dosn't like long filenames? Try creating a temp dir and a test file, then attaching that:

.Attachments.Add(&quot;C:\Test\test.txt&quot

What email system are you using?

 

[danvlas]

Wrong:
With .Attachments.Add(&quot;sfile&quot;)


Right:

With .Attachments.Add(sfile)

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SIG357]

good eye danvlas!
That one slipped right by me

 

[danvlas]

Sure...hawkeye with thick glasses [LOL]

Sig, about FSO...It works great, but if user disables Windows Scripting Host, it doesn't work at all.

And it is surely not needed in Elise's program. The old Dir is better here: just one line of code, no object instatiated...

I have recently participated in a thread dealing with it, you may take a look at:
thread705-550174

Regards,



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SIG357]

Right on dan.
If all elise needs is the file name to look at, Dir is certainly sufficient, and probably more Efficient. THe only reason I suggested FSO was that it would allow more options in picking the files, possibly using other properties of the File object as criteria.

I still can't believe I missed the quotes.... :

 

[EliseFreedman]

Thanks all of you for your help. I've managed to get it to work now - decided to use FSO purely from a learning point of view. Getting this to work has saved me hours in the day - more time for reading this excellent forum!!!