email based on data in access

[optjco]

Is it possible to send an e-mail based on criteria from an access table.

i.e. look at a table and if there are new items added send an e-mail to the relevant people

 

[PeasNCarrots]

Anything is possible with programming..

First I need a bit more info.

Do you have a way of knowing whether new items were added to the table i.e. maining a log. Also, how is the information being added to the table (via form, imported, copy/paste, etc.)

Are you going to store the list of e-mails in a table? (Which I recommend).

 

[optjco]

PeasNCarrots,
I have an access DB table where the data is stored. The data is inputted via a form from a web page on our intranet,What I would like is that perhaps every morning we can run a query to see if there are any new items added from the previous day and if so automatically e-mail a group of people so that they can check to see if any of the new items relate to them. As regards the list of e-mails i had not thought about that at the moment but YES a table sounds like a good idea.

Any help would be much appreciated

Regards

Olly

 

[PeasNCarrots]

Simply add a field to your table say called DateAdded

Then modify your query on the website to append the current date/time to this field. Now all you have to do is query the DateAdded field for yesterday's date.

 

[optjco]

I have a field already called "EnteredDate" which I can use. the reason I posted the question was that I am unsure how to code it so that the e-mail is sent automatically from access, but only if there is new data added. Also i need to be able to ignore the weekend days when running the query i.e. if a new item was added on a Friday I would want the query on Monday to pick that up and ignore Saturday & Sunday

regards

Olly

 

[optjco]

OK, I have sorted this now with the following SQL statement

SELECT tblTaskData.RecordID, tblTaskData.EntryDate, Format([EntryDate],"dddd") AS [Day], tblTaskData.Function, tblTaskData.Customer, tblTaskData.CompletedFlag
FROM tblTaskData
WHERE (((tblTaskData.EntryDate)=IIf(Format(Date(),"dddd")=("Monday") And [CompletedFlag]="No",Date()-3,Date()-1)))
ORDER BY tblTaskData.EntryDate;

What I would like to do now is if the query is not empty then send an e-mail to several users but if the query is empty then do not send.

Could anyone help me on my way with this please

Regards

Olly

 

[ptpaton]

Olly,

Here's what I would do. Create a yes/no field in your table, called DataSent. When you send the data via email (which we'll get to in a second) run an update query and tick each DataSent field to yes. This creates a record of what's "new" and what's "old".

Now, to send data in email, here's some code:

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
'Additional email properties can be added. For help, type "objEmail." A list of all properties and methods will populate, allowing you to choose which you need.

With objEmail
.To = "Your receipent list"
.Subject = "Enter your subject"
.Body = "Generic text to go into the body of your email"
.Display
'".Display" displays the email without sending. To send directly, use ".Send"

End With

Use VBA before the email to run your queries. YOu can use DoCmd.OpenQuery to run whatever queries you need to.

You can get fancy and bring fields in from open forms into the body and subject of your email, but let's start with this. Let me know if you need more help.


-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[optjco]

ptpaton,

Thanks for the info supplied but i think i may have been misleading you. Here is what i want to

Run a query at a set time every day i was thinking of doing this using task scheduler and if there are new records then send an e-mail to display the following in the body.

"New items have been added please go to the web page to view"

Otherwise i do not want to send an e-mail

Regards

Olly