Automatic mail to be triggered from Access (1 Viewer)

[gautambangalore]

All,

I need help from experts out here. I have a table in Access with a field End Date with couple of other fields like Company Name and Description associated with the End Date as one record. In other words, one record has fields Company Name, Description and End Date.

I need help as to how could I get Access trigger an email automatically to a set of people let's say a month before the End Date is approaching. If the End Date is 30/4/2015, I would need Access to be triggering an email to the recipients on 30/3/2015.

Your inputs are much appreciated.

Thanks in advance.

 

[TimW]

Hi
There are a couple of threads with email stuff in including one i have posted to: Alternative to SendObject to achieve automation.

Access will not be able to fully automatically do this... someone will need to click a button to run a query to give you the results about the approaching dates.Part of this routine could include adding the appropriate email addresses and creating an email.

I hope this helps

T

 

[gautambangalore]

I had been able to figure out how email gets triggered. However, can somebody help me with this functionality?

I have fields like Name, Enddate in the table. I want the enddate of each record to be compared with the current date and if the difference in dates is <=30, all names are to be recorded maybe in a file and then email triggered either as an attachment or in the body of the email itself.

Thank you

 

[TimW]

Here is more detail on how I get the text into the email. However, I guess your situation will be different and you may need to create a recordset and then loop though it..

 Private Sub cmdEmail_Click()
Rem -------------------------------------------------------------------------------------------------------------------------- _
    *   In this sub (when sending an email)... _
    *   The data validation is done by the email template for the current stage _
    *   Thas is: if the template required the RFC ID number then if RFCID number is null then an error message pops up... _
    *   Currently the email will contain "N/A" but considering not allowing email if value is not found _
    *************************************************************************************************************************
    
    ' string handling dims
    Dim strTemplate As String
    Dim criteria As String
    Dim i As Integer
    Dim arrResult() As String
    Dim fResult As String
    Dim TBL_NAME As String
    Dim FRM_NAME As String
    Dim strCtrl As String
    
    Dim booLastNote As Boolean
    Dim strLastNote As String
    
     
    ' assign constants
    TBL_NAME = "Data"
    FRM_NAME = "Forms!frmMain!"
    
    rResult = vbNullString ' start with nothing
    '   *************************** _
        * Enter constraints for each stage _
        **********************************
'   StageNo         StageName
    '0                  New
    ' more stuff taken out
     
    ' lookup stage template for stage current rfc is at
    criteria = "[StageNo]=" & Me.CurrentStage
    strTemplate = DLookup("StageResult", "tblStageTemplate", criteria)
     arrResult = Split(strTemplate, "|")
    
    ' loop through string array
    For i = LBound(arrResult) To UBound(arrResult)
        ' find if string is also a field name
        If FieldExists(arrResult(i), TBL_NAME) Then
            'find reference to form control
            strCtrl = FRM_NAME & arrResult(i)
            'if value in control is null then operation needs to stop and warning that the required value is missing
            If IsNullOrBlank(Eval(strCtrl)) Then
                MsgBox "There is no value in field " & arrResult(i) & vbCrLf & "This value is required by the email template for this stage of the process", vbOKOnly + vbCritical, "Information missing"
            End If
            ' if ok replace with value in field (NZ is a precaution as a null value should be found out the step before!
            arrResult(i) = Nz(Eval(strCtrl), "N/A")
        
        End If
        ' reconstruct original string
        fResult = fResult & arrResult(i)
        
    Next i
    
     strSubject = Nz(Me.ChangeID, vbNullString)
     strBody = fResult
   ' REM OPen frmEmailNotesConfig - so user can choose which notes to email
   DoCmd.OpenForm "frmEmailNotesConfig", acNormal, , , , , Me.ChangeID
    
 End Sub

Here I have an email template for each stage of the process.
First it is established the stage of the process. Then the template contains some of the field names that I have used in the form. The information on the template is a string. The field names are delimited by the | character. If the field name is recognised (FIELDEXISTS) then the value of this field name is replace in the string. If the value is not recognized then this is assumed to be text and not replaced.
And then, if required I add information from another table, notes...

 Function fAddNote(iNoteID As Long) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim sql As String
    Dim strAns As String
    
   ' On Error GoTo fAddNote_error
'    ' REM - Last note from selected RFC ID
'    sql = " SELECT TOP 1 * From tblNotes " & _
'            "WHERE tblNotes.ChangeID = '" & sRFCID & "' " & _
'            "ORDER BY tblNotes.NoteDate DESC;"
' REM select note details from NoteID
    sql = "SELECT tblNotes.* From tblNotes WHERE tblNotes.NoteID=" & iNoteID & ";"
     Set db = CurrentDb()
    
    Set rs = db.OpenRecordset(sql)
    
'        NoteID
'        ChangeID
'        NoteText
'        NoteDate
'        Action
'        CommentBy
'        NoteArea
'        NoteHeading
     
    With rs
        ' check for empty recordset (count should be 1)
        If Not .EOF And Not .BOF Then
       strAns = " <style type='text/css'> " & _
".tg  {border-collapse:collapse;border-spacing:0;border-color:#aabcfe;}" & _
".tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aabcfe;color:#669;background-color:#e8edff;} " & _
".tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aabcfe;color:#039;background-color:#b9c9fe;} " & _
".tg .tg-s6z2{text-align:center} " & _
".tg .tg-vn4c{background-color:#D2E4FC} " & _
"</style>"
   
   
        strAns = strAns & "<table class='tg'> " & _
                          "<tr>" & _
                           " <th class='tg-s6z2' colspan='4'><b>Note:<b/> " & !NoteHeading & "</th>" & _
                          "</tr>" & _
                          "<tr>" & _
                            "<td class='tg-vn4c'>RFC:</td>" & _
                            "<td class='tg-vn4c'>" & !ChangeID & "</td>" & _
                            "<td class='tg-vn4c'>Date:</td>" & _
                            "<td class='tg-vn4c'>" & !NoteDate & "</td> " & _
                          "</tr>" & _
                         " <tr>" & _
                            "<td class='tg-031e'>Area:</td>" & _
                            "<td class='tg-031e'>" & !NoteArea & "</td>" & _
                             "<td class='tg-031e'> Comment By:</td> " & _
                             "<td class='tg-310e'>" & !CommentBy & "</td> " & _
                          "</tr>" & _
                            "<tr>" & _
                             "<td class='tg-310e'>To be actioned by:</td>" & _
                            "<td class='tg-310e' colspan='3'>" & !Action & "</td>" & _
                         "</tr> " & _
                          "<tr>" & _
                             "<td class='tg-vn4c'>Note:</td>" & _
                            "<td class='tg-vn4c' colspan='3'>" & !NoteText & "</td>" & _
                         "</tr> " & _
                        "</table> "
        End If
        
    End With
    
    ' output
    fAddNote = strAns
    
    
    
fAddNote_exit:
    Set rs = Nothing
    Set db = Nothing
    Exit Function
fAddNote_error:
     GoTo fAddNote_exit
 End Function

The above code is called from a looped bit of VBA that loops through the records required to be entered as notes. The above code adds the HTML code to the record information so that the notes appear to be in tables. (Whereas the first template is formatted text)
The result is HTML code to put in the body of the email.
For completeness this is a bit of code that calls the above function and the code that produces an email...

 Private Sub cmdSelected_Click()
    Dim temp As Long
    'loops through selected notes and adds to email
       For Each varItem In Me.lstHeadings.ItemsSelected
             temp = Me.lstHeadings.Column(0, varItem)
             strBody = strBody & fAddNote(temp)
       Next varItem
        send_Email
        close_this_form
End Sub
  
     Sub send_Email()
      
          ' email dims
    Dim appOutlook As New Outlook.Application
    Dim msg As Outlook.MailItem
     
       strcc = "CC EMAIL addresses here"
'   strTO = "To email addresses go here"
   
    
       'Create new mail message
   Set msg = appOutlook.CreateItem(olMailItem)
   With msg
      .To = strTO
      .Subject = strSubject
      .HTMLBody = strBody
   '  .Body = strBody '' used htmlbody instead :-)
      .cc = strcc
      .Display
      
   End With
        
     End Sub

strBody is a global variable that the send_Email sub uses to put the HTML formatted string in the body of the email.
I hope this helps

P.S this code is still in a development state.. so it works for me but I have not entered all the error handling that may be needed
T