Updated: March 15, 1996

Go To TechNet Home Page

Creating MS Office-Based Workgroup Solutions with MS Mail-Contents

Presented by: Steve Harshbarger

Steve Harshbarger is a director at Micro Modeling Associates Inc., a Microsoft Solution Provider specializing in application and component development and training using Microsoft technology. He is responsible for application development using Microsoft Visual Basic and the Microsoft Office tool set, including Microsoft Excel, Microsoft Access, and Microsoft Word.

What Can You Do with MS Mail and Office?
What is MAPI?
Understanding MAPI's Structure
Product-Specific Implementations
Example 1: Expense Reporting
Example 2: Report Distribution
Example 3: Report Consolidator
Further Reading

What Can You Do with MS Mail and Office?

Microsoft® Office Professional, the most popular family of products (Microsoft Excel spreadsheet, Microsoft Word word processor, and Microsoft Access database), is a powerful application development platform. Office can be used to build reporting systems, presentation generators/assemblers, executive information systems, financial models, document management systems, database applications, and more. Combined with the functionality of Microsoft Mail, these applications can be made more useful and entirely new types of applications can be built. Consider the following examples of possible applications using Office and Mail.

Expense Reporting

A common activity in many companies is filling out expense reports. Often, these are paper-based forms, which are prone to math errors and take too long to fill out.

In an Office/Mail-based solution, individuals can fill out periodic expense reports using a familiar Microsoft Excel template, which performs the necessary calculations. The template can be programmed to automatically route the completed report to an accounting department for processing via electronic mail. From the user's point of view, a custom button or command on the sheet is all that is necessary to submit the report.

The expense reporting template could even be developed to handle routing of the report to appropriate individuals for approval before it gets to accounting. Once in accounting, another Microsoft Excel application could be written to interface the information with a database for corporate-wide reporting and analysis.

Report Distribution

Many managers are the recipients of periodic reports. A project manager, for instance, might receive a weekly list of projects he or she is responsible for, details of resource usage and budget status for each project, and utilization figures for the staff. Many companies distribute these types of reports on paper.

An Office/Mail-based solution would incorporate a server-based application that creates the reports in batches and automatically mails them to the appropriate individuals. Specifically, a Microsoft Access application could produce the reports, export the information to Microsoft Excel spreadsheets and mail them to each manager. The manager gets more timely information in a more useable (electronic instead of paper) form.

Document Collaboration

A person responsible for the production of a large document often solicits the help of many contributors. Examples would include the production of reports, manuals, and proposals. Distributing and consolidating sections of these documents is often a challenging task.

An Office/Mail-based solution can ease the process. A Word template can be created with custom commands to create sections, distribute them to contributors via electronic mail, track their status, and consolidate them into the original document. All this can be accomplished within Word in the context of the document being worked on.

Document Contents

Each of the applications described above is made possible by the Mail Application Programming Interface (MAPI). It is an interrelated set of mail services in his or her application. It exists at the function level, i.e. MAPI is implemented as a dynamic-link library (DLL) with functions that must be declared within the application that calls them.

This paper addresses a subset of full MAPI often referred to as Simple MAPI. Simple MAPI contains all the functionality needed to implement useful mail-based applications within Microsoft Office.

Simple MAPI provides the following general categories of services:

• Logon and Logoff
• Creating, Addressing, and Sending Messages
• Receiving and Reading Messages
• Deleting Messages

Document Contents

MAPI is composed of a myriad of data types, functions, and constant declarations. This section gives a high level view of these components to give you general direction on how to accomplish specific tasks.

MAPI Data Types

There are three user-defined data types necessary to use MAPI:

• MAPIMessage
• MAPIRecip
• MAPIFile

MAPIMessage defines the structure of an actual mail message including its text, subject, and date:

Type MapiMessage
    Reserved As Long
    Subject As String
    NoteText As String
    MessageType As String
    DateReceived As String
    ConversationID As String
    Flags As Long
    RecipCount As Long
    FileCount As Long
End Type

MAPIRecip defines a recipient (addressee) of a message including the name and address:

Type MapiRecip
    Reserved As Long
    RecipClass As Long
    Name As String
    Address As String
    EIDSize As Long
    EntryID As String
End Type

MAPIFile defines a file attachment to a message including the filename, the name as it appears in the message, and its position in the message.

Type MapiFile
    Reserved As Long
    Flags As Long
    Position As Long
    PathName As String
    FileName As String
    FileType As String
End Type

MAPI Functions

MAPI is composed of a series of functions. The most useful are discussed in this paper. These can be divided into logical groups according to their function as follows:

Logon and Logoff

MAPILogon: logs on to MAPI; returns a session handle

MAPILogoff: logs off of MAPI

Sending Messages

MAPISendMail: Sends a message; offers the most control

MAPISendDocuments: Sends documents; prompts the user for address

MAPIAddress: Prompts the user to address a message

Reading Messages

MAPIFindNext: Locates the next message in the inbox

MAPIReadMail: Reads the contents of the current message

MAPISaveMail: Saves the current message

Deleting Messages

MapiDeleteMail: Deletes the current message

Informational Functions

MAPIResolveName: Displays a dialog to resolve an ambiguous name

Logging On and Off

Every time you use MAPI, you must log on before doing anything else. Logging on returns to you a Session Handle, which is merely a number which you use as a parameter to other MAPI functions. After you are finished with MAPI, you log off to free up any resources used by the session.

The general form is as follows:

Result = MAPILogon(<various parameters>, Session)
	<other MAPI calls>
Result = MAPILogoff(Session, <various parameters>)

Checking for Errors

All MAPI functions return a result code. This value should be checked after each MAPI call to determine if it was successful.

Sending a Message

The general approach to sending a message is as follows (details are in the examples below)

• Log on to MAPI to get a session handle
• Fill a MAPIMessage variable with the message text, subject, number of recipients, and number of attachments
• Fill an array of MAPIRecip variables with each recipient of the message
• Fill an array of MAPIFile variables with details about each attachment
• Call MAPISendMessage passing the session handle, MAPIMessage, MAPIRecip(), and MAPIFile() variables as arguments
• Log off MAPI

Reading a Message

The general approach to reading a message is as follows (details are in the examples below)

• Log on to MAPI to get a session handle
• Call MAPIFindNext to get the message ID of the message
• Call MAPIReadMail, passing the message ID, MAPIMessage, MAPIRecip(), and MAPIFile() variables as arguments
• Examine the MAPIMessage variable to get the text of the message
• Examine the array of MAPIRecip variables to get recipient information
• Examine the array of MAPIFile variables to get details about each attachment
• Log off MAPI

Deleting a Message

The general approach to deleting a message is as follows (details are in the examples below)

• Log on to MAPI to get a session handle
• Call MAPIFindNext to get the message ID of the message
• Call MAPIDeleteMail, passing the message ID as an argument
• Log off MAPI

Document Contents

Although there is only one version of MAPI, it is implemented in a slightly different manner depending on what tool you are using it from. These differences are due mostly to the inherent capabilities and limitations of the language used to host MAPI. The following sections briefly describe how MAPI is implemented in each Microsoft Office product.

Microsoft Access

In Microsoft Access, MAPI is hosted by the Access Basic language. All mail functionality is accessed via functions contained in MAPI.DLL. These functions must be declared within the declarations sections of an Access Basic module.

The full set of declarations and support routines are provided and described in the Microsoft Access sample application described later in the paper.

Microsoft Excel

In Microsoft Excel, MAPI is hosted by the Microsoft Visual Basic®, Applications Edition language. Microsoft Excel/Visual Basic for Applications actually provides some very basic mail services within the Microsoft Excel object model, specifically:

• The MailLogon method. This method can be used to log on to the mail system and establish a new session.
• The MailLogoff method. This method can be used to log off the mail system.
• The SendMail method. This method can be used to send a workbook to a specified recipient.
• The MailSession property. This property returns the session ID created by the MailLogon method and can be used to drive "real" MAPI calls.

More complex requirements can be met by using the power stored in the following:

• MAPI.DLL
• VBMAPI.DLL.

These two DLLs provide the full set of MAPI functions which can be declared within a Visual Basic for Applications module. In addition, there are a set of Visual Basic for Applications routines that can be used to better integrate MAPI with the Microsoft Excel-provided mail methods and properties.

The full set of declarations and support routines are provided and described in the Microsoft Excel sample application described later in the paper.

Microsoft Word

In Word, MAPI is hosted by the WordBasic language. In some ways, WordBasic is the most limited of all the versions of Basic. Limitations on passing strings and a lack of user-defined types necessitates the use of a "wrapper" DLL, WBMAPI.DLL, to implement mail functionality within Word. This DLL changes the MAPI interface to a form that WordBasic can understand. WBMAPI.DLL, in turn, communicates with MAPI.DLL.

WBMAPI.DLL adds several new functions to deal with attachments and recipients since WordBasic cannot manipulate user-defined types directly.

From the Word developer's point of view, a series of functions from WBMAPI.DLL are declared in the body of a WordBasic macro. Both WPMAPI.DLL and MAPI.DLL must be present to use these functions.

The full set of declarations and support routines are provided and described in the Word sample application described later in the paper.

Document Contents

This example can be found in the file EXPENSE.XLS. This file demonstrates a simple expense reporting template. The template provides commands to facilitate entry of expenses, calculates totals, validates user input, and submits the expense report to a destination of the user's choice.

Application Structure

The workbook contains 5 tabs:

• Expenses is the actual expense form
• Departments contains a table of department names
• modMain contains all the custom Visual Basic for Applications code for this application
• modMAPIDeclares declares all the MAPI functions and constants
• modMAPIUtils provides Visual Basic for Applications-specific routines needed to support MAPI

How it Works

The routine to submit the workbook is called SubmitSheet. The relevant code is described as follows.

Save the current workbook to a temporary file:

	sTempFile = ThisWorkbook.Path & "\~expense.tmp"
    ThisWorkbook.SaveCopyAs sTempFile

Send the current workbook using the MAPISendDocument function:

    lResult = MAPISendDocuments(0, ";", \
		sTempFile, ThisWorkbook.Name, 0)
    If lResult <> SUCCESS_SUCCESS Then
        MsgBox GetMAPIErrorText(lResult), 48
    End If

Document Contents

This application can be found in the file REPORTS.MDB. This application demonstrates a server-based application that produces batches of reports and mails them to the appropriate managers.

This Microsoft Access application essentially determines a list of managers, runs a report for each, exports the report to a Microsoft Excel workbook, and mails the workbook as a message attachment.

Application Structure

The application is composed of the following components:

• Managers table-lists managers who will receive reports
• Projects table-lists all projects and associated managers
• ProjectWork table-lists hours and cost of projects by date and staff
• ProjectSummary query-totals hours and cost by project
• Status form-displays while reports are being run
• Project Summary report-a report for distribution to managers
• CreateReports macro-creates and mails reports
• MAPI Library module-MAPI function, type, and constant declarations
• Reports module-all application-specific code

How It Works

The routine to create and mail reports is called Main and is contained in the Reports module. The relevant code is described as follows.

Log on to MAPI

    'Result = MAPILogon(0, "", "", MAPI_LOGON_UI, 0, gSession)
    'If Result <> SUCCESS_SUCCESS Then Exit Sub

Create a report and output to Microsoft Excel format

	DoCmd OutputTo A_REPORT, "Project Summary", A_FORMATXLS, "C:\~TEMP.XLS"

Initialize MAPI variables

        ReDim gRecipients(0)
        ReDim gFiles(0)
        gMessage.RecipCount = 1
        gMessage.FileCount = 1

Specify the Microsoft Excel file attachment

        gFiles(0).Reserved = 0
        gFiles(0).Flags = 0
        gFiles(0).Position = 0
        gFiles(0).pathname = "c:\~temp.xls"
        gFiles(0).filename = "Reports.xls"
        gFiles(0).FileType = ""

Specify the Recipient (manager)

        gRecipients(0).Name = rs("Manager")
        gRecipients(0).RecipClass = MAPI_TO

Specify the Subject (NoteText is set to one space so the space can be replaced with the file attachment)

        gMessage.Subject = "Weekly Report"
        gMessage.NoteText = " "

Send the message by calling MAPISendMail

        Result = MAPISendMail(gSession, 0, gMessage, gRecipients(0), gFiles(0), 0, 0)

Log off MAPI

Result = MAPILogoff(gSession, 0, 0, 0)

Document Contents

This application can be found in WGTREPT.DOT. This application is used to facilitate the collaboration of several people on a single large Word document. The application allows a person to create a master document, add sections to it, distribute them to individuals for completion, monitor their status, receive them back, and merge them into the master document.

Application Structure

The application is composed of the following macros:

• AutoNew-initializes a new document
• AutoOpen-determines if the creator or a contributor opened the document and shows appropriate commands/toolbars
• CheckInbox-checks inbox for incoming drafts
• Constants-defines constants necessary for MAPI
• DistributeSection-sends a section to a contributor
• InsertSection-inserts a new section into the master document
• ReturnSection-returns a section to the master document creator

How it Works

Distributing a Section

Sections are actually stored as Word subdocuments. The following are the key bits of code to distribute a section.

Open the subdocument

	ViewMasterDocument
	OpenSubdocument
	ScreenRefresh

Store the owner name and address in the subdocument (this is used to know where to return the document later)

	EditGoTo "\headinglevel"
	CharLeft
	LineDown 1, 1
	ReportTitle$ = Selection$()
	result = Constants.fCurrentUser(OwnerName$, OwnerAddress$)
	result = SetDocumentVar("OwnerName", OwnerName$)
	result = SetDocumentVar("OwnerAddress", OwnerAddress$)
	result = SetDocumentVar("ReportTitle", ReportTitle$)	

Send the document via calls to MAPISetAttachment and MAPISendMail

If SendMailDialog(ReportTitle$, FName$) = - 1 Then
Function SendMailDialog(Subject$, Attachment$)
	MAPI_LOGON_UI = 1
	MAPI_DIALOG = 8
	Session = MAPILogon(0, "", "", MAPI_LOGON_UI, 0)
	result = MAPISetAttachment("", Attachment$, - 1, 0, 0)
	result = MAPISendMail(Session, 0, Subject$, \
		"Please complete this report.", MAPI_DIALOG, 0)
	SendMailDialog = result
	result = MAPILogoff(Session, 0, 0, 0)
End Function

Returning a Section

Returning a section involves automatically determining where to send it from entries in document variables. The following are the key bits of code to return a section.

Get Address to return to

OwnerName$ = GetDocumentVar$("OwnerName")
OwnerAddress$ = GetDocumentVar$("OwnerAddress")

Log on to MAPI

Session = MAPILogon(0, "", "", MAPI_LOGON_UI, 0)

Set the recipient and attachment

result = MAPISetRecipient(1, OwnerName$, OwnerAddress$)
result = MAPISetMessageType(MessageType$)
result = MAPISetAttachment("", fName$, - 1, 0, 0)

Call MAPISendMail to send the document

result = MAPISendMail(session, 0, Subject$, "", 0, 0)
If result >= 0 Then
	MsgBox "Section sent to " + Ownername$ + ".", 64
End If

Log off MAPI

result = MAPILogoff(Session, 0, 0, 0)

Document Contents

• The Microsoft Workgroup Templates
• The Microsoft Office Developer's Kit
• The Microsoft Electronic Forms Designer

© 1995 Microsoft Corporation.
THESE MATERIALS ARE PROVIDED "AS-IS," FOR INFORMATIONAL PURPOSES ONLY.
NEITHER MICROSOFT NOR ITS SUPPLIERS MAKES ANY WARRANTY, EXPRESS OR IMPLIED WITH RESPECT TO THE CONTENT OF THESE MATERIALS OR THE ACCURACY OF ANY INFORMATION CONTAINED HEREIN, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW EXCLUSIONS OF IMPLIED WARRANTIES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU.
NEITHER MICROSOFT NOR ITS SUPPLIERS SHALL HAVE ANY LIABILITY FOR ANY DAMAGES WHATSOEVER INCLUDING CONSEQUENTIAL INCIDENTAL, DIRECT, INDIRECT, SPECIAL, AND LOSS PROFITS. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF CONSEQUENTIAL OR INCIDENTAL DAMAGES THE ABOVE LIMITATION MAY NOT APPLY TO YOU. IN ANY EVENT, MICROSOFT'S AND ITS SUPPLIERS' ENTIRE LIABILITY IN ANY MANNER ARISING OUT OF THESE MATERIALS, WHETHER BY TORT, CONTRACT, OR OTHERWISE SHALL NOT EXCEED THE SUGGESTED RETAIL PRICE OF THESE MATERIALS.

Document Contents

Click Here to Search TechNet Web Contents

TechNet CD Overview

Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information.

Go To TechNet Home Page

©1996 Microsoft Corporation

Go To Microsoft Home Page