Normally I work in Microsoft Access

[PaulBricker]

Normally I work in Microsoft Access. Our school has Centrinity's First Class for an Email server and automating Access and First Class is a major issue right now. Here's what I would like to do. When a Work Order is closed out, I would like to e-mail the Person Requsting using Outlook to let them know it has been completed. We close out 10's of Work Orders at a time and if I use this code I have to pause between each Work Order long enough for the email to get sent or else it errors out and locks things up.

Dim strValue As String
Dim strName As String
Dim strMessage As String
Dim frm As Form
Set frm = Forms![Work Order]
strMessage = frm.[FirstName] & " the Work Request you submitted " & vbCr & "'" & frm![Nature of Work to be Performed] & "'" & vbCr & "has been completed on " & frm.[Date Work Completed] & vbCr & "by " & frm![Work Assigned To].Column(2) & " " & frm![Work Assigned To].Column(1) & vbCr & "let me know if you have any question" & vbCr & "Paul"

Set myOLApp = CreateObject("Outlook.Application")
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailItem)
    myOLItem.To = txtTO
    myOLItem.Body = strMessage
    myOLItem.Send
'Dim timPause As Single
'timPause = 15
'Call fnMomentaryPause(timPause)
    myOLApp.Quit

So I added a little stop watch to it (the commented out section) that pauses the code 15 seconds while the email gets sent but, this isn't all that effective either. If the email doesn't go out in 15 seconds and it gets the the .Quit line it locks things up.
What I want to try and do now is create all the emails as the Work Orders are being closed and then send them out as a group in the middle of the night or something like that. Is it possible to create emails using VBA and have them sit in the Outbox (or move them to the Outbox) until I give it the Send command. Where would I start? I'm very familiar with VBA so if someone has an idea I'd be interested in hearing it.

Thanks

Paul

 

[PaulBricker]

For anyone interested, what I have ended up doing is inserting the information from the completed work orders into a seperate Table. Then after hours, I have Scheduler open the database and run a function that loops thru the Table and send out the emails and then clears out the table. Works well for my purposes.

Paul