Building Microsoft Access Applications

  • Article

 

This article is an excerpt from Building Microsoft Access Applications, from Microsoft Press (ISBN 0-7356-2039-3, copyright Microsoft Press 2005, all rights reserved). The author of the book is John Viescas.

John L. Viescas is the author of Microsoft Office Access 2003 Inside Out and the popular Running Microsoft Access books from Microsoft Press. He is president of Viescas Consulting, Inc., a respected provider of database application design and editorial consulting services. He has been recognized by Microsoft Product Support Services as a Most Valuable Professional (MVP) every year since 1993 for his contributions to the community of Access users.

No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means&#151electronic;, electrostatic, mechanical, photocopying, recording, or otherwise&#151without; the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Chapter 8: Sending Out Notices

  • Printing Notices
  • Sending Notices by E-Mail

In the previous chapter, you learned techniques for tracking member status and dues payments and reporting members whose dues have expired. But you probably don't want to wait until a member shows up at the next meeting to try to collect dues. As you've worked with the Membership Tracking database, you've probably noticed that it's easy to enter meeting agendas and announcement information for each meeting. But you also want to be to be able to send notices to members in advance of each meeting&#151either; via regular mail or by e-mail. This chapter shows you how to solve both of these problems.

Printing Notices

Today many people have computers and e-mail addresses. But for some types of organizations, a large percentage of the membership might not have a computer or an e-mail address. So, any membership application needs a way to print out notices that can be mailed to members.

Printing Dues Expiration Letters

To construct a letter to send to members whose dues have expired or are about to expire, you need much of the same information that you gathered for the Dues Expiration report in Chapter 7, "Checking Member Status," in the qryRptDuesExpire query. Because you're mailing a letter, you don't need the phone numbers included in that report. Also, you should include a parameter that obtains a date value from a form specifically designed for sending out notices. You can find a query designed for this purpose, qryRptDuesExpireLetter, in the Membership sample database (Membership.mdb). The SQL for the query is as follows:

PARAMETERS [Forms]![fdlgNoticeChoices]![txtDate] DateTime; 
tblMembers.MemberID, ([Title]+" ") & [FirstName] & " " & ([MiddleName]+" ") & [LastName] 
& (", "+[Suffix]) AS MemberName, tblMembers.LastName, tblMembers.FirstName, 
tblMembers.Nickname, NZ([NickName],[FirstName]) AS Salutation, tblMembers.MembershipStatus, 
tlkpMembershipStatus.DuesComp, Choose([DefaultAddress],[WorkAddress],[HomeAddress]) AS 
StreetAddr, Choose([DefaultAddress], [WorkCity] & ", " & [WorkStateOrProvince] & " " & 
[WorkPostalCode],[HomeCity] & ", " & [HomeStateOrProvince] & " " & [HomePostalCode]) AS CSZ, 
Replace(Mid([EmailName],InStr([EmailName],"MailTo:")+7),"#","") AS Email, 
qryDuesExpire.PaidUntil, [Forms]![fdlgNoticeChoices]![txtDate] AS ExpireBy 
FROM tlkpMembershipStatus  
INNER JOIN (tblMembers  
LEFT JOIN qryDuesExpire 
ON tblMembers.MemberID = qryDuesExpire.MemberID)  
ON tlkpMembershipStatus.MembershipStatus = tblMembers.MembershipStatus 
WHERE (((tlkpMembershipStatus.DuesComp)=False) AND ((qryDuesExpire.PaidUntil) Is Null Or 
(qryDuesExpire.PaidUntil)<[Forms]![fdlgNoticeChoices]![txtDate]));

Note   You don't need the e-mail address to mail a dues expiration letter, but you'll see later that I use this same query to send the notices by e-mail.

Tip   As you can see in the qryRptDuesExpireLetter query, the MemberName field concatenates the Title, FirstName, MiddleName, LastName, and Suffix fields and includes appropriate spacing and punctuation. However, many member records might not have a Title, MiddleName, or Suffix. In a string expression in Access, you can use either the & operator or the + operator to concatenate strings. The difference is the & operator ignores Null values and returns all the non-Null values concatenated together. The + operator, however, returns nothing if any part of the expression is Null&#151also; known as Null propagation. The expression for the MemberName field takes advantage of Null propagation provided by the + operator to eliminate the space after Title and MiddleName if either is Null and to eliminate the comma and space before Suffix if that is Null.

Remember that the qryDuesExpire query returns the latest dues expiration date for each member, but some members (such as those who have attended meetings only as a guest) might have never paid dues at all. This is why you must include not only records with an expiration date earlier than the parameter date but also records that have no expiration date (Is Null).

In each letter, it would also be nice to include a list of the current renewal options so that the member can choose a dues amount and related renewal extension length. You might remember from Chapter 7 that the qryCurrentDuesRates query returns the current rate for each different renewal interval. You can combine that query with the records from the qryDuesExpire query to calculate a new expiration date for each interval for each member. If the member has never paid dues, you can display an informative message about the expiration of any first-time payment of dues. You can find the qryRsubDuesExpireLetter query that fetches these rows in the Membership sample database. The SQL for the query is as follows:

SELECT qryDuesExpire.MemberID, [DuesInterval] & " months" AS [Renew For], 
qryCurrentDuesRates.DuesAmt, NZ(DateAdd("m",[DuesInterval],[PaidUntil]),[DuesInterval] & " 
months from date paid.") AS [Renew Until] 
FROM qryDuesExpire, qryCurrentDuesRates 
WHERE NZ(DateAdd("m",[DuesInterval],[PaidUntil]),Date())>=Date() 
ORDER BY qryDuesExpire.MemberID, qryCurrentDuesRates.DuesInterval;

The query uses the DateAdd function to add the number of months specified in the DuesInterval field from the qryCurrentDuesRates query to the member's last expiration date. When the member has never paid dues, the DateAdd function returns a Null value. The NZ function examines that value, and if it is Null, substitutes a message that dues will expire [DuesInterval] months from the date the member first pays dues. The query also eliminates any rows where adding the renewal interval to the last paid until date would result in a date in the past.

Notice that there is no join specification in the From clause of this query. This query matches each row from the qryDuesExpire query with each row in the qryCurrentDuesRates query. You know that the qryDuesExpire query returns exactly one row per member, and the qryCurrentDuesRates query returns one row per available renewal interval. When you use this query as the record source for a subreport in a letter for each member, you can link the subreport on the MemberID field to display the renewal options pertinent for each member.

As with most reports, designing the query that the report needs is the hard part. The design of the rsubDuesExpireLetter report is quite simple. It displays the Renew For, DuesAmt, and Renew Until fields calculated by the qryRsubDuesExpireLetter query. Figure 8-1 shows you the design of this report that is embedded in the final report that produces the letter.

The rsubDuesExpireLetter report that is used to list dues renewal options for each member (Click picture to view larger image)

Figure 8-1. The rsubDuesExpireLetter report that is used to list dues renewal options for each member (Click picture to view larger image)

The report to print the letter (rptDuesExpireLetter) is fairly straightforward. Figure 8-2 shows you the design of this report.

The salutation line uses an expression that includes the Salutation field calculated in the query (use the Nickname field unless it is null; otherwise use the FirstName field). The expression is as follows:

="Dear " & [Salutation] & ":"

Code that executes when the report opens uses the qryCurrentTreasurer query to fetch the name of the member who is the treasurer as of the date you print the report. It also loads the two template messages stored in the ztblDuesMessages table (one for new members, and one for current members) into module variables. The SQL for the qryCurrentTreasurer query is as follows:

SELECT tblOfficers.OfficerType, tblOfficers.ServedTo, [FirstName] & " " & ([MiddleName]+" ") 
& [LastName] & (", "+[Suffix]) AS MemberName 
FROM tblMembers  
INNER JOIN tblOfficers  
ON tblMembers.MemberID = tblOfficers.MemberID 
WHERE (((tblOfficers.OfficerType)="Treasurer") AND ((tblOfficers.ServedTo) Is Null Or 
(tblOfficers.ServedTo)>=Date()));

The rptDuesExpireLetter report in Design view (Click picture to view larger image)

Figure 8-2. The rptDuesExpireLetter report in Design view (Click picture to view larger image)

The Format event procedure for the Detail section creates the text of the letter and assigns that text to the unbound text box control at the top of the Detail section. This text box control has its Can Grow property set to Yes, so the text box expands to display all the text when you view or print the report. Code in that event procedure also inserts the treasurer name in the unbound text box that you see below the subreport control. The code in the Format event of the Detail section that inserts both the text of the letter and the treasurer name is as follows:

Option Compare Database
Option Explicit 
' Place to save treasurer name in Open 
Dim strTreasurer As String 
' Place to save the new member message 
Dim strNewMemberMsg As String 
' Place to save the expired dues message 
Dim strDuesExpireMsg As String 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
Dim strExpire As String 
    ' Set the treasurer name 
    Me.txtTreasurer = strTreasurer 
    ' If a Guest or never paid dues 
    If (Me.MembershipStatus = "Guest") Or _ 
      IsNull(Me.PaidUntil) Then 
      ' Make letter an offer to join 
      Me.txtMsg = strNewMemberMsg 
    Else 
      ' If already expired 
      If Me.PaidUntil < Date Then 
        ' Set expired on 
        strExpire = "Your membership expired on " & _ 
          Format(Me.PaidUntil, "mmmm d, yyyy") 
      Else 
        ' Set will expire 
        strExpire = "Your membership expires on " & _ 
          Format(Me.PaidUntil, "mmmm d, yyyy") 
      End If 
      ' Set the message 
      Me.txtMsg = strExpire & ".  " & strDuesExpireMsg 
    End If 
End Sub

The module variables strTreasurer, strNewMemberMsg, and strDuesExpireMsg are set by code in the report Open event procedure. The code first opens a query that returns the current treasurer name based on today's date. The code then opens the ztblDuesMessages table that contains the text to be displayed for current and new members. Using a table in this way allows the user to modify the messages printed in the dues expiration report without having to change the report design. The code is as follows:

Private Sub Report_Open(Cancel As Integer) 
Dim rst As DAO.Recordset 
 ' Get the current Treasurer name 
 Set rst = DBEngine(0)(0).OpenRecordset("qryCurrentTreasurer") 
 If Not rst.EOF Then 
   strTreasurer = rst!MemberName 
 End If 
 ' Close out 
 rst.Close 
 ' Now open the template table 
 Set rst = DBEngine(0)(0).OpenRecordset("ztblDuesMessages") 
 ' Process the rows 
 Do Until rst.EOF 
   ' Decide which template we're loading 
   Select Case rst!Template 
     ' New member 
     Case "New Member" 
       ' Save the template 
       strNewMemberMsg = rst!TemplateText 
     ' Existing member 
     Case "Current Member" 
       ' Save the template 
       strDuesExpireMsg = rst!TemplateText 
   End Select 
   ' Get the next row 
   rst.MoveNext 
 Loop 
 ' Close out 
 rst.Close 
 Set rst = Nothing 
End Sub

You can run this report by starting the application (open the frmSplash form) and clicking the Notices button on the main switchboard. The application opens the fdlgNoticeChoices form&#151remember;, this form is referenced in the record source of the report. Select Dues Expiration Notice, choose a date, select Print Notices, and click Go, as shown in Figure 8-3, to see the report.

Running the Dues Expiration Notice report

Figure 8-3. Running the Dues Expiration Notice report

The code that responds to clicking the command button is simple. It examines the options you picked and either opens the appropriate report or calls a procedure to generate e-mails.

Printing Meeting Announcements

The Pacific Northwest Access Developers Group sends out regular notices about upcoming meetings. The group secretary will often include various special announcements as well as the actual agenda of the meeting. Some of these announcements should display or print before the agenda items (such as a notice about a meeting location change or special topic that should receive special attention), and some of the announcements should print after the agenda items at the end of the notice (such as a thank you to a sponsor who has provided door prizes).

You can see how the secretary enters agenda items and announcements for a meeting by opening the frmMeetings form in the Membership application. Figure 8-4 shows you the agenda items for one of the meetings, and Figure 8-5 shows you the announcements.

Note that the announcements subform lets the secretary specify whether the announcement is to appear before or after the agenda items. The Before / After combo box actually stores the number 0 for Before and the number 2 for After.

Entering agenda items for a meeting (Click picture to view larger image)

Figure 8-4. Entering agenda items for a meeting (Click picture to view larger image)

Entering announcements for a meeting (Click picture to view larger image)

Figure 8-5. Entering announcements for a meeting (Click picture to view larger image)

To produce all the rows you need for a meeting announcement in the correct order, you can use a union query to select the before rows for announcements, the agenda items, and the after rows for announcements. To put the rows in the correct sequence, you can generate a number literal with a value of 1 to put the agenda rows in the middle. (Now you know why before announcements are 0 and after announcements are 2!) The query that performs this task is qryRptMeetingAnnouncement, and the SQL for the query is as shown here:

SELECT tblMeetings.MeetingID, tblMeetings.MeetingDate, tblMeetings.MeetingLocation, 
tblMeetings.CommitteeName, tblMeetings.MeetingDescription, 
tblMeetingAnnouncements.AnnounceNo, tblMeetingAnnouncements.AnnounceTitle, 
tblMeetingAnnouncements.AnnounceDescription, tblMeetingAnnouncements.BeforeAfter 
FROM tblMeetings  
INNER JOIN tblMeetingAnnouncements  
ON tblMeetings.MeetingID = tblMeetingAnnouncements.MeetingID 
WHERE tblMeetingAnnouncements.BeforeAfter=0 
UNION ALL
SELECT tblMeetings.MeetingID, tblMeetings.MeetingDate, tblMeetings.MeetingLocation, 
tblMeetings.CommitteeName, tblMeetings.MeetingDescription, tblMeetingAgenda.TopicNo, 
tblMeetingAgenda.TopicTitle, tblMeetingAgenda.TopicDescription, 1 AS BeforeAfter 
FROM tblMeetings  
INNER JOIN tblMeetingAgenda 
ON tblMeetings.MeetingID = tblMeetingAgenda.MeetingID 
UNION ALL 
SELECT tblMeetings.MeetingID, tblMeetings.MeetingDate, tblMeetings.MeetingLocation, 
tblMeetings.CommitteeName, tblMeetings.MeetingDescription, 
tblMeetingAnnouncements.AnnounceNo, tblMeetingAnnouncements.AnnounceTitle, 
tblMeetingAnnouncements.AnnounceDescription, tblMeetingAnnouncements.BeforeAfter 
FROM tblMeetings  
INNER JOIN tblMeetingAnnouncements  
ON tblMeetings.MeetingID = tblMeetingAnnouncements.MeetingID 
WHERE tblMeetingAnnouncements.BeforeAfter=2 
ORDER BY MeetingID, BeforeAfter, AnnounceNo;

Note   You might be tempted to create a report that uses a query on the tblMeetings table and uses subreports for the records from the tblMeetingAgenda table and the tblMeeting-Announcements table. However, when a subreport generates more data rows than will fit on a page, producing headers at the top of subsequent pages will be difficult. It's much easier to create a single recordset that joins the tblMeetings table with either the tblMeetingAgenda table or the tblMeetingAnnouncements table and not use subreports.

Because all the work is done by the query, the report layout is simple. Figure 8-6 shows you the design of the rptMeetingAnnouncement report.

The MeetingID Header section has the Force New Page property set to Before Section, and the Repeat Section property set to Yes. The AnnounceNo Header section also has its Repeat Section property set to Yes so that all the details of the meeting and the title of each topic will print again at the top of a new page when the announcement or agenda item description overflows a page.

You can print a meeting announcement either by clicking the Notices button on the main switchboard or by opening the meeting you want and clicking the Print/Send button (as shown in Figure 8-4) on the Meetings form. In either case, you'll see the fdlgNoticeChoices form shown earlier in Figure 8-3, but you'll also see a combo box to select the meeting to print when you select the Meeting Notice option. Figure 8-7 shows you the first part of the announcement for the October 2004 general meeting.

The design of the rptMeetingAnnouncement report (Click picture to view larger image)

Figure 8-6. The design of the rptMeetingAnnouncement report (Click picture to view larger image)

The Meeting Announcement report (Click picture to view larger image)

Figure 8-7. The Meeting Announcement report (Click picture to view larger image)

Sending Notices by E-Mail

You might think that mailing notices is old technology in this modern era of the Internet and e-mail, and you might be right. The good news is that designing the two reports to send out dues expiration notices and meeting announcements has laid the groundwork for creating e-mails that serve the same function.

To make your e-mails look their best, you should format them as HTML so that you can use various fonts and include graphics. You could painstakingly write the HTML code you need in the procedure that formats and sends your e-mails. Or, you could use an HTML editor such as Microsoft FrontPage to design a template and then store the pieces of the template you need in a table. I chose the latter method, and you can find the parts of the templates used in the next two sections saved in the ztblHTMLTemplates table.

Sending Dues Expiration Notices by E-Mail

Because I decided to use templates to build the HTML, I first built a sample Web page for the dues expiration messages. You can find this template file in the sample files saved as DuesExpireTemplate.html. Figure 8-8 shows you the template displayed in Microsoft Internet Explorer.

The template for dues expiration e-mail messages (Click picture to view larger image)

Figure 8-8. The template for dues expiration e-mail messages (Click picture to view larger image)

You can see that I embedded keywords in the HTML ([Today], [Nickname], [ExpireMsg], and so on) to make it easy to customize each message. You can find the template broken into three pieces as records in the ztblHTMLTemplates table&#151the; top part of the message including the Nickname and ExpireMsg keywords, the renewal option line that might appear several times, and the signature lines.

You can try this out by starting the application (open the frmSplash form), clicking Notices on the main switchboard, selecting Dues Expiration Notice in the Notification Choices dialog box, selecting an expiration date, selecting the option to Email Notices (the default), and clicking the Go button. You might see several e-mails generated, but unless you've entered dues payments to bring all members up to date, you should see at least one. Because all the e-mail addresses in the database are fictitious, the code in the application merely displays the e-mail messages rather than sending them. Figure 8-9 shows you a formatted message.

An e-mail sent by the dues expired notification procedure (Click picture to view larger image)

Figure 8-9. An e-mail sent by the dues expired notification procedure (Click picture to view larger image)

Although I set up template records in advance to make assembling the e-mail message easier, the code that performs this task in the fdlgNoticeChoices form isn't simple at all. The code is in the DuesEmail function in the form's module, and that function is called from the procedure that responds to clicking the Go button when you select the Dues Expiration Notice and Email Notices options. The first part of the function gets the current treasurer's name and the two different dues messages&#151similar; to the code in the rptDuesExpireLetter report you saw earlier. It borrows the query for that report to find out all the members whose dues expire by the date specified on the form. Note that to open a parameter query in code, you must first open the QueryDef object, set the parameter, and then open a recordset on the QueryDef. When the code finds no members with dues expiring by the specified date, it displays a message and exits. The code is as follows:

Private Function DuesEmail() As Integer 
Dim db As DAO.Database, qd As DAO.QueryDef, rstRenew As DAO.Recordset
Dim rstTemplate As DAO.Recordset, rstData As DAO.Recordset 
Dim strTo As String, strTitle As String, strHTML As String 
Dim strWork As String, strTreasurerName As String 
 ' Set an error trap 
 On Error GoTo DuesEmail_Err 
 ' Point to this database 
 Set db = DBEngine(0)(0) 
 ' Get the current Treasurer name 
 Set rstData = db.OpenRecordset("qryCurrentTreasurer") 
 If Not rstData.EOF Then 
   strTreasurerName = rstData!MemberName 
 End If 
 ' Close out 
 rstData.Close 
 ' Now open the dues messages table 
 Set rstData = db.OpenRecordset("ztblDuesMessages") 
 ' Process the rows 
 Do Until rstData.EOF 
   ' Decide which template we're loading 
   Select Case rstData!Template 
     ' New member 
     Case "New Member" 
       ' Save the template 
       strNewMemberMsg = rstData!TemplateText 
     ' Existing member 
     Case "Current Member" 
       ' Save the template 
       strDuesExpireMsg = rstData!TemplateText 
   End Select 
   ' Get the next row 
   rstData.MoveNext 
 Loop 
 ' Close out 
 rstData.Close 
 ' Get the query that the report uses 
 Set qd = db.QueryDefs("qryRptDuesExpireLetter") 
 ' Set its parameter 
 qd.Parameters(0) = Me.txtDate 
 ' Open the recordset 
 Set rstData = qd.OpenRecordset 
 ' See if any records 
 If rstData.RecordCount = 0 Then 
   ' Tell the user 
   MsgBox "No member dues expire before the date you specified.", _ 
     vbInformation, gstrAppTitle 
   ' Close out 
   rstData.Close 
   Set rstData = Nothing 
   Set qd = Nothing 
   Set db = Nothing 
   ' Bail 
   Exit Function 
 End If

Next, the code opens the HTML template records for the dues expiration notice. This code expects the template to be broken into three parts:

  1. A header with the logo, address, date, person name, message body, and the heading of the dues renewal table.
  2. The dues renewal line, which the code might use multiple times.
  3. The footer that contains the treasurer name.

The code processes each member's dues expiration record (for members whose dues expire by the date you specified) by fetching the first part of the template and filling in the date, person name, and expiration message. Notice that the Replace function works well to find the template keywords and replace them with the actual text. The code is as follows:

 ' Open the HTML template for email 
 Set rstTemplate = db.OpenRecordset( _ 
   "SELECT * FROM ztblHTMLTemplates " & _ 
   "WHERE Template = 'Dues' " & _ 
   "ORDER By TemplateSeq") 
 ' Process the members one at a time 
 Do Until rstData.EOF 
   ' Make sure we're at the start of the template records 
   rstTemplate.MoveFirst 
   ' Grab the first part 
   strWork = rstTemplate!TemplateHTML 
   ' Put in today's date 
   strWork = Replace(strWork, "[Today]", Format(Date, "mmmm d, yyyy")) 
   ' Put in the person's first name or nickname 
   strWork = Replace(strWork, "[Nickname]", rstData!Salutation) 
   ' If a Guest or never paid dues 
   If (rstData!MembershipStatus = "Guest") Or _ 
     IsNull(rstData!PaidUntil) Then 
     ' Make letter an offer to join 
     strMsg = strNewMemberMsg 
   Else 
     ' If already expired 
     If rstData!PaidUntil < Date Then 
       ' Set expired on 
       strExpire = "Your membership expired on " & _ 
         Format(rstData!PaidUntil, "mmmm d, yyyy") 
     Else 
       ' Set will expire 
       strExpire = "Your membership expires on " & _ 
         Format(rstData!PaidUntil, "mmmm d, yyyy") 
     End If 
     ' Set the message 
     strMsg = strExpire & ".  " & strDuesExpireMsg 
   End If 
   ' Replace the message in the HTML 
   strWork = Replace(strWork, "[ExpireMsg]", strMsg) 
   ' Stuff what we have so far in the HTML variable 
   strHTML = strWork

Next, the code gets the second part of the template to fill in the renewal option lines. The code opens the renewal options recordset for this member, using the same query that is also the record source for the rsubDuesExpireLetter subreport. Using the template, it fills in the Renew For, Dues, and Renew Until values and adds each row one at a time to the HTML built to this point. The code is as follows:

   ' Move to the next template row 
   rstTemplate.MoveNext 
   ' Open a recordset on the renew options for this person 
   Set rstRenew = db.OpenRecordset("SELECT * FROM " & _ 
     "qryRsubDuesExpireLetter " & _ 
     "WHERE MemberID = " & rstData!MemberID) 
   ' Loop and insert the options 
   Do Until rstRenew.EOF 
     ' Get the template for the expire option rows 
     strWork = rstTemplate!TemplateHTML 
     ' Put in Renew For months 
     strWork = Replace(strWork, "[RenewFor]", rstRenew![Renew For]) 
     ' Put in Dues 
     strWork = Replace(strWork, "[Dues]", _ 
       Format(rstRenew!DuesAmt, "Currency")) 
     ' Put in new expire date 
     strWork = Replace(strWork, "[RenewUntil]", rstRenew![Renew Until]) 
     ' Add to the HTML 
     strHTML = strHTML & strWork 
     ' Get the next renewal option 
     rstRenew.MoveNext 
   Loop 
   ' Close the renewal recordset 
   rstRenew.Close 
   Set rstRenew = Nothing

Finally, the code gets the last part of the template for this member's message, fills in the treasurer name, and calls the SendOutlookMsg function to send the message on its way. The code is as follows:

   ' Get the last piece of the template 
   rstTemplate.MoveNext 
   ' Get the HTML 
   strWork = rstTemplate!TemplateHTML 
   ' "sign" the letter 
   strWork = Replace(strWork, "[Treasurer]", strTreasurerName) 
   ' Finish the HTML 
   strHTML = strHTML & strWork 
   ' Send the email 
   If Not (SendOutlookMsg("Notice of Dues Expiration", _ 
     rstData!FirstName & " " & rstData!LastName & _ 
     "<" & rstData!Email & ">", strHTML)) Then 
     ' Got failure - tell the user 
     MsgBox "Notice to " & rstData!FirstName & " " & _ 
       rstData!LastName & " failed.", vbCritical, gstrAppTitle 
   End If 
   ' Move to the next member record 
   rstData.MoveNext 
 Loop 
 ' Close the member data and template 
 rstData.Close 
 Set rstData = Nothing 
 rstTemplate.Close 
 Set rstTemplate = Nothing 
 Set qd = Nothing 
 Set db = Nothing 
 ' All worked - return success 
 DuesEmail = True 
 DuesEmail_Exit: 
  Exit Function 
 DuesEmail_Err: 
  ' Tell user about an error 
  MsgBox "Unexpected error: " & Err & ", " & Error, _ 
    vbCritical, gstrAppTitle 
 ' Bail 
 Resume DuesEmail_Exit Function
End Function

You can find the code for the SendOutlookMsg function in the modOutlook module. If you look at the references for the Membership project (open the Visual Basic Editor and choose References from the Tools menu), you'll find that I did not include a reference to the Outlook library. I did this so you can run this application on any machine that has Microsoft Outlook 2000, 2002, or 2003 installed. If I referenced a specific library, none of the code would run on a machine that had another version installed because the project would have a library reference error.

To make this code work, I used a coding technique called late binding. Rather than declare objects from the Outlook library, I declared the objects I need simply As Object. The code uses the CreateObject function to start a copy of Outlook and get a reference to its Application object. The code then uses the CreateItem method of the Application object to create a new e-mail message, set its Subject property, the To or BCC property, and the HTMLBody property using the parameters passed to the function. Finally, the code executes the Display method to show you the result. The code is as follows:

Option Compare Database 
Option Explicit 
Const olMailItem = 0 
Public Function SendOutlookMsg(strSubject As String, strTo As String, _ 
   strHTML As String, Optional intUseBCC As Integer = 0) As Integer 
' Function to send an email message using Outlook 
' Inputs: Subject of the message 
'         List of valid "To" email addresses 
'         HTML for the body of the message 
'         Send using BCC flag (optional) 
' Output: True if successful 
' Note: This demo version only formats and displays a new 
'       message.  Use ObjMail.Send instead of .Display 
'       to actually send the message 
Dim objOL As Object, objMail As Object 
 ' Set an error trap 
 On Error GoTo SendOutlookMsg_Err 
 ' Get a pointer to Outlook - late binding 
 Set objOL = CreateObject("Outlook.Application") 
 ' Create a new email 
 Set objMail = objOL.CreateItem(olMailItem) 
 ' Set the subject 
 objMail.Subject = strSubject 
 ' Set To or BCC 
 If intUseBCC = True Then 
   objMail.BCC = strTo 
 Else 
   objMail.To = strTo 
 End If 
 ' Insert the HTML of the message 
 objMail.HTMLBody = strHTML 
 ' Display it 
 objMail.Display 
 ' Done - clear objects 
 Set objMail = Nothing 
 Set objOL = Nothing 
 ' Return true 
 SendOutlookMsg = True 
SendOutlookMsg_Exit: 
 Exit Function 
SendOutlookMsg_Err: 
 ' Log the error 
 ErrorLog "SendOutlookMsg", Err, Error 
 ' Bail 
 Resume SendOutlookMsg_Exit
End Function

Tip   If you look carefully at the templates I created for the HTML code builds, you'll notice that the code embeds the logo by making a reference to a graphics file on my Web site. The HTML code is as follows:

<img src="http://www.viescas.com/PNWADGLogoSmall.gif" width="89" height="94">

Although you can include an image directly in an HTML format e-mail message from the user interface and send the graphic embedded in the message, doing so from code is extremely tricky. You must first add the graphic as an attachment and then establish a Messaging Application Programming Interface (MAPI) session for the mail item so that you can change the attributes of the attachment to mark it embedded and create the Content ID (CID) you need to be able to reference the image within your HTML. Unfortunately, some of the methods you must use in the MAPI session are undocumented, and I'm always loath to recommend such techniques. You can read more about this technique at fellow Outlook MVP Sue Mosher's Web site: http://www.outlookcode.com/d/code/htmlimg.htm.

Sending a Meeting Announcement by E-Mail

As you might suspect, I also used an HTML template to construct a meeting announcement. You can find this template file saved as MeetingAnnouncement-Template.html in the sample files. Figure 8-10 shows you the template displayed in Microsoft Internet Explorer.

You can see that I embedded keywords in the HTML ([MeetingDate], [Committee], [MeetingDescription], and so on) to make it easy to customize the message. You can try this out by opening the frmMeetings form, selecting one of the general meetings (they are more interesting), clicking the Print/Send button, and clicking the Go button in the Notification Choices dialog box. As noted earlier, because all the e-mail addresses in the database are fictitious, the code in the application merely displays the e-mail message rather than sending it. For meetings, the code also places the e-mail addresses on the Bcc line of the message. Figure 8-11 shows you a formatted meeting announcement. You can click on any of the topic lines to jump to that topic or announcement because the code generates internal hyperlinks to bookmarks.

The template for meeting announcement messages (Click picture to view larger image)

Figure 8-10. The template for meeting announcement messages (Click picture to view larger image)

A meeting notice ready to send via e-mail (Click picture to view larger image)

Figure 8-11. A meeting notice ready to send via e-mail (Click picture to view larger image)

In some ways, the code to assemble this message is somewhat less complex because it has to generate only one message to multiple recipients. However, the code to assemble the topic index and the topics is trickier. You can find the code in the AnnouncementEmail function in the code module for the fdlgNoticeChoices form. The code begins by opening a recordset on the same union query used by the rptMeetingAnnouncement report, but filtered for the MeetingID that you selected on the form. If the recordset contains no records, the code tells the user and exits. The code is as follows:

Private Function AnnouncementEmail() As Integer
Dim db As DAO.Database, rstAnnounce As DAO.Recordset
Dim rstTemplate As DAO.Recordset, rstData As DAO.Recordset 
Dim strTo As String, strTitle As String, strHTML As String 
Dim strWork As String, strBody As String, strTopics As String 
Dim strTopicIndexTemp As String, strTopicTemp As String, strFootTemp As String 
Dim intTopicNo As Integer, datMtgDate As Date 
 ' Set an error trap 
 On Error GoTo AnnounceEmail_Err 
 ' Point to this database 
 Set db = DBEngine(0)(0) 
 ' Open the query that the report uses 
 ' -- filtered on the meeting selected 
 Set rstAnnounce = db.OpenRecordset("SELECT * " & _ 
   "FROM qryRptMeetingAnnouncement WHERE MeetingID = " & Me.cmbMeeting) 
 ' See if any records 
 If rstAnnounce.RecordCount = 0 Then 
   ' Tell the user 
   MsgBox "The meeting you selected has no announcement " & _ 
     "or agenda records.", vbInformation, gstrAppTitle 
   ' Close out 
   rstAnnounce.Close 
   Set rstAnnounce = Nothing 
   Set db = Nothing 
   ' Bail 
   Exit Function 
 End If

Next, the code assembles a list of recipients. If the meeting is for a committee, the code uses the qryAnnounceEmailCommittee query to fetch the current members of the committee. If the meeting is a general meeting, the code uses the qryAnnounceEmail query to get a list of all members except those with Inactive status. The code is as follows:

 ' Get the list of members 
 ' If a committee meeting, 
 If Not IsNothing(rstAnnounce!CommitteeName) Then 
   ' Get the list of members on this committee 
   Set rstData = db.OpenRecordset( _ 
     "SELECT * FROM qryAnnounceEmailCommittee " & _ 
     "WHERE CommitteeName = '" & rstAnnounce!CommitteeName & _ 
     "' AND ((DateLeft Is Null) Or (DateLeft > #" & _ 
     rstAnnounce!MeetingDate & "#))") 
   ' Make sure we have some 
   If rstData.RecordCount = 0 Then 
     ' Ask if they want to send to entire list or bail 
     If vbYes = MsgBox("There are no members currently assigned " & _ 
       "to the " & rstAnnounce!CommitteeName & " Committee.  Do you " & _ 
       "want to send an announcement to all members?", _ 
       vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then 
       ' Close this one 
       rstData.Close 
       ' Open for all members 
       Set rstData = db.OpenRecordset("qryAnnounceEmail") 
     Else 
       ' Close out 
       rstData.Close 
       Set rstData = Nothing 
       rstAnnounce.Close 
       Set rstAnnounce = Nothing 
       Set db = Nothing 
       ' Bail 
       Exit Function 
     End If 
   End If 
 Else 
   ' Open a recordset on all active members 
   Set rstData = db.OpenRecordset("qryAnnounceEmail") 
 End If 
 ' Build the "To" list 
 Do Until rstData.EOF 
   ' Add an email name 
   strTo = strTo & rstData!FirstName & " " & _ 
     rstData!LastName & _ 
     "<" & rstData!Email & ">" & ";" 
   ' Get the next record 
   rstData.MoveNext 
 Loop 
 ' Close the recordset 
 rstData.Close 
 Set rstData = Nothing

Next, the code opens the HTML template records for the meeting announcement. This code expects the template to be broken into four parts:

  1. A header with the logo, meeting date and time, committee name, meeting description, meeting location, and the header of the topics list.
  2. The topics index line.
  3. The topic title and full description, which the code uses once per topic.
  4. The footer to close out the HTML.

The code gets the header and replaces all the relevant template marker fields with the data from the first row in the announcement recordset. The code is as follows:

 ' Open the HTML template for email 
 Set rstTemplate = db.OpenRecordset( _ 
   "SELECT * FROM ztblHTMLTemplates " & _ 
   "WHERE Template = 'Announcement' " & _ 
   "ORDER By TemplateSeq") 
 ' The first record has the header - copy it 
 strHTML = rstTemplate!TemplateHTML 
 ' Insert the date 
 strHTML = Replace(strHTML, "[MeetingDate]", _ 
   Format(rstAnnounce!MeetingDate, "mmmm dd, yyyy h:nnampm")) 
 ' Insert the Committee, if any 
 strHTML = Replace(strHTML, "[Committee]", _ 
   Nz(("Committee: " + rstAnnounce!CommitteeName), "")) 
 ' Add the meeting description 
 strHTML = Replace(strHTML, "[MeetingDescription]", _ 
   rstAnnounce!MeetingDescription) 
 ' Finally, do the location 
 strHTML = Replace(strHTML, "[MeetingLocation]", _ 
   rstAnnounce!MeetingLocation) 
 ' Save the meeting date 
 datMtgDate = rstAnnounce!MeetingDate

Next, the code gets the topic index and topic detail templates. It then loops through all the topics in the announcement recordset, adding a line to the topic index (which will appear in a block at the beginning of the message), and a block to the topic body for each row (which all appear after the topic index block). As it adds each topic, it increments a topic number counter so that it can set up unique bookmarks and hyperlinks within the HTML. The code is as follows:

 ' Load the rest of the template text 
 rstTemplate.MoveNext 
 ' Record 2 is the Topic Index Template 
 strTopicIndexTemp = rstTemplate!TemplateHTML 
 rstTemplate.MoveNext 
 ' Record 3 is the Topic Detail Template 
 strTopicTemp = rstTemplate!TemplateHTML 
 rstTemplate.MoveNext 
 ' Record 4 is the footer 
 strFootTemp = rstTemplate!TemplateHTML 
 ' Close the template recordset 
 rstTemplate.Close 
 Set rstTemplate = Nothing 
 ' Now process all the topics, building indexes as we go 
 Do Until rstAnnounce.EOF 
   ' Build the index first - add 1 to counter 
   intTopicNo = intTopicNo + 1 
   ' Get the index template - insert the link key 
   strWork = Replace(strTopicIndexTemp, "[TopicKey]", _ 
     "Topic" & intTopicNo) 
   ' Insert the Topic Title 
   strWork = Replace(strWork, "[AnnounceTitle]", _ 
     rstAnnounce!AnnounceTitle) 
   ' Add it to the existing topics 
   strTopics = strTopics & strWork 
   ' Now, do the topic body - insert link key 
   strWork = Replace(strTopicTemp, "[TopicKey]", _ 
     "Topic" & intTopicNo) 
   ' Insert the Topic Title 
   strWork = Replace(strWork, "[AnnounceTitle]", _ 
     rstAnnounce!AnnounceTitle) 
   ' Insert the topic detailed description 
   strWork = Replace(strWork, "[AnnounceDescription]", _ 
     rstAnnounce!AnnounceDescription) 
   ' Add it to the existing topic body 
   strBody = strBody & strWork 
   ' Get the next record 
   rstAnnounce.MoveNext 
 Loop 
 ' Close the recordset 
 rstAnnounce.Close 
 Set rstAnnounce = Nothing 
 Set db = Nothing

Finally, the code assembles the pieces of the message and calls the SendOutlookMsg function to send the message. The code is as follows:

 ' Got the pieces built, now assemble them 
 strHTML = strHTML & strTopics & strBody & strFootTemp 
 ' Send the email 
 If Not (SendOutlookMsg("PNWADG - " & Format(datMtgDate, "Long Date") & _ 
   " Meeting Announcement", _ 
   strTo, strHTML, True)) Then 
   ' Got failure - tell the user 
   MsgBox "Sending meeting notice failed.", vbCritical, gstrAppTitle 
 End If 
 ' All worked - return success 
 AnnouncementEmail = True 
AnnounceEmail_Exit: 
 Exit Function 
AnnounceEmail_Err: 
 ' Tell user about an error 
 MsgBox "Unexpected error: " & Err & ", " & Error, _ 
   vbCritical, gstrAppTitle 
 ' Bail 
 Resume AnnounceEmail_Exit 
End Function

This concludes the review of features specific to the Membership Tracking application. As you explore the forms and reports in the application, you're likely to discover additional interesting features that you can use in any application. You can find some of these features described in Appendix E, "Implementing Generic Features."

Read Chapter 4

Read Chapter 5

Read Chapter 6

Read Chapter 7