Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 24th, 2008, 08:06 PM
Registered User
 
Join Date: Apr 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Automated E-mail

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
 
Old April 25th, 2008, 06:27 AM
Authorized User
 
Join Date: Dec 2003
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 25th, 2008, 07:46 AM
Registered User
 
Join Date: Apr 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 25th, 2008, 11:23 AM
Friend of Wrox
 
Join Date: Jun 2005
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Automated email ashik112 Classic ASP Professional 3 March 14th, 2007 11:11 PM
Sending an automated mail pramos.21d ASP.NET 1.0 and 1.1 Professional 1 July 3rd, 2006 09:32 AM
Automated Mail Merge Print jcellens Access 0 June 22nd, 2006 05:10 PM
Automated/ Secure Transfer from 1 to another rit01 SQL Server 2000 6 May 23rd, 2006 06:31 PM
Automated debugging JAVA Java GUI 0 April 14th, 2004 05:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.