How to Email From Access using Outlook

After seeing this subject quite a few times on Tek-Tips and other newsgroups, I thought
I would put together a FAQ to help answer some of the questions surrounding sending
email from Access with Outlook.

Sending email from Access with Outlook is quite easy. In the following example, lets assume a business wants to send an email confirmation to a buyer after their order has shipped.

First things first... You will need to set a reference to the Outlook Object model if you have not done that yet.

In the VBA window, Goto: Tools>References

Scroll down and look at all the checked boxes. If you do not see one for Microsoft Outlook, scroll down further and put a check next to it.

Also, I write just about everything using the DAO object model. Be sure it is checked while you are there.

From a main form in the database, the user can input data to a few required fields, then
click a command button to send the email.

*****the breakdown of how it all works************

Form textboxes and data in the textboxes:

me!email: somewhere@yahoo.com
me!ref: Order#12345
me!origin: Atlanta, GA
me!destination: Seattle, WA
me!notes: Thank you for your business

The following code can be used behind a command button in the On_Click event. It will
take the information from the form fields and insert the information into a email to be
sent by Outlook.

'******begin code******
Dim email as String
Dim ref as String
Dim origin as String
Dim destination as String
Dim notes As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form. this sets the string variable to your fields
email = Me!email
ref = Me!ref
origin = Me!origin
destination = Me!destination
notes = Me!notes

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = email
.Subject = ref & " " & origin & " to " & destination
.Body = notes
.Send 'sends the email in Outlook. Change to DISPLAY if you want to be able to
'modify or see what you have created before sending the email
End With

'**closes outlook
objOutlook.Quit
Set objEmail = Nothing

Exit Sub
'****end code****

The email ends up like:

To: somewhere@yahoo.com
Subject: Order#12345 Atlanta, GA to Seattle, WA
Body: Thank you for your business


Sometimes, you might want to have a CC on the email. I recommend making it conditional for those times when a CC is not needed. With the code above, change the section that creates the email to the following:

'***creates and sends email
With objEmail
.To = strEmail
If Not IsNull(your CC email field name) then
.CC = (your CC email field or string)
end if
.Subject = "Your information has been received"
.Body = strBody
.Send
End With
'****end *****

The If Not IsNull statement is checking the CC textbox on the form to see if there is any
data. If not, the CC field is not added. If there is data, the field will be added with
the email data.

Ok, now you want to add attachments....

Modify the section of code that creates the email again.

'***creates and sends email
With objEmail
.To = strEmail
If Not IsNull(txtCCemail) Then
.CC = strCCemail
End If
.Subject = "Your information has been received"
.Body = strBody
.Attachments.Add ("c:\lott.txt") 'add attachment
.Send
End With

'****end attachment code****


Much more valuable information is available in the original thread. Numberous people contributed to making the thread a valuable source of information. Check out: thread702-396121

Now, if you just really want to jazz it up, how about checking to see if the email address
input on the main form follows the standard email format? You can do so by adding a module and a simple before update event on the textbox.

'****Before_Update Event code**********
Private Sub email_BeforeUpdate(Cancel As Integer)


If NOT IsMail(Me!) Then
MsgBox "You have not provided a valid email address. Try again", vbOKOnly, "Warning"
Cancel = True
Exit Sub
End If

End Sub
'**************************************

'******Function Module***************
Public Function IsMail(strString As String) As Boolean
Dim Lgth As Integer
Dim I As Integer
Dim CheckString As String

Lgth = Len(strString)
IsMail = False
For I = 1 To Lgth
CheckString = Mid(strString, I, 1)
If CheckString = "@" Then
If Mid(strString, Len(strString) - 3, 1) = "." Then
IsMail = True
Exit For
End If
End If
Next I
End Function
'***************************************

I hope this FAQ helps. If something is not explained well enough, please let me know. I want to ensure this faq is useful. I am open to any and all suggestion to better this faq.

Thanks in advance.

Nathan
Senior Software Tester
www.skylineprints.com