Email Zip Files with Recordset

[Guest]

Hi,

I'm seeking help with the following scenario.
I have a table that contains employeeid, and employee names and email
addresses.
I have zipped files located on a hard drive that containts the employee Id
as the name.
For example: Employye Id: 12345 Employee Name: John Smith
Zip FIle Name: 12345.zip

How do I use a recordset to search for the the zip file on the hard drive
and then and email the specific zip file to each employee?

Any light that you can shed on this is appreciated.

 

[Guest]

I am not sure why you would use a recordset for this. If you are using a
licenses copy of Winzip, you can actuall shell from Access, create the zip
file, and then E-mail the zip file you just created. I did this about5 yrs
ago. As I recall, the ability to shell to winzip and pass it parameters is a
downloadable that allows command line access to Winzip. If you are creating
the zip file outside Access, why not just E-mail it manually?

 

[Guest]

Klatuu,

Thanks for your input. I thought the recordset was the easiest way to do
this. If this is not - I'm definitely interested in learning a new way.

Basically, there are approximately 220-300 zip files already created and
stored on a hard drive. The names of these zipped files are named according
to employee Ids, as I stated in the first post. I have a a table that
contains each employye name, email address and their employee id.

I am trying to have MS Access find the zip file on the hard drive and find
the corresponding employee id and email address from the Access table. If
there is a match (the zipped file and employee id from the MS Access table)
then I want to send the zip file automatically to the recepient.

Hope that clarifies things...

 

[Guest]

MLamour,

Okay, now that I have more detail, you will need the recordset you
described. To find all the files you want, the best way to do that is with
the FileSearch object. You can read up on it in Access Help. It will return
an object that will have the files you want. Conceptually, it is like a
Tabledefs object where Tabledefs have tables, for a Filesearch object, it has
filenames (with path). You would adress it the same way.

 

[Immanuel Sibero]

MLamour,

You may not need to do a file search.
Another option is to have a textbox on your form asking the user to specify
which folder contains all the zip files.
Since your table contains EmployeeID and the zip files are named using
EmployeeID. Conceptually, all you need to do is:
- parse through your table
- for each employee record, use Dir() function to check for the existence of
a file called [EmployeeID].zip in the folder specified by the user above.
- if the file exists, send an email and attach the file.
- you may even add a field in your table to indicate if the file is sent or
not, what date it is sent, etc. etc.

This logic would be more efficient than searching for the file for every
employee record.

Immanuel Sibero

 

[Guest]

I disagree. You method would require a Dir() call for every employee. A
Filesearch is one call. You would still need to tell the Filesearch what
directory to find the files in. Then you would have the Filesearch object
you could loop through looking for the matching employee and sending the
E-mail. I believe the logic would be simpler and the processing more
efficient.

MLamour,

You may not need to do a file search.
Another option is to have a textbox on your form asking the user to specify
which folder contains all the zip files.
Since your table contains EmployeeID and the zip files are named using
EmployeeID. Conceptually, all you need to do is:
- parse through your table
- for each employee record, use Dir() function to check for the existence of
a file called [EmployeeID].zip in the folder specified by the user above.
- if the file exists, send an email and attach the file.
- you may even add a field in your table to indicate if the file is sent or
not, what date it is sent, etc. etc.

This logic would be more efficient than searching for the file for every
employee record.

Immanuel Sibero

Klatuu,

Thanks for your input. I thought the recordset was the easiest way to do
this. If this is not - I'm definitely interested in learning a new way.

Basically, there are approximately 220-300 zip files already created and
stored on a hard drive. The names of these zipped files are named according
to employee Ids, as I stated in the first post. I have a a table that
contains each employye name, email address and their employee id.

I am trying to have MS Access find the zip file on the hard drive and find
the corresponding employee id and email address from the Access table. If
there is a match (the zipped file and employee id from the MS Access table)
then I want to send the zip file automatically to the recepient.

Hope that clarifies things...

 

[Immanuel Sibero]

Klatuu,

As far as which one is faster, I guess it would be comparing ONE dir() call
against ONE loop of the Filesearch object. I havent really tested it, so I
might have been incorrect claiming that the dir() method is faster. The fact
is I dont know which one is faster. But I would think the Filesearch method
is more expensive? Since you're loading an Office object (Filesearch object)
up front (i.e. DIM fs as Filesearch and SET fs = Application.Filesearch)

I have always regarded the Filesearch object as a tool to do sophisticated
file management/manipulation (i.e. listing, filtering, sorting, parsing down
subdirectories, etc). If all you need to do is to check for an existence of
a file (i.e you know the folder name and the file name) which is what the OP
needs, I cant believe there is anything that can beat:


If Len(dir("c:\filename.zip")) > 0 Then
'email the file
Endif


Other reason I would avoid using the Filesearch object just to check for
existence of a file can be found on Google. You would find issues with the
Filesearch object ranging from OS version dependency, stability and others.
The Filesearch object is also tied with the Fast Find that comes with MS
Office. The Fast Find was installed by default in previous MS Office
versions but no longer installed by default in newer versions of MS Office
because nobody likes it.


Immanuel Sibero

I disagree. You method would require a Dir() call for every employee. A
Filesearch is one call. You would still need to tell the Filesearch what
directory to find the files in. Then you would have the Filesearch object
you could loop through looking for the matching employee and sending the
E-mail. I believe the logic would be simpler and the processing more
efficient.

MLamour,

You may not need to do a file search.
Another option is to have a textbox on your form asking the user to specify
which folder contains all the zip files.
Since your table contains EmployeeID and the zip files are named using
EmployeeID. Conceptually, all you need to do is:
- parse through your table
- for each employee record, use Dir() function to check for the existence of
a file called [EmployeeID].zip in the folder specified by the user above.
- if the file exists, send an email and attach the file.
- you may even add a field in your table to indicate if the file is sent or
not, what date it is sent, etc. etc.

This logic would be more efficient than searching for the file for every
employee record.

Immanuel Sibero

Klatuu,

Thanks for your input. I thought the recordset was the easiest way to do
this. If this is not - I'm definitely interested in learning a new way.

Basically, there are approximately 220-300 zip files already created and
stored on a hard drive. The names of these zipped files are named according
to employee Ids, as I stated in the first post. I have a a table that
contains each employye name, email address and their employee id.

I am trying to have MS Access find the zip file on the hard drive and find
the corresponding employee id and email address from the Access table. If
there is a match (the zipped file and employee id from the MS Access table)
then I want to send the zip file automatically to the recepient.

Hope that clarifies things...




:

I am not sure why you would use a recordset for this. If you are

using
a

licenses copy of Winzip, you can actuall shell from Access, create

the
zip

file, and then E-mail the zip file you just created. I did this

about5
yrs

ago. As I recall, the ability to shell to winzip and pass it

parameters
is a

downloadable that allows command line access to Winzip. If you are creating
the zip file outside Access, why not just E-mail it manually?

:

Hi,

I'm seeking help with the following scenario.
I have a table that contains employeeid, and employee names and email
addresses.
I have zipped files located on a hard drive that containts the employee Id
as the name.
For example: Employye Id: 12345 Employee Name: John Smith
Zip FIle Name: 12345.zip

How do I use a recordset to search for the the zip file on the

hard
drive

and then and email the specific zip file to each employee?

Any light that you can shed on this is appreciated.

 

[Guest]

Immanuel,

Thanks for the reply. I enjoy rational discussion and different points of
view. I will agree that loading the object is more overhead and that the
FileSearch object is for more complex tasks than a Dir() can accomplish;
however, if you read her second post, she has 220 - 300 files to process. If
that were not the case, then your Dir() solution would be the best.

Klatuu,

As far as which one is faster, I guess it would be comparing ONE dir() call
against ONE loop of the Filesearch object. I havent really tested it, so I
might have been incorrect claiming that the dir() method is faster. The fact
is I dont know which one is faster. But I would think the Filesearch method
is more expensive? Since you're loading an Office object (Filesearch object)
up front (i.e. DIM fs as Filesearch and SET fs = Application.Filesearch)

I have always regarded the Filesearch object as a tool to do sophisticated
file management/manipulation (i.e. listing, filtering, sorting, parsing down
subdirectories, etc). If all you need to do is to check for an existence of
a file (i.e you know the folder name and the file name) which is what the OP
needs, I cant believe there is anything that can beat:


If Len(dir("c:\filename.zip")) > 0 Then
'email the file
Endif


Other reason I would avoid using the Filesearch object just to check for
existence of a file can be found on Google. You would find issues with the
Filesearch object ranging from OS version dependency, stability and others.
The Filesearch object is also tied with the Fast Find that comes with MS
Office. The Fast Find was installed by default in previous MS Office
versions but no longer installed by default in newer versions of MS Office
because nobody likes it.


Immanuel Sibero

I disagree. You method would require a Dir() call for every employee. A
Filesearch is one call. You would still need to tell the Filesearch what
directory to find the files in. Then you would have the Filesearch object
you could loop through looking for the matching employee and sending the
E-mail. I believe the logic would be simpler and the processing more
efficient.

MLamour,

You may not need to do a file search.
Another option is to have a textbox on your form asking the user to specify
which folder contains all the zip files.
Since your table contains EmployeeID and the zip files are named using
EmployeeID. Conceptually, all you need to do is:
- parse through your table
- for each employee record, use Dir() function to check for the existence of
a file called [EmployeeID].zip in the folder specified by the user above.
- if the file exists, send an email and attach the file.
- you may even add a field in your table to indicate if the file is sent or
not, what date it is sent, etc. etc.

This logic would be more efficient than searching for the file for every
employee record.

Immanuel Sibero




Klatuu,

Thanks for your input. I thought the recordset was the easiest way to do
this. If this is not - I'm definitely interested in learning a new way.

Basically, there are approximately 220-300 zip files already created and
stored on a hard drive. The names of these zipped files are named
according
to employee Ids, as I stated in the first post. I have a a table that
contains each employye name, email address and their employee id.

I am trying to have MS Access find the zip file on the hard drive and find
the corresponding employee id and email address from the Access table. If
there is a match (the zipped file and employee id from the MS Access
table)
then I want to send the zip file automatically to the recepient.

Hope that clarifies things...




:

I am not sure why you would use a recordset for this. If you are using
a
licenses copy of Winzip, you can actuall shell from Access, create the
zip
file, and then E-mail the zip file you just created. I did this about5
yrs
ago. As I recall, the ability to shell to winzip and pass it parameters
is a
downloadable that allows command line access to Winzip. If you are
creating
the zip file outside Access, why not just E-mail it manually?

:

Hi,

I'm seeking help with the following scenario.
I have a table that contains employeeid, and employee names and email
addresses.
I have zipped files located on a hard drive that containts the
employee Id
as the name.
For example: Employye Id: 12345 Employee Name: John Smith
Zip FIle Name: 12345.zip

How do I use a recordset to search for the the zip file on the hard
drive
and then and email the specific zip file to each employee?

Any light that you can shed on this is appreciated.