Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and E-Mail

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.

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.

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.

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts