Monday, October 20, 2008

Simple way to add Email Capability to your Access Database

So you have an Access database and are beginning to store records in it. You quickly realize the power that Access can give you but you also quickly hit a wall in your learning curve. You know there must be more you can do, but don’t know how to do it. Well why not add some email capabilities to your database? Using the SendObject command is an easy way to get this done.

If you’ve really just begun to use Access then using a macro is probably your best bet. You can then run your macro from a command button on your form. Form my example; I will use a form called AnyForm. AnyForm is a form that is bound to either a table or query. It doesn’t really matter is the form properties are set to single form or continuous. The table or query must include a field to hold email and name. My field is called txtEmail and txtName.

Use the toolbox and add a command button to the detail section of the form. Name this command button cmdEmail, and set the caption property to Email. In the “On Click” event of the events tab, click on the ellipse (…) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, EmailMacro. In the Action column of the macro, use the drop down and select SendObject.

The SendObject action has a number of arguments; Object Type, Object Name, Output Format, To, CC, BCC, Subject, Message Text, Edit Message, and Template File. You can use this action to email an object (table, query, report, etc.) as an attachment, but you can also leave it blank. For my example we will leave Object Type, Name and Format blank. We will program the To argument to be filled in automatically from your form. We can do this by typing in the To argument, [Forms]![AnyForm]![txtEmail]. For the Subject argument type in “Message from (your name)”. Message Text can be filled in the same way, but to personalize it, type the following “Dear “&[Forms]![AnyForm]![txtName]. Change the Edit Message property to Yes. If you leave it as no, the email will send without you being able to edit it. By setting the property to Yes, we will be able preview and add more text to our message before we send it.

Save and close your macro. Now open your form in Form View and click on your email command button. This event will open your default mail client, open a new mail message and populate the To, Subject, and the first line of your email!

If you have some experience with Access and prefer to use VBA, you can do this as well. For you VBA users, instead of selecting Macro Builder, choose Code Builder. The syntax would then be:

DoCmd.SendObject (Object Type, Object Name, Output Format, To, CC, BCC, Subject, Message Text, Edit Message, Template File)