07-21-16, 10:24 #1Registered User
- Join Date
- Jul 2016
Unanswered: Automatic email reminder from Access to Outlook
Hi, I am a creating a database that will send automatic reminder emails for upcoming task. I have two questions. I have already created an automatic email reminder, but I need to know First Q: how to stop the automatic email once the task is completed or in progress. Second Q: How do you create a automatic email for 3 month reminder, 6 month reminder, or yearly reminders? I've tried to incorporate the add a task/reminder code with the code I have to help me with the 3 month reminder, 6 month reminder, or yearly reminder, but it did not work. I need help.
Function GenerateEmail(MySQL As String)
'On Error GoTo Exit_Function:
Dim oOutLook As Outlook.Application
Dim oEmailAddress As MailItem
Dim MyEmpName As String
Dim MyEquip As String
Dim MyModel As String
Dim MyAsset As String
Dim MySerial As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
Do Until rs.EOF
If IsNull(rs!EmailAddress) Then
If oOutLook Is Nothing Then
Set oOutLook = New Outlook.Application
Set oEmailAddressItem = oOutLook.CreateItem(olMailItem)
MyEmpName = DLookup("EmpName", "Employees", "[EmpID]= " & rs!EmpName)
MyEquip = rs!EquipmentType
MyModel = rs!ModelNo
MyAsset = rs!AssetNo
MySerial = rs!SerialNo
.To = "another@.com;another@.com;another@.com"
.Subject = "Calibration that's due between 1 to 11 months"
.Body = "Calibration ID: " & rs!RecordID & vbCr & _
"Location: " & rs!CalLocation & vbCr & _
"Requirement: " & rs!CalRequirement & vbCr & _
"Employee: " & MyEmpName & vbCr & _
"Name: " & MyEquip & vbCr & _
"Serial No.: " & MySerial & vbCr & _
"Model No.: " & MyModel & vbCr & _
"Asset No.: " & MyAsset & vbCr & _
"Due Date : " & rs!CalUpcomingDate & vbCr & vbCr & _
"This email is auto generated. Please Do Not Replay!"
'MyEmpName = DLookup("EmpName", "Employees", "[EmpID]= " & rs!EmpName)
'.To = rs!EmailAddress
'.Subject = "Task due in between 1st and 11th month reminder for " & MyEmpName
'.Body = "Task ID: " & rs!RecordID & vbCr & _
'"Task Name: " & rs!TaskName & vbCr & _
'"Employees: " & MyEmpName & vbCr & _
' "Task Due: " & rs!CalUpcomingDate & vbCr & vbCr & _
'"This email is auto generated from Task Database. Please Do Not Replay!"
' rs!DateEmailSent = Date
Set oEmailAddressItem = Nothing
Set oOutLook = Nothing
This is the task/reminder code from an article.
Dim appOutLook As Outlook.Application
Dim taskOutLook As Outlook.TaskItem
Set appOutLook = CreateObject("Outlook.Application")
Set taskOutLook = appOutLook.CreateItem(olTaskItem)
.Subject = "This is the subject of my task"
.Body = "This is the body of my task."
.ReminderSet = True
.ReminderTime = DateAdd("n", 2, Now) ' Set to remind us 2
' minutes from now.
.DueDate = DateAdd("n", 5, Now) ' Set the due date to
' 5 minutes from now.
.ReminderPlaySound = True
'add the path to a .wav file on your computer.
.ReminderSoundFile = "C:\Win95\media\ding.wav"
07-21-16, 15:39 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
To do something at a specific interval requires logic to identify the interval and a means of executing that logic at the required interval.
So part 1 is store a means of working out when an event last occured or when an even should next occur. Usually youd be advised to store the date tbe event last occured so if required a change in interval has no impact. Say the requirement changes to monthly from 3 monthly... storing the next date rwquires additional work.
Part 2 is how to execute the process.
Several approaches, the first is to fire up the process from a specific user account when they first logon /open the app say in the morning.
Or set up a batch process everynight / interval using windows scheduler. Essentially you create an access front end that uses an autoexec macro to do a puece if work.
The batch process us a smarter but requires moredevelopment effort. You need to write more disciplined code, code that caters for all anticipated error conditions, reports back what its done, or not so you can work out what to do and monitor what emails have been sent.I'd rather be riding on the Tiger 800 or the Norton
07-21-16, 16:00 #3Registered User
- Join Date
- Jul 2016
This is my first time creating a database and what you just said was a mouth full. I'm not sure on how to do any of that. Can you show me an example please?