Automated Email Alerts

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

The procedure we are about to explore applies to both Internet and Intranet emails. We have already configured Microsoft Outlook with a Lotus Notes profile for users who wish to send scheduled emails from Microsoft Access within a Local Area Network (LAN).

When the If SendObject Action is invoked with the appropriate parameters, Microsoft Access will utilize the default Outlook profile—whether configured for Internet or Lotus Notes—to retrieve the target email addresses and transmit messages accordingly.

In our scenario, we aim to send mail alerts on a weekly schedule, which requires some preliminary setup. Specifically, we need to create two small tables to support the automation process.

Address Book Table.

1. We will create a local Address Book table within the Access database to store the email addresses of recipients.

   Note: It is also possible to link the Microsoft Outlook or Lotus Notes Address Book directly to a Microsoft Access database. However, for our purpose, a simplified local table is preferred.

   While your Outlook or Lotus Notes Address Book may contain numerous contacts, our scheduled mail alerts will only be sent to a specific group of individuals relevant to the subject matter. Therefore, we will maintain a curated list of these recipients in a dedicated local table, populated with selected entries from the configured mail profile's Address Book. (Refer to the fifth image in the previous post titled Configure Outlook for Lotus Notes for context.)

   A sample layout of this local Lotus Notes Mail Address Table is shown below:

   

   Click to Enlarge

   We can use VBA code to read the contents of the local address table and construct the To and Cc fields of the email message dynamically.

   Lotus Notes email addresses are often long and structured, typically containing location names, department identifiers, and organizational units, separated by forward slashes. A typical address may look like:

   Nancy Davolio/Ohio/hrd/Orgzn01@Orgzn01

   However, for our local address table in Access, we only need to store the display name — typically the First Name and Last Name (e.g., Nancy Davolio). When the mail is sent via Lotus Notes, the application will resolve the full email address automatically by matching the display name against the entries in its main Address Book and insert the complete address into the email.

   In some cases, the contents of the report attached to the email may be relevant only to specific recipients, and we may wish to exclude others from receiving that particular message. To implement such targeted email distribution, we need a way to filter out unrelated addresses from the Address Book for the current mailing.

   This can be achieved by using a unique identifier, such as an Employee Code, which should be recorded in both the report’s source data and the local Address Book table (refer to the ECODE field in the Address Book example above). With this common field in place, we can link the two datasets and retrieve only those email addresses that match the relevant employee codes from the report.

   Alternatively, the program can search for each employee code from the report’s source data within the address table and extract the corresponding email addresses. This approach ensures that report attachments are sent only to the appropriate recipients, maintaining both relevance and confidentiality.

   However, for now, we will proceed with a simpler method in our demonstration.

   Email Scheduling

2. The second required table is a Control Parameter Table for the email automation program. This table stores the weekly mail alert schedule and other related control settings necessary for managing the automated email process.

   A sample structure of the Parameter Table is shown in the image below.

Configuring a Machine.

Although the application may be shared across multiple users on different PCs, it is not necessary to configure every machine for sending emails. In fact, we want to avoid having emails sent from multiple sources. To ensure consistency, the program will identify the designated mail-sending machine by checking the active computer name at runtime. This is achieved by storing the authorized Computer Name in a control field and comparing it with the name of the current machine before executing the mail procedure.

1. To find your computer's name, follow either of the methods below:

   Method 1: Using System Properties

   • Right-click the My Computer (or This PC) icon on your desktop and select Properties from the context menu.

   • In the System Properties window, go to the Computer Name tab.

   • Your computer’s name will be shown next to the Full computer name label.

   Method 2: Using the Command Prompt

   • Click the Start menu and select Run.

   • In the dialog box, type cmd and click OK to open the Command Prompt.

   • At the prompt, type set and press Enter.

   • A list of environment variables will appear. Look for the entry COMPUTERNAME=X, where X is your system’s name.

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

X = Environ("COMPUTERNAME")

Our email alert is scheduled to be sent once a week, on a specific day—let’s say every Monday. We assume the application will be opened daily by users for regular use. Each time the application launches, it will perform a check against the email schedule.

If the current day is Monday, the program will initiate the mail-sending process. However, to avoid duplicate emails, the report should be sent only once per day, regardless of how many times the application is opened on that Monday.

It’s also possible that the user might forget to open the application on Monday, or the scheduled day falls on a holiday, causing the email alert to be missed. In such cases, the program should detect the missed schedule and send the overdue email the next time the application is opened, regardless of the day.

To support this behavior, we will store the last scheduled date in a control parameter table. This allows the application to determine whether the current day has passed the scheduled date and act accordingly. For our example, we will begin with a sample date: Monday, November 19, 2007.

The Email Sending VBA Code

With the above groundwork completed, our preparation phase is now complete. It’s time to move on to the VBA code implementation.

Copy the Main Program provided below and paste it into a standard module in your Access project. After pasting the code, save the module to proceed further.

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

Important Note:
In the above code, you will need to modify the SendKeys "xxxxxxx~", False line. Replace the x characters with your Lotus Notes password, ensuring the tilde character (~) is retained, as it simulates the Enter keystroke.

This line is intended to automatically pass the Lotus Notes password in anticipation of the login prompt, allowing the process to continue without user intervention. However, since the mail may be routed through multiple layers, this approach is somewhat unreliable—the timing may not always align with the prompt. If this fails, Lotus Notes will still request the password, and you’ll need to enter it manually and press Enter.

For Internet users (i.e., sending via SMTP), this line can be disabled by simply commenting it out with a single quote ('), If Outlook has already been configured to store the email password.

To ensure a smooth process at application startup, we’ll also include a few lines of code in the Control Screen module to introduce a brief delay after the application opens, before calling the SendWeeklyMail() procedure.

' 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 a Table