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 e-mails through Internet, when 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 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 for 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, for Internet 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 on the top area of the About Screen.

Let us explore few scenarios that warrants for 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 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 attachment, in Microsoft Access Snapshot Format, manually.

  1. Open your Database and Click on a Report (avoid the Dynamic Report that we have created in the earlier session).
  2. Point your mouse on the Send To.. option in File Menu and Click on Mail Recipient (as Attachment).
  3. Select Snapshot Format from the displayed file formats menu.
  4. A dialogue control will open up asking 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 that 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 Items display of Microsoft Outlook 2003 is given below for reference.

If you are sending a regular Report as 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 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 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 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 Mail Transport to send e-mails within 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 are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...

Labels

Blog Archive

Recent Posts