I have a database that I would like to generate two automated emails on certain conditions. I have absolutely no idea how to go about that so here is a little background info.

The database is used to track standards that have tentative expiration dates that can be extended by the manufacturer. When an expiration date is within 30 days I would like two emails to be generated. The first will always come to me, the second will go to the manufacturer which varies by standard (therefore the email address will change).

So far I have set up a query that will find the standards that are within the 30 day window. I would like the query to run daily (automatically if possible) and then send the emails to be generated everytime a new standard populates the list. I also need to ensure that an email is only sent once and despite the fact that a standard will remain in the query at least until the manufacturer responds.

I think it will be necessary to find a way to mark whether or not an email has been sent for each standard in the list.

Any help will be extremely appreciated!!!

Let me know if anymore info is needed

Hi,

I had the same requirement and I use Automailer XL

http://www.duodata.de/amlxl/

It has a "send" folder the contents of which are checked for a file on startup. If there is a file, it is sent to the recipients in the Rcpts.csv file.

I export my data to reports and recipient details to Rcpts.csv files and then use VBA to copy the report files in turn to the "send" folder and recipient details to the Automailer XL folder and then start up the Automailer. Automailer auto-closes after 4 seconds. After send, I have a batch job that clears out the copied files and then it just repeats.

I hope this helps

Pavesa

Thanks for the reply.

That would probably work if I were doing it at home but it won't at work. Our IT department will not allow us to install ANYTHING! no matter how much easier it will make our job. As a result I am limited to Access and Outlook to accomplish this task.

Thanks again

OK I use the first code to time an event this may be an e-mail or report.
The second piece of code is for sending an e-mail automatically.
On Error GoTo ErrorHandler
    Dim dtrunreport As Date
    Dim dtCurTime As Date

    dtrunreport = Format(#12:06:00 PM#, "hh:mm") Or Format(#12:07:00 PM#, "hh:mm")
    dtCurTime = Format(Time(), "hh:mm")

    If dtCurTime = dtrunreport Then
       DoCmd.SendObject acSendReport, "No Assignment Report", acFormatHTML, "Brendan.Bartley@anpost.ie", "", "", "NO Assignment Report", "Suppliers with NO ASSIGNMENT", True
ErrorHandlerExit:

   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

    End If

End Sub




Private Sub cmdsend_Click()

On Error GoTo ErrorHandler

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim outfile As String
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
    With objmail
        .To = ""
        .CC = ""
        .Subject = ""
        .Body = ""

        .NoAging = True

        .Display
    End With


   DoCmd.Close
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
   End If
End Sub

Brendan Bartley