Sending an E-mail from Access

  • Thread starter Thread starter Bent Lauridsen
  • Start date Start date
B

Bent Lauridsen

Hi All

I hope that there might be someone that can help me.

I need to make my access application send a copy of the database file
automatical once a month, and I would like to make this a part of the
closing part.

The way I have thought to have it done is, when the user click on the "close
system" button, then the system should look at the system clock and if this
says that is is the ex. 1 of the month the it should start winzip and pack
sown the application and then send the packed file in a E-mail to a spec
mail account, and all this should be done without the user hae to do
anything.

Is this possible

Yours

Bent Lauridsen / Bamsefar
mailto:bl_ at post5 dot tele dot dk
 
Dear Arvin

Do you have an exsample on how to do this either in a VB or in Access,
windows Scheduler I don´t know

Yours

Bent Lauridsen / Bamsefar
mailto:bl_ at post5 dot tele dot dk
 
Bent said:
Hi All

I hope that there might be someone that can help me.

I need to make my access application send a copy of the database file
automatical once a month, and I would like to make this a part of the
closing part.

The way I have thought to have it done is, when the user click on the
"close system" button, then the system should look at the system
clock and if this says that is is the ex. 1 of the month the it
should start winzip and pack sown the application and then send the
packed file in a E-mail to a spec mail account, and all this should
be done without the user hae to do anything.

Is this possible

Yours

Bent Lauridsen / Bamsefar
mailto:bl_ at post5 dot tele dot dk
Click to expand...

Most all serious Access apps are "split". One file with just tables and one
file with everything else. There are many advantages to this approach and I
won't go into all of them here, but you have just pointed out one of them.

It is never a good idea to attempt to copy or otherwise manipulate an Access
file while it is open. However; with a split Access app all you have to do is
close all bound forms and RecordSets and then the file containing the tables is
effectively "closed" while the front end or application file is still open.
This would allow you to run code (safely) in the front end that would create an
Email with the data file as an attachment. After all, it is the data that you
want to send somewhere right?

You will need to automate an external Email client like Outlook or CDO as Access
has no native methods for sending an Email that has an external file as an
attachment. You will also need a program for zipping that can be automated or
at least shelled from Access to first create the zip file of your back end.

So you really have several distinct operations that you need to figure out to do
this. Determining that it is the first of the month is easy enough...

If Day(Date()) = 1 Then
' It's the first of the month
' Run code to close all forms
' Run code to create zip file
' Run code to send Email
End If

....but what if the user closes the app more than once on the first of the month?
Do you want to send the Email every time? If not, then you really need a table
to store the most recent date that the Email was sent so that you can ignore the
current day if the Email has already occurred.

If Day(Date()) = 1 _
And DLookup("LastEmailDate", "LastEmailTable") < Date() Then
' Run code to close all forms
' Run code to create zip file
' Run code to send Email
' Run code to update date into LastEMailTable
End If

I would work on this basic logic first and the make separate postings concering
any other steps you have problems implementing. Googling should find examples
of all of them.
 
Hi Rick

thanks for the Idear of lastEmailDate

At the Time where the user is on the form where he can click on the close
app buttom all other forms should be closed, but is there a way to make a
check on this, so all other forms is closed exept the main menu form, to
which no tabels is att.

and to make the zipfile I have made this line and it function.
Call Shell("c:\programmer\winzip\winzip32.exe -a Demo.zip c:\demo.mdb",
1)

so now I just miss the function to make it be send as a mail att.

yours

Bent Lauridsen / Bamsefar
mailto:bl_ at post5 dot tele dot dk
 
Bent said:
Hi Rick

thanks for the Idear of lastEmailDate

At the Time where the user is on the form where he can click on the
close app buttom all other forms should be closed, but is there a way
to make a check on this, so all other forms is closed exept the main
menu form, to which no tabels is att.
Click to expand...

The most reliable thing to do is to look for the existence on a locking file
(LDB extenstion) in the folder where the back end file resides. If anything is
still connected to the back end then an LDB file will be found. If no LDB file
exists then you are safe to zip the file.

If you find an LDB file figuring out what is still connected might not be so
easy. You can loop through the forms and reports collections backwards closing
all forms and reports you find except for the form running the closing code
(assuming that this form is not bound to anything). As long as you don't leave
any code-created Recordsets open then closing all forms and reports should be
sufficient to release all connections to the data file.
 
Hi Rick
How du I check if the LDB file exist.

and how du i from access mail the ZIP file

Yours

Bent Lauridsen /Bamsefar
 
To check whether a file exists, use

If Len(Dir(<full path to file>)) = 0 Then
' File doesn't exist
End If

Assuming that the name of the MDB file of interest to you is stored in a
variable named strDatabase, you'd use:

If Len(Dir(Left(strDatabase, Len(strDatebase) - 4) & ".ldb")) = 0 Then
' LDB File doesn't exist
End If
 
Dear Douglas

Thanks for the answer.

Do you also knoew how to send the file directly from the projekt to outlook

Yours

Bent Lauridsen /Bamsefar
 
Hi Rick

Thanks for the link, but I can´t get it to work in Office 2003

Yours

Bent Lauridsen / Bamsefar
 
What error are you running into? Did you remember to add the reference to
Microsoft CDO 1.21 like the page said to?
 
Hi Douglas

Yes I had add the regerence, I have also found the error, I cant add a
ZipFile, that is not located in the root of C:\, when I add a file that is
located in a n other directory the proc don´t add the file, but it don´t
come with any error either.

But I can remember that I also at a earlier time have seen a command that
just was 1 line in with you had how shoud have the mail, the filename,
subject and at last a note. as far as a remember it should be a built in
function, but I don´t remember the name of this.


Yours

Bent Lauridsen / Bamsefar
 
Hi

I now have got it to function, I just had not seen that I had to put in the
stFile:= in front of the filename.
But there is 1 thing that I can´t understand, the name of the att file is
correct when I send it, but when the resiver get it the name is
ATT*****.dat, why this conversion???

Can I do anything so that the filename is not converted.

Youts Bent Lauridsen / Bamsefar
 
Although you have had a lot of help on this I don't quite understand what
you are planning to solve.

a) Update other people with an upgraded db (fixes, new stuff).
b) Update your db with data from other people.
c) both (a), (b) or neither/both.

Each choice will affect the way to carry out your task.

Regards John
 
Hi John

What I want with this function is to solve a little problem with remembering
to send a copy of the database between 2 users, until I have got the
webbased version of the system made finish, and to send a sicurity copy of
the system to the backup mail.
In the way I will always have a new copy of the system, in case there might
happend something to the person that daily work with the system, and I can
take over the maintrence of the system until an other person have been
elected to maintain the system.

That is why I need to send a backup of the system.

I hope that this answer your quistion.

Yours

Bent Lauridsen / Bamsefar
 
Still not following your needs.

"send a copy of the database between 2 users"

Seems like a request to mutually update 2 sites ie 2 people changing
creating forms/tables... at the same time and passing the results back and
forth.

Regards John

To keep changes without loss, something more frequent than once a month
should be required, personally I use version numbers and create backup
copies *BEFORE* starting on a new round of changes and when finishing off
(ie after testing correct) update the version number and create a new
backup.
 
Back
Top