Working with Access VBA

Wouldn't you like to do more with your Access database? There is so much that you can do with Access by unleashing the full power of VBA.

What is VBA?

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft to be used with the Office products. VBA can be used to automate tasks, insert text strings, automate repetitive operations, create a custom command, create a custom toolbar, create a custom menu command, create a simplified front-end, develop new functions, or to create macro-driven applications.

This tutorial will show you how to create a mail function using Microsoft Access and VBA. Before getting started, I am going to assume that you have a basic knowledge of Access and forms.

First things first. Create a new database or use a database you already have. In one of your tables, you will need contact information, including email addresses, for this function to work. Create a form called 'Contacts'. Add the following controls to the form:

Name: txtLName
Type of Control: textbox
Properties: Set label = "Last Name"

Name: txtFName
Type of Contol: textbox
Properties: Set label = "First Name"

Name: txtEmail
Type of Control: textbox
Properties: Set label = "Email"

Name: cmdSendEmail
Type of Control: command button
Properties: Picture = Select Mailbox

Save the form. Now it is time for the fun part. Open up the click event for command button in the Properties window. To do this, click on the 'Event' tab and find 'On Click'. Choose [Event Procedure] from the drop-down menu and click the '...' button next to it. The Visual Basic editor opens to the cmdSendEmail click event. Type the following command so that your event looks like this:

Private Sub cmdSendEmail_Click( )

'This is an example of a comment. It is used to explain your code.

'This handles errors

On Error Resume Next

'Create a new email to the selected contact using the txtEmail textbox.

DoCmd.SendObject acSendNoObject, , , txtEmail.Value, , , , , True, False

Exit Sub

Explanation of the SendObject method

The SendObject method is used to send electronic mail messages with VBA. You can use the SendObject to send any type of Access object (datasheet, form, report, module, or data access page) or an Office object; such as an Excel spreadsheet or Word file.

The syntax for the SendObject method is:

expression.SendObject (ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

ObjectType is the type of object that will be included in the mail message. This could be one of the items that I listed above. In the SendObject method we are using, we selected acSendNoObject for our object type because we want to open an empty email address.

ObjectName is the name of the object that will be included in the mail message. We didn't include a name because we aren't sending an object.

OutputFormat is the format you want for the object included. For instance, you can turn a datasheet into HTML or Microsoft Excel format. We didn't include an option for OutputFormat because we aren't sending an object.

To are the recipients of the email message. For this we used the value of our textbox as the value of the To field.

Cc is the message recipients you want to send a carbon copy to. We didn't use a value for this.

Bcc is the message recipients you want to send a blind carbon copy to. We didn't use a value for this.

Subject is the subject of the message. We opened an empty message, so we didn't include a Subject. This way you can type in any subject you want with each individual message.

MessageText is the body of the email message. We didn't include this because we opened an empty mail message. We didn't use the MessageText isn't a uniformed response.

EditMessage specifies whether the message is editable. We set this to 'True' because we want the ability to edit the message.

TemplateFile is the path and filename of the template you want to use for your email message. We didn't use this option.

That's now. Now you can click one button on your form to open an Outlook email message. Play around with the settings of the SendObject properties to familiarize you with it. After a while, you will be a real wiz at sending emails from an Access form.