Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2016
    Posts
    4

    Question 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
    rs.MoveFirst
    Do Until rs.EOF
    If IsNull(rs!EmailAddress) Then
    rs.MoveNext
    Else
    If oOutLook Is Nothing Then
    Set oOutLook = New Outlook.Application
    End If
    Set oEmailAddressItem = oOutLook.CreateItem(olMailItem)
    With oEmailAddressItem

    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!"
    .Display
    '.Send
    ' rs.Edit
    ' rs!DateEmailSent = Date
    ' rs.Update
    End With
    Set oEmailAddressItem = Nothing
    Set oOutLook = Nothing
    rs.MoveNext
    End If
    Loop
    Else
    'do nothing
    End If
    rs.Close
    Exit_Function:
    Exit Function
    End Function


    This is the task/reminder code from an article.

    Function AddOutLookTask()
    Dim appOutLook As Outlook.Application
    Dim taskOutLook As Outlook.TaskItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set taskOutLook = appOutLook.CreateItem(olTaskItem)
    With taskOutLook
    .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"
    .Save
    End With
    End Function

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    Jul 2016
    Posts
    4
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •