Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Automated Email Alerts

Continued from previous Post: Configure Outlook for Lotus Notes.

The next procedure, that we are going to look into, is applicable for both Internet 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 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.

  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 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. 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 with 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 send 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 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 identification 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 mails with report attachment goes to the correct recipients and prevented others from getting the mail. But, here we will try the simple method.

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

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 mails 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. 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 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 at 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 lengthy 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 VB Function Environ() 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 by passed. 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 on 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.

With the above lines I hope our preparation story is all done and it is time to go for the VB Code. Copy the Main Program given below and paste it into a Global 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 Key stroke.

This will send the Lotus Notes password in advance and 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-folded shot and can miss the target, in that case Lotus Notes will demand for the password and you have no other choice but to key in manually and press Enter.

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

We will put few lines of code on 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 few clicks we can send an email with MS-Access Objects as attachment to the mail. Depending on the object selected for attachment the output format options available may be different. The attachment format for 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 our 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 to use its Contact List.

Your Application may be shared by several Users on different PCs but the mails should go from 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 Lotus Notes Administrator for this purpose.

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

  1. Your Lotus Notes ID File reference must be inserted into a 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 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 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, 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 Lotus Notes password to complete this option. The information Service Lotus Notes Transport will appear in the next window.

  9. Select 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 Options. . . of Tools Menu of MS-Outlook too.
  11. We have to define one more setting as how to store the Contact's Name in the Address Book, which we will do it from Outlook.
  12. Open Microsoft Outlook. Select Preferences from 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 says 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 mails from MS-Access we must present the email ID to Lotus Notes in the first format.

    Image of the Lotus Notes Mail ID look up control is given below for reference. I have edited the image to insert those names more familiar to you. Check the name displayed at 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 earlier session, using some of your friends Lotus Notes ID. During the mail transmitting point Lotus Notes will prompt for 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

It is unlikely that you will use Microsoft Access to send your personal e-mails through Internet, when 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 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 for Intranet Mails in the forthcoming Posts.

There are two versions of Microsoft Outlook installations. The normal one that we see on our PCs, for Internet 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 on the top area of the About Screen.

Let us explore few scenarios that warrants for 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 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.

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

  1. Open your Database and Click on a Report (avoid the Dynamic Report that we have created in the earlier session).
  2. Point your mouse on the Send To.. option in File Menu and Click on Mail Recipient (as Attachment).
  3. Select Snapshot Format from the displayed file formats menu.
  4. A dialogue control will open up asking 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 that 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.

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

If you are sending a regular Report as 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 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 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 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 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 Mail Transport to send e-mails within Local Area Network (LAN).

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts