Sending Automatic EMail Reminders from Microsoft Access 2007

[pclutts]

I have a table (tblTasks) with a number of fields in it, some of which are “AssignedTo” and “DueDate”. AssignedTo is a LookUp field that comes from the table tblPersonnelList.LastName. I need to add a feature in my database that will automatically send email alerts to the “AssignedTo” person on the date the task is due (i.e. “DueDate”). Can anyone advise me on how to do this? An example of some code would be a great help or maybe point me to another resource on the Internet. Thanks in advance for any help

 

[Lightwave]

PC

The following code should do pretty much what you want. I use it presently in a Access 2003 application but it should work if placed behind the click event of a command button. (subsequent to correct alterations)

Run down ;

Firstly you create a DAO.Recordset

Then you tell it what records to select using the embedded SQL and the
location of the database (front end)

Then you create a loop collect up all the e-mails and assign that to a string.

Finally you allocate that string into an e-mail using the DoCmd.Send Object.

Your database will be different but the principle is the same. You will also need to create a query that selects the specific e-mails you are interested in.

Probably by setting a query filtering on DueDate = Today.

Remember if you are struggling with the SQL string to embed create in design builder and then press the SQL view to get the actual text rather than the non transferrable Graphical View.

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

Dim sSubject As String
Dim sMessageBody As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Set MyDB = OpenDatabase("C:\Program files\BlueCoyote\Projects" & "\ProjectManagementApp_fe2003.mdb")
Set rsEmail = MyDB.OpenRecordset("SELECT T013ProjectInvolvement.Id, T013ProjectInvolvement.ProjectID, T013ProjectInvolvement.PersonID, " & _
"T013ProjectInvolvement.Capacity, Q002NamesAZ.[e-mail address 1] " & _
"FROM T013ProjectInvolvement LEFT JOIN Q002NamesAZ ON T013ProjectInvolvement.PersonID = Q002NamesAZ.ID " & _
"WHERE (((T013ProjectInvolvement.ProjectID)= " & selectedID & "))", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![e-mail address 1]) = False Then
sToName = sToName & rsEmail![e-mail address 1] & ";"
sSubject = " "
sMessageBody = " "
End If
.MoveNext
Loop
End With

DoCmd.SendObject , , , strEaddress, , sToName, sSubject, sMessageBody, True

Set MyDB = Nothing
Set rsEmail = Nothing

Exit_Command22_Click:
    Exit Sub

Err_Command22_Click:
    MsgBox "Please note records without e-mails were not included", , "PCs Database"
    Resume Exit_Command22_Click
    
End Sub

 

[Lightwave]

I should add that this produces one e-mail that is sent to all people selected in the query. The Docmd.SendObject has the option that you can alter what field Outlook places the list of recipients - I would suggest you probably want to put them all in BCC that way none of them see who else got the same e-mail.