The finished application can be downloaded from the link at the end of this article.
Your life is about to get easier!
Access is a great solution for automating email tasks. We’re going to put together a bare minimum email application to illustrate how the code works. I hope you get a chance to use it soon.
We’ll start with the tables.
We need four tables. The first two, email_to and email_subject will have one column each, then we need email_body with body and body_title columns, and finally email_signature with sig and sig_title columns.
Next we’ll need an unbound form like the one shown at left. Click on the magnifying glass to enlarge the image.
Set up the “Save” buttons to add the content of your text box (or boxes) to the appropriate table like this:
Private Sub SaveSubjectButton_Click()
Dim dbs As Database
Set dbs = CurrentDb()
dbs.Execute “INSERT INTO unirev_email_subject (email_subject) SELECT '" & Me!email_subject & "';")
Me.email_subject.Requery
dbs.Close
End Sub
We’ll need AfterUpdate events on the body_title and sig_title combo boxes so that when you choose a body or signature by title the appropriate text is added to the box below.
Private Sub email_body_title_AfterUpdate()
Dim dbs As Database
Dim qdf As dao.QueryDef
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", "SELECT email_body as a1 " & _
"FROM unirev_temail_body WHERE email_body_title='" & Me!email_body_title & "'")
Set rst = qdf.OpenRecordset()
rst.MoveFirst
Me!email_body = rst!a1
rst.Close
qdf.Close
dbs.Close
Exit Sub
The code for sending or displaying the email is very straightforward:
Private Sub SendEmailButton_Click()
Dim strEmail As String
Dim strMsg As String
Dim objOL As Object
Dim objMail As Object
Set objOL = CreateObject("Outlook.Application")
Set objMail = objOL.createitem(0)
With objMail
.to = Me!email_to
.body = Me!email_body & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Me!email_sig
.Subject = Me!email_subject
'.Attachments.Add (strAttachmentPath)
.display ' or .send
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub
Now we’re ready to generate an email. Fill out a few of the fields and click display.
Options
Having data in an existing application for our subject and body text would be a great way to increase the usefulness of our little application.
Note that with this code you can display or send an email by clicking the button. One thing to beware of is trying to generate multiple emails from one click.
Double Duty
If you attempt to send more than one email at a time you’ll get a security error. Now there are solutions to this problem, but I’ve found it is more appropriate to send multiple emails from one click by going through a back-end database system. This has the advantage of not overriding an important security check.
Another option is to just display more than one email at once and have the user send them individually. I’ve had customers request this functionality when they need to customize each email slightly before they send it. It works very nicely and without a security error.
We’ve also got the ability to add an attachment in the code provided. We just need to get a valid file path and the email will appear with the attachment in tow.
By Justin Tocci
Related Tip: The signature function in your favorite email application can be used to provide boiler-plate body text as well as a signature.
Microsoft Office Access™ and Microsoft Office Excel™ are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.