Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    13

    Unanswered: creating a trigger email

    i want to set a condition where an email can be triggered after a condition has met, lets say after 300 days of the Approval date an email should be sent to the user that his approval is going to expire. Im totally new to MS Access and not familiar to coding also. How can I do this? will it require coding?

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I've done this a few times. Without a little experience it may not be so straightforward but I'll break it down as much as I can.

    First, I suggest that you add an additional field into your table next to the approval date, called 'Reminder date'. You'll want this, to identify those that you've already chased otherwise you could end up chasing the same person more than once.

    You then need to create a query that searches for all the records where "approval date" < now() + 300 AND where "Reminder date" is blank.

    Next, create a form with a table which displays the results of the query. The result of this query will be everyone that you need to send the reminder to.

    Add a button onto the form to send the email with a bit of code in which sends the email to all the relevant managers. Unfortunately this bit will be the tricky bit for you but I can help with more information.

    Once you've sent the email, you would need to update the "Reminder date" with todays date.

    You can effectively manage the above 2 at once with some looping.

    Ok. So if you want more help, either upload your database (wipe out the data), or tell me the table names, and the names of all the fields in the table.
    Last edited by christyxo; 06-17-11 at 10:23.

  3. #3
    Join Date
    Jun 2011
    Posts
    13

    Emailed

    Hi Christy, i have emailed you the specific details.
    thankyou

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Code:
    Private Sub Cmd_Reminder_Click()
    
    Dim MyDimListIndex
    Dim MyDimSubject
    
    Dim MyDimMessage
    Dim MyDimSignature
    Dim SQLDelEvaluation
    
    MyDimListIndex = 1
    
    MyDimSubject = "ERC Reminder"
    
    Do Until Me.List01.ListCount = 1
    
        Me.List01 = Me.List01.ItemData(1)
    
    '
    ' This is the email that you want to send.
    
        MyDimMessage = vbCrLf
    
        MyDimMessage = MyDimMessage & "Dear " & Me.List01.Column(9) & ", " _
        & vbCrLf _
        & vbCrLf
    
        MyDimMessage = MyDimMessage & "The approval status of your study titled;" _
        & vbCrLf _
        & vbCrLf _
        & Me.List01.Column(3) _
        & vbCrLf _
        & vbCrLf _
        & "...is approaching it's expiration date." _
        & vbCrLf _
        & vbCrLf
    
        MyDimMessage = MyDimMessage & "Put the rest of your message here..." _
        & vbCrLf _
        & vbCrLf _
    
    '
    ' This will be the signature displayed at the end of your email
    '
    
        MyDimSignature = "Yours Sincerly," _
        & vbCrLf _
        & vbCrLf _
        & "Your Name Here" _
        & vbCrLf _
        & "Your Contact Number Here"
    
    '
    ' Send the message
    ' Change the final arguement to FALSE if you want to send the emails automatically, without checking them
    '
    
        DoCmd.SendObject , , , Me.List01.Column(10), , , MyDimSubject, MyDimMessage & MyDimSignature, True
    
        MyDimListIndex = MyDimListIndex + 1
    
    ' Apend Table
    
    DoCmd.SetWarnings False
    DoCmd****nSQL "UPDATE Project SET Project.ReminderDate = Now() WHERE (((Project.ProjectId)=" & Me.List01.Column(0) & "));"
    DoCmd.SetWarnings True
    
    Me.List01.Requery
    
    Loop
    
    End Sub
    Last edited by christyxo; 06-21-11 at 11:44.

Posting Permissions

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