Auto email overdue notice.

[rgreene]

Hi all I've been searching and reading all morning on how to send an email through access. All the examples I've seen seem to do a lot more than I need (or atleast more than I think I need). I could use a little help getting started. Here is my scenario

I'm working on a "Library" database. I have a very simple for that has a little info about the book, who checked it out, When it was checked out, and a due date. What I want is

if me.ddate > date() then
send an email to the checked out person (tblemployee.email) that says the book (insert title here) you checked out is past due. Please return at your earliest convenience.

Also,
I'd like to scan in the cover of each book and insert it into the record. I've been reading that Access doesn't do pictures very well. Any suggestions on how to accomplish this?

Thanks everyone,
Rick

 

[charityg]

The email issue is probably something you can handle on the database startup. To get you started, create a query that will select all books that are overdue called qryOverdue.

Then you will write a bit of code that will loop through the records selected by the query and email a notice.

What version of Access are you using?

 

[rgreene]

Any help with what the code should be? I can do a little more then spell Visual Basic but that's about it.

 

[charityg]

I was waiting for you to let me know what version of Access you are using.

You'll need to create a recordset to loop through when sending the emails, and how you create the recordset depends on your version of Access. If you have Access97, I can whip up the code in about a minute, but if you have 2K or later, I'll have to remember how to create the recordset and I'll have to look it up.

I'll put together examples in both versions post them.

 

[charityg]

Here's the Access97 version.

 

[charityg]

Here's Access2K version.
Not a big difference, but you need to reference the Microsoft DAO 3.6 Library.

Let me know if you have any questions.

 

[rgreene]

Thanks charityg, I got it I'm sorry about the confusion (I was never notified that there was another reply). I'm running 97. I got the zips dowloaded I'll take a look at them.
Thanks again!!!!!!

 

[jereece]

I have looked at this and believe I can use this for my task program. I will try to modify the data and tables to fit my need, but I was wondering if the code is still applicable for Access 2002. The last example was written for Access 2000.

Thanks if someone can check on this.

Jim

 

[jereece]

I have looked at the 2000 version, but don't see how it works. Can you give a little more details. I entered myself and email, then ran the query, but no email notice. Please help.

Thanks,
Jim

 

[rgreene]

I ended up doing something different.

 

[charityg]

This is how it works.

qryOverdue is a query that finds all overdue books and links the email address of the person who checked out the book.

Sub Main in Module1 calls SendOverdueEmail which creates a recordset from the results of qryOverdue, then loops through the recordset sending an email for each book to each email.

To test it, enter your email address in the Employee table for each record. Close the db, then reopen it. When you open the db, it should run Sub Main on startup and send the emails.

Hope this helps!

 

[jereece]

I entered my email address into the Employee table, closed and reopened the database. Nothing happens. I have tried this several times. No emails.

 

[charityg]

When you open qryOverdue. Are there records? If so, what are they?

 

[SAK]

And let there be light

Finally. Thanks charityg, for the code: the 2K version module was something I needed to see. Accessing a recordset via code has been on my list of things to bone up on and voila, here we go. The proverbial lightbulb went on, and I see all sorts of uses for this in my apps.

Thanks

SAK

 

[jereece]

When I run the qryOverdue, there are 2 records showing overdue. Both have my email address. Nothing happens when I close and reopen.

Any suggestions? Does it matter that I am opening the 2000 version using Access 2002?

Jim