Automated Email Alerts

Continued from the previous post: Configure Outlook for Lotus Notes.

The next procedure, that we are going to look into, is applicable for both internets as well as Intranet Emails. We have already configured Microsoft Outlook with Lotus Notes Profile for those who would like to try sending scheduled Emails from MS-Access in their Local Area Network (LAN).

When we invoke the SendObject Action to send Emails with applicable parameters, the Target Addresses will be picked up from the default MS-Outlook Profile (Internet or Lotus Notes) and transmit Emails to the destination.

We will send the Mail Alerts on a weekly schedule and little more preparation is required for this event. We need two small tables for the Program.

Address Book Table

1. A Local Address Book Table to keep the Recipient's Addresses in Access.

   NB: It is possible to attach MS Outlook Address Book or Lotus Notes Address Book to Microsoft Access Database.

   We may be having Email addresses of many individuals in our Contact List in MS Outlook or Lotus Notes. But, we are planning to send the automated Mail Alerts regularly only to a few people who are involved with the subject matter. We will keep a list of those email addresses picked from the Address Book of the configured profile (please see the fifth image in the earlier Post: Configure Outlook for Lotus Notes) in a local table. A sample image of the local Address Table for Lotus Notes Mail is given below:

   

   Click to Enlarge

   We can read the contents of this Table with Code and create the To & Cc Field values of the Email message.

   Lotus Notes Email Addresses are very lengthy with Location-Names, Department-Names, and other details separated by a forward slash, like 

   Nancy Davollio/Ohio/hrd/Orgzn01@Orgzn01.

   In our local address table, we only need to keep the First-Name, Last-Name part (Nancy Davolio) to use as Mail ID. When the Mail is sent through Lotus Notes it will find and pick the full Email Addresses using the First Name, Last Name part, from its main Address Book and insert them into the Mail.

   Suppose the contents of the Mail attachment (the Report), should go to only certain recipients in our address book on a particular day and we don't want others to receive the Mail. To implement such a type of refinement in the mail sending procedure, we must find a way to filter out unwanted Addresses for the current mail. This can be achieved if some kind of personal identifying information (like the employee Code), were already recorded in the main table. The employee code also must be included in the Address Book Table in a separate field (See the ECODE field in the above Address Book image). Having this information in both files we can easily link them together and pick addresses that match with the Report Source Data. Or in Program, we can search for the employee code in the address table using the Report Source data Value and pick the email addresses. This way we can ensure that the emails with report attachments go to the correct recipients and prevented others from getting the mail. But, here we will try the simple method.

   Email Scheduling

2. The second one is a control parameter table for the Email Program, which holds the weekly mail alert schedule information. The Parameter Table image is given below:

Configuring a Machine

Your Application may be shared by several users, but it is not necessary to configure all the PCs for sending emails. Even if they all are, we don't want our emails to go from different machines. When the Application is open in any of those machines the program will perform a check to identify the correct machine to send the mail to. For this, we will keep the Computer Name in a field to cross-check with the active Machine Name.

You can look for your computer's name in one of the two methods explained below.

1. Right-click the My Computer Desktop Icon and click on the Properties option in the displayed menu. Select the Computer Name Tab. Your Computer's name will appear on the right side of the Full computer name: label.
2. Click on Start Menu; select Run, then type cmd in the Open control and click OK. The DOS command prompt will appear. Type Set and press Enter Key. This action will display a long list of Environment Variable settings. Among them, you will find your computer's name like COMPUTERNAME=X where X stands for your Computer's name.

We can check for this value using VBA Environ() Function with the parameter value "COMPUTERNAME".

X = Environ("COMPUTERNAME")

Our Mail is scheduled to send only once in a week on a particular day, let us say every Monday. We assume that the Application will be opened for normal use every day by the User(s) and every time the Program will perform a check for the mail sending schedule. If it is Monday then the mail must go out only once on that day with the Report attachment, even if the Application is open several times on Monday.

It can also happen that the user forgot to open the Application exactly on Monday or it was a holiday and the Mail schedule is bypassed. In such cases whenever the user opens the Application next time, any Day, the overdue mail should go and must get prepared itself for the next mail sending schedule next Monday. For this preparation, we will keep a date in the parameter table. Let us start with the sample date Monday, November 19, 2007.

The Email Sending VBA Code

With the above lines, I hope our preparation story is all done and it is time to go to the VBA Code. Copy the Main Program given below and paste it into a Standard Module of your Project and save the Module.

Public Function SendWeeklyMail()
'--------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 01/11/2007
'--------------------------------------------------------
Dim db As Database, rst As Recordset
Dim m_Addto As String, m_Cc As String, j As Integer
Dim msgBodyText As String, m_Subject As String
Dim m_MailDate, m_ComputerName As String, chk_CName As String

On Error GoTo SendWeeklyMail_Err

'Field Name and Table Name are same
m_MailDate = DLookup("MailDate", "MailDate")
m_ComputerName = DLookup("ComputerName", "MailDate")
chk_CName = Environ("COMPUTERNAME")

'identify the Mail Client Computer
If chk_CName  = m_ComputerName Then
   Exit Function
End If

'Verify Mail Schedule
If m_MailDate + 7 > Date Then
    Exit Function ' mail not due
End If

m_Addto = ""
m_Cc = ""
j = 0

'Create Address List
Set db = CurrentDb
Set rst = db.OpenRecordset("Add_Book", dbOpenDynaset)
Do While Not (rst.EOF)
    If rst!TO Then
        If Len(m_Addto) = 0 Then
            m_Addto = rst![MailID]
        Else
            m_Addto = m_Addto & ", " & rst![MailID]
        End If

    ElseIf rst![cc] Then

        If Len(m_Cc) = 0 Then 
           m_Cc = rst![MailID]
        Else
            m_Cc = m_Cc & ", " & rst![MailID]
        End If

    End If
rst.MoveNext
Loop
rst.Close

m_Subject = "BANK GUARANTEE RENEWAL REMINDER"

msgBodyText = "Bank Guarantee Renewals Due weekly Status Report as on " _& Format(Date, "dddd, mmm dd,yyyy") & " which expires within next 15 days Cases is attached for review and " & "necessary action. "
msgBodyText = msgBodyText & vbCr & vbCr & "Machine generated email, " & " please do not send reply to this email." & vbCr

'replace x with your Lotus Notes password retaining the ~ character at end. 
SendKeys "xxxxxxx~", False

DoCmd.SendObject acReport, "BG_Status", "SnapshotFormat(*.snp)", m_Addto, m_Cc, "", m_Subject, msgBodyText, False, ""

'Update MailDate to current date (if Monday) or date of
'previous Monday, if the mail was send after the due date.

Set rst = db.OpenRecordset("MailDate", dbOpenDynaset)
rst.Edit
Do While rst![MailDate] + 7 <= Date
  rst![MailDate] = rst![MailDate] + 7
Loop
rst.Update
rst.Close

SendWeeklyMail_Exit:
Exit Function

SendWeeklyMail_Err:
MsgBox Err.Description, , "SendWeeklyMail()"
Resume SendWeekly
Mail_Exit

End Function

In the above code, you must make a change in the SendKeys "xxxxxxx~", False command parameter given in quotes. You must replace the x characters with your Lotus Notes Password and retain the tilde (~) character, which is equal to the Enter Keystroke.

This will send the Lotus Notes password in advance and is expected to meet the Password Prompt of Lotus Notes in time, in that case, the password Prompt will not pop up. Since the mail is routed through different channels this is a blind shot and can miss the target, in that case, Lotus Notes will demand the password and you have no other choice, but to key in and press Enter.

For Internet Users, this can be disabled with a single quote on the left side of the command, if the SMTP in Outlook is already configured with the Email password.

We will put a few lines of code in the Control Screen Module to give a few seconds delay immediately after opening the Application to get set for calling the SendWeeklyMail() Program.

' put the next line at the header section of the Module
Dim i as integer 

Private Sub Form_Load()
   i = 0
   Me.TimerInterval = 1000
End Sub

Private Sub Form_Timer()
i=i+1
If i = 16 then ' 15 seconds delay
   Me.Timerinterval = 0
    SendWeeklyMail
End if
End Sub

Copy and paste the above lines in the Control Screen Module save and close the Form. Try out the Automated pre-scheduled EMail Procedure.

• MS-Access & Graph Charts-2
• MS-Access & Graph Charts
• Reports Page Border
• Highlighting Reports
• Saving Data on Forms Not in Table