Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

  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.

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).

Share:

6 comments:

  1. I would suggest looking at Total Access Emailer from FMS. It lets you send personalized emails from Access including the use of Access reports at the body of each message or as attachments. It's a commercial product, but it's very good.

    http://www.fmsinc.com/MicrosoftAccess/Email.asp

    ReplyDelete
  2. thanks for his helpfull article

    ReplyDelete
  3. Thanks for this article. It's really helpful. I have one question, though, at my company Outlook is not installed. Can I still use Lotus notes and Access to e-mail reports?

    ReplyDelete
  4. I did already find out how to send e-mail with MsAccess. Although this works fine, I am still looking for a way to use a group-account to send the email from. In MsAccess I cannot find a From: option. If wonder if there is a possibillity to send mails On Behalf Of....

    ReplyDelete
  5. [...] at the following blog posts for a detailed discussion of using Lotus Notes for Mails from Access: LEARN MS-ACCESS TIPS AND TRICKS - MS-Access and E-Mail LEARN MS-ACCESS TIPS AND TRICKS - Configure Outlook for Lotus Notes LEARN MS-ACCESS TIPS AND [...]

    ReplyDelete
  6. [...] the following links: Ms-Access and Email Automated e-mail alerts __________________ www.MsAccessTips.com (Learn MS-Access Tips & [...]

    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