LEARN MS-ACCESS TIPS AND TRICKS: Emails and Alerts

Showing posts with label Emails and Alerts. Show all posts

Database Open Close Event Alerts

a.p.r. pillaiJune 06, 2008Emails and Alerts, msaccess alerts No comments

Introduction.

Your application is installed on a Network and shared by different Users. You would like to monitor the activities of this Database, like which User Opens or Closes it, and at what time? Or your database is not a Secured one (which means anybody can open it) and is installed on a Network, for the benefit of a few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know someone else is opening it to explore and when they do catch them red-handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing him that he is not in the permitted zone and requesting him to close the database immediately. He will know that you are observing the activities of over your Database and will not attempt a second time when you are around! If he responded positively by closing the Database then you will get an Alert message from the Database transmitted by the Closing Event.

Or you are doing some maintenance work on the Database and you would like to know if someone opens it in the middle of your work. Or you are waiting for the Current User to close the Database to make some changes. Or you would like to send a message to the Active User (without lifting your Telephone), asking her to shut down the Application so that you can fix some bugs or want to inform her about the completion of the maintenance tasks by sending a message to her PC.

It is interesting and very useful to automate some of these tasks. Get timely feedback from your Databases so that you can plan your actions and execute them conveniently, keeping in touch with your Users and Databases all the time.

All these tricks are possible when you get Alert Messages from your Databases to your PC immediately after the User(s) opens or closes it. Visit the Page Titled: Sending Alerts to Workstations based on process-related activities within a Database. Once you get the User's Workstation ID you can send messages back to them from your Desktop, if you need to communicate with them. Read the earlier Article Titled: Who is Online, where we have approached some of these issues differently.

You have several Applications on the Network. Alert Messages coming to you are too many, then switch them Off or keep a few cases On. You don't need to touch any of your Databases at all for this changeover.

After going through the above introduction you may be interested to have a look at it and probably assume that it is a big project for implementing all of these features. Or you have already come to the conclusion that the whole thing is going to be so complicated you don't want to touch it at all. Relax, it is so simple you will love it, I am sure about it. If you have 10 minutes of your time to spare, you can implement it and try it out. All you need is a Windows Network in your Office.

The Alert Message Contents

The Alert Messages must be loaded with the following information when received:

User Workstation-ID
Date and Time
Database PathName
Status info (Open/Close)
User Network ID
MS-Access User ID

Our Network Administrator was kind enough to set my Last Name (PILLAI) as my WorkStation ID. The images of sample Network Messenger Alerts that comes to my WorkStation are given below.

The Database Open Event Alert Message.

Database Close Event Alert Message

All we need for this interesting Trick is a VBA Routine (given below) and 3 Text Files. The VBA Program must be copied to the Global Module of your Database and call it from the Main Switchboard (Control Screen) in the On Open and On Close Event Procedures.

If you have a Library Database already linked with your Applications from a Common Location on the Server, then it makes it much easier to implement by placing the main Code in the Library Database only. Then you need to install only two lines of code in the On Open and On Close Event Procedures in the Main Switchboard Form (Control Screen) in your Databases.

Prepare for a Trial Run

But for now, let us try it in one of your Databases installed in Network Location shared by Users. When you are ready to implement it in other Databases you can do so following the same procedure. The Names and usage of the three Text files (all of them are Optional), created for your control purposes, are given below:

File-1: AccsCtrl.txt - List of Databases where the Message Alert Programs are running.

File-2: AccLog.txt - Message Alert Log entries for reference purposes.

File-3: Message.BAT - For sending messages, and alerts from your PC to your Database Users' Workstation.

Copy and Paste the following VBA Code into a Global Module of your Project or Library Database and save it:

The OpenCloseAlert() Function

Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"

Public Function OpenCloseAlert(ByVal mstat As String)
'-----------------------------------------------------------
'Author: a.p.r. pillai
'URL   : www.msaccesstips.com
'Date  : 20/05/2008
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim t As Double, WorkStationID As String * 15
Dim netUserName As String * 15, accUserName As String * 15
Dim str As String, loc As Integer, AlertMsg As String
Dim AlertFlag As String,  flag As Boolean
Dim chk As String, dbpath As String, m_stat As String * 1
Dim status As String * 8, dttime As String * 22

On Error GoTo OpenCloseAlert_Err

dbpath = CurrentDb.Name
m_stat = mstat
str = Dir(accmsgtxt)
If Len(str) = 0 Then
'accsctrl.txt File Not Found   
'ignore ON/OFF flag checking   
'proceed to Alert Message Section
   GoTo nextstep
End If

'open the control file and check the Status
Open accmsgtxt For Input As #1
AlertFlag = " ": flag = False
Do While Not EOF(1)
Input #1, chk
If flag = False And Left(chk, 5) = "ALERT" Then
   flag = True
   chk = UCase(chk)
   loc = InStr(6, chk, "OFF")
   If loc > 0 Then     'Turn off Alerts from All Databases
     Exit Function
   Else
     GoTo readnext
   End If
End If

loc = InStr(1, chk, dbpath)
If loc > 0 Then'database file found, take the flag value
  AlertFlag = Right(Trim(chk), 1)
  Exit Do
End If
readnext:
Loop
Close #1

If AlertFlag = "0" Or Len(AlertFlag) = 0 Then   'Turn off Alert in this Database
   Exit Function
End If

nextstep:

WorkStationID = Environ("COMPUTERNAME")
netUserName = Environ("USERNAME")
accUserName = CurrentUser

status = IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE")
dttime = Format(Now(), "mm/dd/yyyy hh:nn:ss")

AlertMsg = LCase(CurrentDb.Name) & " OPEN/CLOSE Status : " & vbCr & vbCr & "STATUS............: " & IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE") & vbCr & "WORKSTATION ID....: " & WorkStationID & vbCr & "NETWORK USERNAME..: " & netUserName & vbCr & "ACCESS USERNAME...: " & accUserName & vbCr 

'insert your Workstation ID replacing the text
 Call Shell("NET SEND  " & AlertMsg, vbHide)

str = Dir(acclogtxt)
If Len(str) = 0 Then
  GoTo OpenCloseAlert_Exit
Else
  Open acclogtxt For Append As #1
  Print #1, status; dttime; WorkStationID; netUserName; accUserName; CurrentDb.Name
  Close #1
End If

OpenCloseAlert_Exit:
Exit Function

OpenCloseAlert_Err:
MsgBox Err.Description, , "OpenCloseAlert()"
Resume OpenCloseAlert_Exit
End Function

The first two lines in the above code must be placed on the Global Declaration area of Module, below the Option Declarations as indicated below.

Option Compare Database
Option Explicit
Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"

Copy the following lines in the VBA Module of the Main Switchboard (Control Screen) Form or copy only the middle line into their respective Event Procedures, if they already exist:
```
Private Sub Form_Open(Cancel As Integer)
     OpenCloseAlert "Open"
End Sub

Private Sub Form_Close()
     OpenCloseAlert "Close"
End Sub
```
We will create the Text Files AccsCtrl.txt and AccsLog.txt in a common location on the Server, where all your Application Users have Access Rights. Change the Server Path h:\inhsys\comlib in the Constant Declaration lines to the common location Path on your Server. Do not use UNC like Path specification \\Server\Folder\database.

Note: The term Path represents the location address of a file without the File Name, but Path Name denotes the location Address including File Name and File Name Extension.
Create Text Files
Open Windows Notepad (Start - - > Program Files - - > Accessories - - > Notepad). Copy and Paste the following lines into the blank Notepad File:
```
ALERT=ON
H:\FolderName\FolderName\MYDB1.MDB=1
G:\FolderName\MYDB2.MDB=0
```
Change the second and third lines to point to your Databases on a Server already in use by others. Save the Text File with the name AccsCtrl.txt at the Location on the Server specified in the constant declaration line path given above. Databases can be in any location on the Server.
Select New from Notepad File Menu to open a new text File. Save the empty text file in the same location on the Server with the name AccsLog.Txt. The Log details of the Alert Messages that you receive will be saved in this file too. You can open and view history when needed.

This File can be created through code, if not present. But, I thought it was better to leave the option for you to decide, whether you need it or not. If you have several Applications running with this Alerts mechanism the log file can grow very fast and can shrink the allocated space on your Disk Quota. You may try this out and watch the frequency of events. You can periodically review the contents and delete old entries or copy them into another file on Local Drive.
Create Desktop Shortcuts to these Files so that you can open them quickly, and edit or view the contents when needed. Right-Click on the Desktop, highlight New and select Shortcut from the displayed menu. Browse to the location of AccsCtrl.txt file and select it, click OK, click Next then click Finish.

Let us look into the Logic of the Main Program OpenCloseAlert() and how the File AccsCtrl.txt contents are interpreted and control the execution of the Program.
- If you have not created the File AccsCtrl.txt at all (as per the declaration Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt") in the specified location, still you will receive the Alert Messages but you cannot control it by switching Off or On at will.
- If you have created the File AccsCtrl.txt but forgot to add the Database Path Name with flag 1 at the end, then the Alerts will not trigger for that Database.
- If the first line in this file is ALERT=OFF then further lines in this file will not be processed and turn Off Alerts from All Databases.
- Any value after the word ALERT= other than OFF (ON or anything else except space) will be ignored and will proceed with the individual Database level flag checking. We opted out of checking the ON flag setting because we are doing it at the individual database level to trigger the Alerts. Besides, this line was introduced to switch off all alerts from all databases with one change. If you prefer to control with an individual file level flag setting (which is explained below) you don't need to put this line at all in this File.
- If you place the ALERT=OFF statement after a few lines of the Database Path Names (see Step-4) in the Text File then the program always checks the lines above this statement and executes the program OpenCloseAlert() depending on the logic setting (1 or 0) at the Database level. But Path Names after this line are ignored and never checked.
- We have added two sample Database Path Names after the first line with Alert status flag =1 and =0 at the end (1= Alerts ON, 0=Alerts OFF). You may add any number of Databases in this way with flag value 0 or 1 after the equal (=) sign, after installing the Program OpenCloseAlert() in the Database.
- When the program encounters the Database Path Name it extracts the last character from that line to check the status. Ensure that you are not adding any other characters after 0 or 1 except white spaces.
- If the flag is 0 then Alert Message will be turned off for that Database. Any other value will send it.
- By adjusting the values in this file you can control Alerts coming to you without touching any of your Databases.
- If you have not created the Logfile AccsLog.txt the program will not generate any error, but the message history is not maintained. You can delete old records from this file to keep the file size small on the Server.
- Create copies of these Files periodically as a backup and keep them in a safe place for restoring, if the active one got corrupted or deleted by mistake.
The DOS Batch File.
We will create a DOS Batch File on your Desktop for sending messages to your Database Users as and when the need arises. Open Windows Notepad and write the following line:
```
NET SEND WorkstationID messagetext
```
Note: The NET SEND Command works under Windows XP only. Later versions of Windows use the MSG Command. C:\>MSG /? - The Command displays the details of different Parameters which can be used with this Command.
Write the network of a known machine in place of the text 'WorkstationID' and a test message, replacing the text 'message text--.'
Select Save As from the File menu and Browse to your Desktop (C:\Documents and Settings\your network Id\Desktop) and save it with the name MESSAGE.BAT. Select All Files in the Save as Type control before saving it. The MESSAGE.BAT name will appear on your Desktop.
Since this file is on your Desktop you can always right-click on it, select Edit from the displayed menu and open it in Notepad and change the WorkStationID and message text and close it.
Double-click on the Desktop icon of MESSAGE.BAT File to send the message after making changes. Batch Files are treated as programs and if you face any security issues please check with your System Administrator. Alternatively, you can send Network messages directly from DOS Prompt. Open Run from Start Menu. Type cmd and click OK. Type the NET SEND command followed by WorkstationID and your Message Text, giving space between the WorkstationID and the Message, then press Enter Key.

When you implement this method correctly, you can keep track of your Databases and the log file (AccsLog.Txt) will record the history of activities. If you need, you can link this Text File to a Database for analysis purposes and find out the frequency and User-level time spent, and so on.

When you communicate with your Database Users asking them to open or close the Database they will also know that you are watching over their activities. You can open and check the AccsLog.Txt file contents to find out who is active and for how long etc.

You must consider securing your Databases for the internal security of Objects and for regulating access rights to a different category of Users if you have not done that so far. Read the Pages Microsoft Access Security for details on this very powerful feature. It may take some time to understand all the twists and turns of this feature. But, if you start using it you can learn it over a period of time by experimenting. Believe me, if you are a serious MS-Access Developer you must learn it. Doesn't matter how long it takes to understand the rules. You can Post your doubts over Security issues in the Comments Section. I will extend all possible help that I could give you.

The Trial Run.

Now the stage is all set for trial Runs. Ask one of your Application Users to open the above Database from their Workstation. You must get a similar Popup message, like the sample image given above, during both Opening and Closing events. Ask more Users to open the same Database from their PCs too. You must get Alerts from each instance of the same Database.

You can implement the same Code in other Databases also and add their Path Names in the AccsCtrl.txt file.

SENDING ALERTS TO WORKSTATIONS

a.p.r. pillaiMarch 22, 2008Emails and Alerts, msaccessQuery No comments

Introduction.

Let us consider a scenario to understand the importance of this method.

Suppose we have three MS Access front-end applications—A, B, and C—linked to a shared table in a back-end database on the network. Users of Applications B and C can update information in this table and complete their tasks only after the main records have been created by users of Application A. Not all records are time-sensitive, but some require urgent attention from users of B and C.

We need a mechanism to inform Application A users to prioritize certain records for updating in the system. Once these records are updated, users of Applications B and C should receive alerts indicating the change in status, prompting them to open their applications and act on their respective tasks. Importantly, Applications B and C do not need to remain open continuously to receive these alert messages.

Functional Diagram.

A Functional Diagram of this arrangement is given below:

Click to Enlarge

The Solution:

Create a new table with the name Alert_Param with similar fields given below in the BE Database:
- Create key fields—such as Supplier Code, Invoice Number, and Invoice Date—to establish a link with the main table’s records. These fields, when combined, should be defined as the Primary Key.
- Add a Text field named WrkStation to store the name of the workstation on the Windows network. This value will be recorded automatically when users of Applications B and C enter the key field values from their respective workstations. It will be used by the system running Application A to send alerts to the appropriate recipients.
- Include a Logical (Yes/No) field named Updated to indicate that an alert has been sent to the designated workstations, thereby preventing duplicate alerts for the same item. If users of Applications B and C miss a live alert (for example, if their machine was turned off at the time), this flag can serve as a visual cue that the relevant records have already been updated in the main table.
  Note: Management of the parameter table’s contents (adding, editing, and deleting records) will be performed exclusively by users of Applications B and C. Users of Application A will only reference this list to prioritize these cases and complete their part of the process.
- Finally, design a form named Alert to serve as the interface for working with the parameter table.
- Display the Property Sheet of the Wrkstation Field and change the Enabled property to No.
- Repeat this for the Logical Field with the name Updated. Both these fields are managed and used by the program only.
- Link the parameter table Alert_Param in BE to all the three Applications (A, B & C).
- Install the Alert Form in all three Applications.
- In Application A, open the Alert Form in the design view. Display the Form's Property Sheet and change the following Properties to prevent modifying the parameter data manually:
- Allow Edits = No
- Allow Deletions = No
- Allow Additions = No
- Allow Design Changes = Design View Only
Display the Code Module of the Form (View -> Code), copy and paste the following code in the Module, and save the Form:
```
Private Sub Form_BeforeUpdate(Cancel as Integer)
   Me![WrkStation] = Environ("COMPUTERNAME")
End Sub
```

Create a Query to JOIN the Alert_Param Table with the Main_Table on the Key Fields (Supplier Code, Invoice Number & Invoice Date) in the BE Database to pick the records that match for sending Alerts.

SELECT DISTINCTROW Alert_Param.*
FROM Main_Table 
INNER JOIN Alert_Param ON (Main_Table.Supl_Code = Alert_Param.SuplCode) AND (Main_Table.INVOICE = Alert_Param.INV_NO) AND (Main_Table.INVOICE_DATE = Alert_Param.INV_DATE) 
WHERE (((Alert_param.Updated)=False));

Save the above Query with the name Alert_inQ.
Create a second Query as shown below using Alert_inQ as the Source and save it with the name Alert_in_ParamQ.
```
SELECT Alert_inQ.WrkStation
FROM Alert_inQ
GROUP BY Alert_inQ.WrkStation;
```
Copy and paste the code below into a Global Module of your Project and save it.

Public Function WKAlert()
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date : 01/03/2008
'All Rights Reservered by msaccesstips.com
'--------------------------------------------------------------
Dim wrkStn() As String, xlnvoice As String, msg() As String
Dim cdb As Database, rst1 As Recordset, rst2 As Recordset
Dim reccount, j As Integer, T As Double, flag As Boolean

On Error GoTo WKAlert_Err

reccount = DCount("* ", "Alert_in_ParamQ")

If reccount > 0 Then
 'check the number of workstations involved
     ReDim wrkStn(l To reccount) As String, msg(l To reccount) As String
    Set cdb = CurrentDb
    Set rst1 = cdb.OpenRecordset("Alert_in_paramQ", dbOpenDynaset)
    For j = 1 To reccount
        wrkStn(j) = rst1![WrkStation]
    Next
    rst1.Close
Else
    Exit Function
End If

Set rst1 = cdb.OpenRecordset("Alert_inQ", dbOpenDynaset)
For j = 1 To reccount
    rst1.MoveFirst
    flag = False
    Do While Not rst1.EOF
    If flag = False Then
        msg(j) = " UPDATED "
        flag = True
    End If
     'add the Supplier Invoice details.
    If rst1![WrkStation] = wrkStn(j) Then
        msg(j) = msg(j) & "Supl.Code: " & rst1![SuplCode] & ", " & "Invoice: " & rst1![Inv_No] & ", " & "Inv.Date: " & rst1![Inv_Date] & ", : "
        rst1.Edit
        rst1![Updated] = True
        rst1.Update

    End If
        rst1.MoveNext
    Loop
    'Use the NET SEND command and format the message
     msg(j) = Left(msg(j), Len(msg(j)) - 2)
    msg(j) = "NET SEND " & wrkStn(j) & msg(j)
    msg(j) = msg(j) & " on " & Now()
Next

For j = 1 To reccount
    Call Shell(msg(j))
' send message through Network
     T = Timer
    Do While Timer < T + 0.5
       DoEvents 'Delay Loop for the next message
   Loop
Next

WKAlert_Exit:
Exit Function

WKAlert_Err:
MsgBox Err.Description, , "WKAlert"
Resume WKAlert_Exit
End Function

Note: DOS Command NET SEND works under Windows XP only, later Versions of Windows use the MSG Command. Type MSG /? On the Command prompt, for help and an optional parameter listing.

Consider Options for Sending Alerts to Workstations.

Automated Alert

a) This method is recommended when multiple records are added to the main table in batch mode—for example, at the end of intermediate processing within Application A—based on parameters created by users of Applications B and C. In such cases, call the function `WkAlert()` at the end of the processing steps.

When using this approach, all updated record references for a particular workstation can be consolidated into a single message (via the relevant queries) and sent together, avoiding repetition for each individual record. This is the most efficient method, as it sends alerts automatically while reducing the total number of messages. The VBA code provided here demonstrates this approach and executes after the records are added to the main table.

b) Alternatively, run the program from the After Update event of an individual record updated in Application A. In this case, the program will reference the parameter table created by Applications B and C. If the updated record in the main table matches an entry in the parameter table, then an alert is sent to the corresponding workstation for that record. The updated flag in the parameter table is set accordingly.

Sending Alerts Manually

If the above automated methods are not practical in certain situations, alerts can be sent manually to the respective workstations by clicking on a command button after updating all, or a selected subset, of the records requested through the parameter table.

Clicking the button will invoke the program and send one consolidated message to each workstation for the group of records belonging to them, based on the parameter table.

While this approach relies on manual user intervention and is therefore less efficient than automation, it is still preferable to sending alerts at the individual record level, as described in item b above.

Download.

Download AlertDemo.zip

Automated Email Alerts

a.p.r. pillaiNovember 16, 2007Emails and Alerts, msaccess email 4 comments

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.

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
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.

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.

Configure Outlook for Lotus Notes

a.p.r. pillaiNovember 08, 2007Emails and Alerts, msaccess email 2 comments

Continued from earlier Post: MS-Access and Emails

We have seen that sending emails from Microsoft Access is quite straightforward, just like other familiar methods. With a few clicks, we can send emails with MS Access objects attached. The available output formats for the attachment depend on the type of object selected. For example, if a VBA code module is selected, it will be automatically converted into plain text format before being attached to the email.

As mentioned in an earlier post, we can automatically send mail alerts on important matters within a Local Area Network (LAN) using email transport services like Lotus Notes, directly from a Microsoft Access application.

However, before we delve into the mail-sending procedures and related programs, we must first configure Microsoft Outlook on one of the PCs to work with Lotus Notes and use its contact list for managing recipients.

Access Mail Client

Your application may be shared by multiple users across different PCs; however, all outgoing emails should be routed through a single designated machine—let’s call it the Access Mail Client. This machine must be configured specifically for sending emails via Lotus Notes.

Lotus Notes requires both a Mail Server and a Client application installed on each PC. Accordingly, the Access Mail Client must have the Lotus Notes Client installed and actively used for sending emails. Additionally, this machine will require a unique Lotus Notes ID file and an associated Lotus Notes email account.

NB: If you prefer that Mail Alerts from your Access application do not display a personal user ID as the sender, a dedicated Lotus Notes mail ID should be created for this purpose. The Lotus Notes Client on the Access Mail Client machine must then be configured using this ID. You may need to consult your Lotus Notes Administrator to set up and configure this special account.

Configuring MS Outlook with Lotus Notes.

We will now explore the Microsoft Outlook configuration steps, accompanied by a few illustrative images and explanations of what each step accomplishes.

Your Lotus Notes ID file must be registered within Lotus Notes itself to make it accessible to external programs, such as Microsoft Outlook. To do this, open Lotus Notes and log in with your User ID and password. From the File menu, select Mobile → Edit Current Location. The Location: Office (Network) settings screen will appear.

Click to Enlarge
Click on the Advanced Tab and select Basics from the displayed 2nd set of Tabs. See the Image below.

Click to Enlarge
Click on the text box next to the User ID label to activate it, then click on the Search (magnifying glass) icon located below the label to browse for your Lotus Notes User ID file. The typical file path is:
```
makefile
X:\Lotus\Notes\Data\filename.id
```
Here, X represents the drive where Lotus Notes is installed, and the filename usually resembles your name or the one assigned by your Lotus Notes administrator. Locate this .id file and insert it into the text box.

This file contains your Lotus Notes email ID and password, which Microsoft Outlook requires to access the Lotus Notes Contact List and to send or receive emails through Lotus Notes.
Close the Control after inserting the Location reference and minimize Lotus Notes.
Open the Control Panel from the Start Menu → Settings, then double-click the Mail icon to display the Mail Setup control, which includes the Services, Delivery, and Addressing tabs (as shown in the image below).

Please note that if your version of Microsoft Outlook is not installed as Corporate or Workgroup, the available options and layout may appear differently. The screenshots referenced are based on a Microsoft Office 2000 installation.

Click to Enlarge
Click on the Services tab. If no mail services have been previously configured, the Profiles window will appear empty.
Next, go to the Delivery tab and select Lotus Notes Message Store in the control below. At this point, you may be prompted to enter your Lotus Notes password to complete the setup. Once this step is finished, the Information Service entry for Lotus Notes Transport will appear in the following window.
Click the Add button to open the Add Services to Profile list. From the available options, select Lotus Notes Mail and click to add it to the Profiles window.

Click to Enlarge
Click on the Addressing tab in the Mail Setup control panel. You can choose either the Lotus Notes Main Address Book or your personal Lotus Notes Address Book—which may include selected entries copied from the main address book—and assign it to the appropriate controls. Refer to the image below for guidance.

Click to Enlarge
Click Apply to save the updated settings, and then click OK to close the window. These configuration steps can also be accessed through the Options menu under Tools in Microsoft Outlook.
Next, we need to define how contact names are stored in the Address Book, which can be done from within Outlook.
Open Microsoft Outlook and select Preferences from the Tools menu.
Click on the Contact Options button.
Choose the appropriate settings in both controls as shown in the image below, then click OK to confirm.

Click to Enlarge
The first control settings say that while sending mails, insert the Names in this order, but while storing the values in the Address Book, use the format in the second control.
When you look for the Email addresses manually in the Address Book, it will be displayed in the second format, and you can search for the names by typing the first few characters of the first name in the lookup control.
But when we attempt to send mail from MS-Access, we must present the email ID to Lotus Notes in the first format.
The Image of the Lotus Notes Mail ID lookup control is given below for reference. I have edited the image to insert those names more familiar to you. Check the name displayed in the left window and how the name format changes when it is inserted in the To line.

Click to Enlarge
You may now try to send an e-mail through Lotus Notes Mail manually from MS-Access, as we did in an earlier session, using some of your friends' Lotus Notes IDs. During the mail transmitting point, Lotus Notes will prompt for the password.

Next, we will see how to send the Mail Alerts automatically from Microsoft Access through Lotus Notes at scheduled intervals.

MS-Access and E-Mail

a.p.r. pillaiNovember 03, 2007Emails and Alerts, msaccess email 6 comments

Introduction

You are unlikely to use Microsoft Access to send personal emails over the Internet, especially when so many other convenient options are available.

However, if your Access application regularly generates reports that need to be shared with remote users—and you are currently sending them via traditional postal services—email becomes a practical and efficient alternative worth considering.

If you are connected to a Local Area Network (LAN), reports can also be distributed internally through intranet mail services such as Lotus Notes.

For both Internet and Intranet mail to function properly, Microsoft Outlook must be configured as the communication bridge between Microsoft Access and the mail transport system—whether it’s the SMTP server of your Internet Service Provider (ISP) or an Intranet mail system like Lotus Notes. If you’re already using Microsoft Outlook to send and receive emails, then it's likely already set up correctly. Outlook also maintains a Contact List, which can be used to select prospective email recipients.

Note: Configuring Microsoft Outlook is beyond the scope of this article. For guidance, refer to Outlook’s built-in Help or the documentation provided by your ISP. That said, we will cover the configuration steps for using Outlook with Lotus Notes intranet mail in upcoming posts.

Outlook Versions.

There are two types of Microsoft Outlook installations:

Internet Mail Only – typically used for personal email through services like Gmail or Outlook.com.
Corporate or Workgroup – configured for use in business environments, often connected to Microsoft Exchange or intranet mail systems like Lotus Notes.

To check which version is installed on your PC:

Open Microsoft Outlook.
Click on the Help menu (in older versions) and select About Microsoft Outlook.

If it is the Corporate or Workgroup version, you will see this label explicitly displayed in the upper section of the About screen.

Scenario 1: Automated Birthday Greetings

If your company is a service-oriented organization—such as a Bank, Insurance Firm, or Health Clinic—you likely maintain customer profiles that include date of birth. You can configure your Microsoft Access application to automatically:

Identify customers whose birthdays fall on the current day.
Generate personalized birthday messages using a predefined email template.
Send the greeting via Outlook with a single click or fully automated routine.

This adds a personal touch to your service and strengthens customer relationships.

Scenario 2: Notification of Revised Interest Rates

When your bank revises interest rates for Fixed Deposits or Savings Schemes, you may want to:

Notify all relevant account holders or only those with deposits maturing soon.
Include personalized details such as account number, maturity amount, or new applicable rates.
Attach an official circular or brochure in PDF format.

Using Microsoft Access, you can filter target recipients, generate individual messages, and dispatch them through Outlook in a batch process.

Scenario 3: Bank Guarantee Renewal Alerts

If your company extends credit to customers based on renewable Bank Guarantees, your Microsoft Access application can be configured to:

Monitor expiry dates of submitted guarantees.
Automatically generate email alerts to internal stakeholders or account managers responsible for follow-up.
Ensure that reminders are sent well in advance of the renewal due dates, giving ample time to coordinate with the customer.

This proactive approach minimizes credit risk and helps maintain compliance with company policies and financial safeguards.

In all the above scenarios—or in similar situations—the Mail Alert process can be fully automated using VBA programs within Microsoft Access. Toward the end of this series, we will explore a sample procedure for sending emails automatically through Lotus Notes, demonstrating how such integration can streamline communication and improve operational efficiency.

Using Lotus Notes for email.

In the meantime, let us look into a simple mail-sending example that sends a Report as an attachment, in Microsoft Access Snapshot Format, manually.

Open your database and select a Report (avoid choosing the dynamic report we created in the previous session).
Go to the File menu, point to Send To, and click Mail Recipient (as Attachment).
From the list of available file formats, select Snapshot Format.
A dialog box will appear, prompting you to choose a Mail Transport Profile already configured in Microsoft Outlook or to create a new one.
Choose the default profile you typically use to send emails. If you set this profile as the default, this dialog will not appear the next time.
After clicking OK, the email editor screen will open, allowing you to modify the message content before sending.

See the image below.

Click to Enlarge
Click the To button to select email addresses from the Contacts List in Microsoft Outlook. The Cc button functions the same way.
You will notice that the selected Report has already been converted into Microsoft Access Snapshot Format and attached to the email. If you prefer to send an external file—such as a Word or Excel document—instead, click the Attach... button and browse to select the file from your hard disk.
The blue-colored panel on the right side will remain hidden until you click the Attachment Options button located next to it.
Two attachment options are available. The default option, Regular Attachment, sends individual copies of the attachment to each recipient. The second option is intended for Intranet mail systems, where the attachment is saved in a shared location accessible to all recipients. This option can be safely ignored in most cases.
You can compose your email in plain text and click the Send button on the toolbar. Microsoft Outlook will handle the rest.

Sample Mail Image

A sample image of the Sent Items display of Microsoft Outlook 2003 is given below for reference.

Click to Enlarge

If you are sending a standard report as an attachment with predefined wording in the email body, the entire process can be automated using a macro. Refer to the configuration of the SendObject Command in the macro design image below for proper setup.

The SendObject Command

The SendObject command is selected in the Action column of the macro design grid, and the corresponding email-related field values are specified in the property sheet below. If you do not intend to send an Access object as an attachment, the first three property values can be left blank. Setting the Edit Message property to Yes will open the email client, allowing you to edit the message before sending, as demonstrated earlier. If it is set to No, the message will be sent directly through Microsoft Outlook without being displayed on the screen.

You can attach this macro to a Command Button or run it from VBA code using DoCmd.RunMacro "MacroName". If you wish to control the frequency of emails with the report attachment, you should implement a procedure that verifies the scheduled date or day before executing the macro. We will explore such a program—designed to send emails automatically on a weekly schedule—at the end of this series.

Next, we will explore the procedure for configuring Lotus Notes as a mail transport mechanism for sending emails within a Local Area Network (LAN).

Reminder POPUPs

a.p.r. pillaiSeptember 12, 2007Emails and Alerts, msaccess reports 3 comments

Introduction.

The Reminder Popup is a specially designed report that opens automatically, displays important content, and plays a background sound to draw the user's immediate attention. It is exported in Access Snapshot File Format (available in Microsoft Office 2000 and later), allowing it to open in a separate window independent of Microsoft Access.

Reports in Snapshot format can be viewed without Access, making them ideal for sharing via email or transporting as standalone files.

This reminder pop-up is useful for upcoming events—such as appointments, conferences, or birthdays—that fall within the next 7 days and require advance notice. The pop-up functionality is triggered through the Form_Timer() event procedure is coded in the Control Screen module.

The Demo Project.

Let’s try an example using the Employees table from the Northwind.mdb sample database. Begin by importing the Employees table into your current database.

If you're unsure about the location of the sample Northwind.mdb file, refer to the instructions provided on the page Saving Data on Forms Not in Table for guidance on locating it.

After importing the Employees table, create a new query using the SQL string provided below.
To do this, open a new query in Design View—when prompted, do not select any tables or queries from the list.

Instead, switch to the SQL View and paste the SQL string into the window.
Save the query with the name Birthday_Reminder.

SELECT Employees.EmployeeID,
 [TitleofCourtesy] & " " & [FirstName] & " " & [LastName] AS Name,
 Employees.BirthDate,
 DateValue(Format([birthdate],
 "dd-mm") & "-" & Year(Date())) AS BirthDay,
 DateDiff("yyyy",[birthdate],
 [BirthDay]) AS age
FROM Employees
WHERE (((DateValue(Format([birthdate],"dd-mm") & "-" & Year(Date()))) Between Date() And Date()+30));

Open the Birthday_Reminder query in Datasheet View to check if any records are returned based on the specified criteria.
If no records appear, open the Employees table and modify a few records by changing the Birth Date field so the month falls within the current month or within the next 30 days.

At the end of the query’s SQL string, you'll notice a condition like Date() + 30 used for testing.
You can adjust this value in Design View to suit your needs—for example, +7 or +15—depending on how many days in advance you want to be notified of upcoming birthdays or events.

Design a Report using Birthday_Reminder Query as Record Source, like the sample given below, and save the Report with the name Birthday_Reminder:

Click to Enlarge

Copy and paste the following Code into the VB Module of the Control Screen or the Main Screen of your Application.

'Global Declaration
Dim T As Long
'-------------------------------------------------------
Private Sub Form_Load()
Dim RCount

On Error GoTo Form_Load_Err
DoCmd.Restore

T = 0
RCount = DCount("*", "BirthDay_Reminder")
'if no records in BirthDay_Reminder Query then
'control is not passed into the Timer Event procedure

If RCount > 0 Then
    Me.TimerInterval = 250
End If

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description, , "Form_Load"
Resume Form_Load_Exit
End Sub


Private Sub Form_Timer()
On Error GoTo Form_Timer_Err

T = T + 1
Select Case T
    Case 20
        REMPOPUP
        'Me.Timerinterval = 0
    Case 260
        REMPOPUP
    Case 261
      T = 21
End Select

Form_Timer_Exit:
Exit Sub

Form_Timer_Err:
MsgBox Err.Description, , "Form_Timer"
Resume Form_Timer_Exit
End Sub

Private Function REMPOPUP()
Dim strPath As String, i As Integer, mplayerc As String
Dim mplayer As String, soundC As String
On Error GoTo REMPOPUP_Err

mplayerc = "C:\Program Files\Windows Media Player\mplayer2.exe " 'WindowsXP

soundC = "C:\Windows\Media\notify.wav" 'WindowsXP

'if media player2 not found then don't play sound
If Len(Dir(mplayerc)) > 0 Then
     mplayer = mplayerc & soundC
    Call Shell(mplayer, vbMinimizedNoFocus)
End If

strPath = "C:\Windows\Temp\BirthDay_Reminder.snp"

DoCmd.OutputTo acOutputReport, "BirthDay_Reminder", _"SnapshotFormat(*.snp)", strPath, True, ""

'if snapshot format is not available 
'disable the previous line and enable next line

'DoCmd.OpenReport "BirthDay_Reminder", acViewPreview

REMPOPUP_Exit:
Exit Function

REMPOPUP_Err:
MsgBox Err.Description, , "REMPOPUP"
Resume REMPOPUP_Exit
End Function

The Trial Run

When the main form containing the code is opened, the Form_Load() event procedure checks whether the Birthday_Reminder query returns any records.

If records are found, the Form_Timer() event is activated, running at an interval of 250 milliseconds ( quarter of a second), incrementing a globally declared variable T.

Once the value of T reaches 20 (after 5 seconds), the REMPOPUP() procedure is executed. This opens the Birthday_Reminder report in Access Snapshot Format. At the same time, Windows Media Player plays the notify.wav sound file to alert the user.

Click to Enlarge

After that, the Reminder Popup opens at hourly intervals. If this repetition is not required, then enable the statement (remove the ' character at the beginning).

Me.Timerinterval = 0

During the Form_Timer() event procedure, in that case, the Popup opens only once per Session.