MS-Access and E-Mail

Introduction

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

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

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

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

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

Outlook Versions

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

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

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

OR

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

OR

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

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

Using Lotus Notes for email

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

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

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

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

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

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

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

   See the image below.

   

   Click to Enlarge

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

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

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

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

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

Sample Mail Image

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

Click to Enlarge

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

The SendObject Command

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

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

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

• Reports Page Border
• Highlighting Reports
• Saving Data on Forms Not in Table
• Edit Data in a Zoom-in Control
• Custom Menus and Tool Bars-2