Setting up automatic e-mails 2

[chopin67]

I am setting up a project database in which people are responsible for having parts of a project done by a certain deadline. How can I set up my database so that Access will automatically send an e-mail reminder to the appropriate person two days before there project is due? Please keep in mind that I'm new to VBA code.

 

[ajdesalvo]

In your table you have the Project Due date (right?) and the Responsible Person's emaill address.

Then you need another field called Reminder Sent (either Yes/No, or Date if you want to know hwen the reminder was sent).

Then on the startup of the database (either on the load of the login or menu form), run a functio that checks the Due Date and sends emails to the appropriate person for each project.

Make sure that the Microsoft Outlook 9.0 Object Library is referenced in your database. In the code window, select Tools, References, scroll to locate, then check it.

So in a module, create a function called CheckDueDate:

Function CheckDueDate()

Dim dbs as Database
Dim rst as RecordSet
Dim SqlStr as String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)
set dbs = Currentdb

'see if any projects are due soon, and that reminders have
'not been sent
SqlStr = "Select * FROM [Projects] WHERE (Date() - [Project Due Date] > 2 AND [Reminder Sent] Is Null;"

Set rst = dbs.OpenRecordset(SqlStr)

'if no projects then exit function
If rst.eof = True then
Exit Function
End If

Do While rst.eof = False
With MailOutLook
.To = "someone@yourcompany.com"
.Subject = "Project Due Reminder"
.HTMLBody = "Project " & rst![Project Name] & " is due on " & rst![Project Due Date] & "."
.Send
End With
rst.Edit
rst![Reminder Sent] = Date()
rst.Update
rst.MoveNext
Loop

End Function

Then in the OnLoad event of the login or menu form:

Call CheckDueDate

Happy coding! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[ajdesalvo]

Sorry, one thing you need to change in my code, and that is for the person being sent the mail:

.To = "someone@yourcompany.com"

should be:

.To = rst![Responsible Person email]


Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[chopin67]

Thank you for your help! I have another question. We do not use Microsoft Outlook 9.0 for our e-mail we use GroupWise (Novell). Under references would I then check "GroupWise C3PO Manager Type Library" instead? If so, how will this affect the rest of the code?

 

[ajdesalvo]

The code would have to be changed if were not using Outlook/Exchange as the mail server.

However, if you use SMTP, and Microsoft's Winsock control, you don't need Outlook.

I have described how to do this in an earlier post, thread705-459000.

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"