You are here:

Using MS Access/Auto Email due dates and calculate extensions

Advertisement


Question
QUESTION: Good day!

I am using access 2010 and would like to have the database send an e-mail a report to our branch chief when an item has passed its due date. We are on a network and use outlook (no way around that here). How would I go about doing this?

I also have another item that I hope its easy. I have a field that generates a due date based off a simple equation. =[DateDue]+90. However in some situations extensions to time may be granted (15, 30, 45 and 60) I have check boxes now to indicate that an extension had been granted, but how would I get that check box to automatically add that time value to the date due field?

I would greatly appreciate your help with this!

ANSWER: First, Sending an email from an Access database is fairly easy, especially with Access 2010. Using the SendObject command, you can easily attach a PDF report to an e-mail.
The problem is triggering the e-mail. You have to open the database and have a query that returns past due items. Then test the query to ddetermine if it returns any records. If it does you trigger the SendObject.

If your app is sure to be opened on a daily basis, then you can trigger this when the app is opened. If not, you have to do something to make sure its opened.

I would not use checkboxes for this. I would use a combobox or an options group. Set the list control so it returns the number of days to extend. Then use something like the following expression:

=DateAdd("d",90+Me.optExtension,[DateDue])

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA




---------- FOLLOW-UP ----------

QUESTION: Thank you for your help, both of those suggestions worked very well. (didn't realize it was that simple ;-)

I did have one follow up question however. Now that I have the proper dates, and can send the report via email with just a click of a button, how would I get that button to also tell the database to mark a field showing the email notification was sent?

I have tried with if then statements, but I have to be honest I am not very proficient with automation, macro and codes yet.

Once again I appreciate your help!

ANSWER: Are you using a macro or VBA code? How you do this depends on several factors. When you click the button to send the e-mail what is the code behind that button? Are you sending a single e-mail or looping through a set of records?

In the former, you can just set a control on your form to Yes. In the latter you would run an Update query for all the records processed. Or, if you are using a loop, Update the field after you run the SendObject.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for the quick reply

Its a single e-mail (Macro), I am just calling the "emailaccessobject" macro and attaching a report. I don't understand enough code, or even all the macros at this stage yet to do much more than this.

I understand what your stating about setting the field to "true" but not sure how/where to accomplish this. I have looked in the macro options but honestly I am not certain what to look for.

A few more details:

The field name I need to update is
Database: [Event_Data_Table]
Field (Y/N): fac_notification_sent

I could also add a date field for better tracking if you think that would be a wise feature.

I am sorry I feel as if I am asking absurd and very elementary questions

Again, your help is beyond appreciated!

Answer
Not a problem at all.

I would spend more time learning VBA than macros. They are much more powerful and flexible.

But what I would do, in your case, is add a control to your form bound to the fac_notification_sent field. Then add an action to your macro:

SetProperty
Control Name: Forms!formname!fac_notification_set
Property: Value
Value: -1

So this will set the value of the control to True for the current record.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.