Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

    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.

Share:

Configure Outlook for Lotus Notes

Continued from earlier Post: MS-Access and Emails

We have seen that sending EMails from MS-Access is easy enough like any other method you are familiar with. With a few clicks, we can send an email with MS-Access Objects as an attachment to the mail. Depending on the object selected for attachment the output format options available may be different. The attachment format for the VB Code module is Text format only and will be converted into this format before attaching it to the mail.

As I have pointed out in the earlier post that we can send Mail Alerts automatically on important matters, within the Local Area Network (LAN) through Email Transport Services, like Lotus Notes, from the Access Application.

But before we look into the mail sending procedure and Programs, we must configure MS Outlook on one of the PCs, for Lotus Notes and use its Contact List.

Access Mail Client

Your Application may be shared by several users on different PCs, but the mails should go from the only Machine, let us call it Access Mail Client. We need to configure the Access Mail Client Machine for Lotus Notes. Lotus Notes has its own Mail Server and Client installations on each PC. The Access Mail Client machine also must be installed with Lotus Notes Client and you must be using it for sending emails directly from Lotus Notes. You will have a Unique Lotus Notes ID File on your PC besides your own contact Lotus Notes Mail ID.

NB: If you don't want a personal User Id to appear as the Sender of the Mail Alerts from your Access Application then a special Lotus Notes Mail ID must be created and Lotus Notes Client must be installed with this ID on the Access Mail Client machine. You may seek the help of the Lotus Notes Administrator for this purpose.

Configuring MS Outlook with Lotus Notes.

We will look into the MS-Outlook configuration steps now with a few images and what it does.

  1. Your Lotus Notes ID File reference must be inserted into the control within Lotus Notes itself to be visible to the external Programs like MS Outlook to use the facilities provided by Lotus Notes. To do this, open Lotus Notes and Log in with your User Id and password. Select Edit Current Location from the Mobile Group menu of File Menu. The Location: Office (Network) control will be displayed.

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

  3. Click on the Text control to the right of the label User ID to switch to and click on the Search Light icon underneath the User ID label to browse to the location of your User ID file. The Path will be X:\Lotus\Notes\Data\filename.id, where X stands for the Lotus Notes installed Drive and the file name may have some similarity to your name depending on what name your Administrator has given with the file extension id. Find this file and insert it into the Text control. Your Lotus Notes email Id and the password are stored in this file. MS-Outlook needs this information to access the Contact List as well as to send and download the emails from Lotus Notes.

  4. Close the Control after inserting the Location reference and minimize Lotus Notes.

  5. Open Control Panel from Start Menu - - > Settings and double click on the Mail Icon to display the Control, with Services, Delivery & Addressing Tabs. See the image below. If your MS-Outlook installed version is not Corporate or Workgroup then the Options may look different. The images are from MS Office 2000 installation.

  6. Click on the Services Tab. The Profiles window will be empty if you have not configured for any other services earlier.

  7. Click on the Add Button to open up the Add Services to Profile list with several options. Select Lotus Notes Mail from the list and click to add it to the Profile window.

  8. Click on the Delivery Tab and select Lotus Notes Message Store in the control below. At this point, you may be prompted for the Lotus Notes password to complete this option. The information Service Lotus Notes Transport will appear in the next window.

  9. Select the Addressing Tab on the Control. You may select the Lotus Notes Main Address Book or your personal Lotus Notes Address Book with selected Addresses from the Main one and insert it in the appropriate controls. See the image below:

  10. Click Apply to update the settings. Click OK to close the window. You can configure the same settings from the Options of Tools Menu of MS-Outlook too.

  11. We have to define one more setting as to how to store the Contact's Name in the Address Book, which we will do from Outlook.

  12. Open Microsoft Outlook. Select Preferences from the Options of Tools menu.

  13. Click on the Contact Options' button.

  14. Select the Options in both controls as shown below and click OK.

    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 changed when it is inserted in the To line.

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

Share:

MS-Access and E-Mail

Introduction

It is unlikely that you will use Microsoft Access to send your personal emails via the Internet,  while so many other options are available to you.

But, when you need to share information from your Access Application with remotely located users regularly and you are sending printed Reports through conventional mail services then you can seriously think about the e-mail option.

If you are connected to a Local Area Network (LAN) you can distribute Reports to the recipients through Intranet Mail Service Applications like Lotus Notes.

For both Internet and Intranet mails to work Microsoft Outlook must be configured to act as a communication link between Microsoft Access and the Mail Transport; the SMTP server of your Internet Service Provider(ISP) or the Intranet Mail Transport, like Lotus Notes. If you are already using Microsoft Outlook for Sending and Receiving e-mails then it is already configured. MS-Outlook will maintain the Contact List from which we can select the prospective mail recipients.

NB: The procedure for configuring Microsoft Outlook is beyond the scope of this Article. You may refer to the Help topics of Microsoft Outlook or Help documents provided by your ISP for guidance. But, we will go through the procedure while configuring MS-Outlook for Lotus Notes Intranet Mails in the forthcoming Posts.

Outlook Versions

There are two versions of Microsoft Outlook installations. The normal one that we see on our PCs, is for Internet e-mails and the Intranet type for Corporate or Workgroups. You can check your installation version by displaying the About Microsoft Outlook Screen from the Help Menu. If it is a Corporate or Workgroup installation this exact label will appear in the top area of the About Screen.

Let us explore a few scenarios that warrant sending e-mails from Microsoft Access.

Your Company is a Service-oriented Organization, like a Bank, and you want to send Birthday Greetings to your Customers automatically or need to inform them of the revision of Interest Rates on Fixed Deposits.

OR

Your Company enters into Agency Agreements with other Parties, which is renewable periodically, and your Access Application should monitor and send Mail Alerts to the respective Departments well in advance so that they can act in time for renewal of Agency Agreements.

OR

Your company extends Credits to the Customers based on renewable Bank Guarantees and your Access Application should send mail alerts to the parties concerned for taking it up with the Customers well before renewals are due.

In all the above or similar scenarios, we can automate the Mail Alert procedure with the help of programs and we will look into a sample procedure for sending e-mails automatically through Lotus Notes, at the end of this series.

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.

  1. Open your Database and Click on a Report (avoid the Dynamic Report that we created in the earlier session).

  2. Point your mouse to the Send To option in File Menu and Click on Mail Recipient (as Attachment).

  3. Select Snapshot Format from the displayed options of the file-formats menu.

  4. A dialogue control will open up asking you to select the Mail Transport Profile already configured in Microsoft Outlook, or you can create a new profile.

  5. Select the default profile that you are using to send your e-mails. If you select the options and set this as the default Profile then this dialog control will not appear next time.

  6. When you click OK the e-mail Editor Screen will appear and you can modify your mail before sending it.

    See the image below.

  7. Click on the To Button to pick e-mail addresses from the Contacts List of Microsoft Outlook. The Cc: Button also works the same way.

  8. You can see the Report that we have selected is already converted into Microsoft Access Snapshot Format and attached to the Mail. Instead, if you prefer to send an external Object like Word or Excel File you can Click on the Attach. . . Button and select the file from your Hard Disk.

  9. The right-side blue-colored panel will not appear till you click on the Attachment Option Button that you see next to the blue-colored panel.

    Two options are provided and the default one is the Regular Attachment option, which sends individual copies of the attachment to each recipient. The second option is for Intranet Mails which will save the attachment in a common location where everybody can share the same file. You can ignore this Option.

  10. You can compose your mail in Plain Text and click the Send Button on the Toolbar above. Microsoft Outlook will take care of the rest.

Sample Mail Image

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

If you are sending a regular Report as an attachment with fixed wordings of Mail body text, then you can automate this action with a Macro. See the SendObject Command and its settings in the Macro image below:


The SendObject Command

The SendObject Command is selected in the Action Grid. The E-Mail Field values are filled-in in the Property Sheet of the SendObject command below. If you don't want to send an Access Object as an attachment, then you can leave the first three property values empty. If you set the Edit Message property to Yes then the E-Mail Client will open for editing as we did earlier, otherwise, the mail will be passed on to Microsoft Outlook, without appearing on the screen, for sending through the Internet.

You can attach this Macro to a Command Button or run the macro from VB Code using Docmd.Runmacro "MacroName". If you want to control the frequency of the e-mails with the Report attachment, then you must set up a procedure to cross-check the scheduled Date or Day before running the macro. We will look into such a program that sends mail automatically when it hits the weekly schedule, at the end of this series.

Next, we will explore the procedure that involves configuring Lotus Notes to use as a Mail Transport to send emails within the Local Area Network (LAN).

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code