INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Emailing From Access

How to Email From Access using Outlook by nat1967
Posted: 21 Dec 02 (Edited 27 Nov 03)

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![email]) 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

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close