October 12, 2008 08:40pm pdt

My Account

Top 15 Experts

Overall Top Experts

1LSMCons... 251,222
2capricorn1 181,303
3boag2000 174,692
4DatabaseMX 173,275
5peter57r 128,969
6harfang 72,470
7jimhorn 70,076
8matthews... 69,331
9jefftwilley 60,468
10GRayL 41,359
11JDettman 35,587
12frankytee 33,442
13MikeToole 30,650
14dqmq 27,400
15jmoss111 24,841

	[x] Attachment Details

Automatic Email Distribution - MS Access

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.7

Zones: Access Architecture/Design, Outlook Groupware Software, Access Coding/Macros

Tags: Microsoft, Access, 2007

I have an MS Access program that monitors bids/quotes. Each bid has a bid manager and a due date associated with that bid. I run a query to determine if any bid are due within the next day. I currently have the attached code behind a command button for testing purposes and it successfully emails the bid to the manager's stored email address. However, my goal is to have the emails sent every morning at a set time, but only if the query returns any upcoming due bids. Through reading various post and articles, I believe the following is the best method to accomplish my goals, but I am not quite sure how do execute the plan...

1. Install a copy of the front-end(including email modules/macros) on central server
2. Open the Access application via Windows Task Scheduler on the server
3. Execute a macro upon the opening of the Access app. that sends the emails
- Run query to determine if any bids are due soon
- Send SEPERATE emails for each bid to the associated bid manager
4. Close the app after a set amount of time

**Repeat Process Every Day

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

           
           
             Private Sub Command47_Click()
     Dim BidsRs As DAO.Recordset, MgrRs As DAO.Recordset
    Dim olApp As Object, olMsg As Object
 
    Set olApp = CreateObject("Outlook.Application")
 
    Set BidsRs = CurrentDb.OpenRecordset("qryBidDueSoon")
    Do Until BidsRs.EOF
        Set MgrRs = CurrentDb.OpenRecordset("SELECT * FROM tblEmployee " & _
            "WHERE EmployeeID = " & BidsRs!EmployeeID)
        Set olMsg = olApp.CreateItem(0)
        With olMsg
            .Subject = "Bid Manager Alert: Bid# " & Format(BidsRs!BidNumber, "0000")
            .To = MgrRs!EmployeeEmail
            .Body = MgrRs!EmployeeName & "," & vbCrLf & vbCrLf & _
                "This is a reminder that Bid# " & Format(BidsRs!BidNumber, "0000") & " must be submitted to " & _
                BidsRs!ClientName & " by the close of business on " & BidsRs!BidDueDate & "."
            .Send
            MgrRs.Close
        End With
        BidsRs.MoveNext
    Loop
 
    BidsRs.Close
 
    Set BidsRs = Nothing
    Set MgrRs = Nothing
    Set olMsg = Nothing
    Set olApp = Nothing
 
    MsgBox "Done"
End Sub

           
         

	#	Title
	1	Editing Goal Seek in Excel
	2	Using Goal Seek Function in Access
	3	Reproducing the Goal-Seek function t…
	4	Need a script to query a couple of distr…
	5	Centralize audio, multiple distributions
	6	Can Goal Seek accomplish this, or is t…