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:

4 comments:

  1. [...] dbsTemp.Close End Sub There is a Blog Post that explains how to send Weekly e-mails automatically (Automated E-Mail Alerts) on schedule without User's intervention. This may give you ideas to write code for your [...]

    ReplyDelete
  2. Thank you for this honest blog post. I believe it's crucial for persons to remember this when they're commenting. I actually all web site comments, and am happy to get them. But, I always respond for the ones that take the time to accomplish it correct.

    ReplyDelete
  3. [...] output) to an e-mail that can be forwarded to you automatically. Refer this article for guidance: Automated Email Alerts __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete
  4. [...] users as a PDF file Thanks, Haffy311 Take a look at this link for a discussion on sending Automated e-mail Alerts. You can use the DoCmd.SendObject method as shown below to convert your Report into PDF format and [...]

    ReplyDelete

Comments subject to moderation before publishing.

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