August 30, 2008 12:09am pdt

1. LSMCons... 134,175
2. boag2000 133,590
3. capricorn1 113,175
4. DatabaseMX 76,689
5. peter57r 70,551
6. harfang 41,526
7. jimhorn 38,854
8. matthews... 34,872
9. hnasr 18,075
10. GRayL 16,347
11. MikeToole 15,650
12. LennyGray 14,955
13. RgGray3 14,896
14. jefftwilley 14,700
15. frankytee 14,550
16. JDettman 13,352
17. clarkscott 12,710
18. wiswalld 11,200
19. kelvinsparks 10,760
20. Arthur_... 10,550
21. nexusnation 10,400
22. eghtebas 10,000
23. omgang 9,375
24. jmoss111 9,150
25. koutny 8,675

1. LSMCons... 199,290
2. capricorn1 156,183
3. DatabaseMX 152,047
4. boag2000 141,565
5. peter57r 113,005
6. matthews... 63,206
7. jimhorn 62,872
8. harfang 60,506
9. jefftwilley 60,468
10. GRayL 36,779
11. JDettman 33,627
12. frankytee 31,442
13. MikeToole 29,650
14. dqmq 22,560
15. stevbe 21,852
16. kelvinsparks 20,260
17. mbizup 20,200
18. rockiroads 19,250
19. hnasr 18,075
20. puppydog... 17,545
21. LPurvis 16,625
22. LennyGray 14,955
23. RgGray3 14,896
24. Arthur_... 14,650
25. jmoss111 13,750
25. adraughn 13,750

05.13.2008 at 12:52PM PDT, ID: 23399198

	[x] Attachment Details

Automatic Email Distribution - MS Access

Asked by alliedtech in 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

Start Free Trial

         
           
             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

           
         

Keywords: Automatic Email Distribution - MS Acc…

	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…

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_2_20070628

Automatic Email Distribution - MS Access

Asked by alliedtech in Access Architecture/Design, Outlook Groupware Software, Access Coding/Macros

Tags: Microsoft, Access, 2007

About this solution