Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

1. Default Application

Example: Open the default email application using the address on the current form

   FollowHyperlink "MailTo:" & Me.txtEmailAddress

The example above will simply open a new email message window with the 'to' address set to the value of a control called txtEmailAddress.

Example: Send an email to everyone in the membership list including a report as an attachment

   Sub SendEmail()
   'Requires reference to the Microsoft DAO x.x Object Library
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim strSubject As String
   Dim strBody As String
   Dim strBodyPersonal As String
   
       'This is the subject line for the email.
       strSubject = "Membership List"
       
       'This is the message.
       strBody = "The membership list for " & Format(Date, "mmmm yyyy") _
           & " is attached." & vbCrLf & "Regards, LTD"
       
       'This is the query (SQL string) for people in the membership list who are
       'still current.
       strSQL = "Select LastName, FirstName, EmailAddress From tblMembers " _
           & "WHERE CurrentMember=True"
       
       'Open a recordset using the SQL string.
       Set rs = CurrentDb.OpenRecordset(strSQL)
       
       'Starting at the beginning of the list ...
       Do While Not rs.EOF
       
           'Personalize the message ...
           strBodyPersonal = "Hi " & rs!FirstName & vbCrLf _
               & strBody
       
           '... and send an email using this information from Microsoft help:
           'DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject,
           'MessageText, EditMessage, TemplateFile)
       
           DoCmd.SendObject acSendReport, "rptMembershipList", acFormatRTF, _
           rs!EmailAddress, , , strSubject, strBodyPersonal
       
           'On to the next record ...
           rs.MoveNext
       
       'And repeat.
       Loop
   End Sub

The example illustrates sending a report as an attachment in Rich Text Format (RTF). You can send Data Access Pages (DAP), Forms, Modules, Queries, Reports and Tables in these formats:

• Data Access Page: acFormatDAP
• HTML: acFormatHTML
• Rich Text: acFormatRTF
• Plain Text: acFormatTXT
• Microsoft Excel: acFormatXLS
• Access Snapshot: acFormatSNP

You can also use this method with acSendNoObject to simply send a message.

Advantages This is a quick and easy means of mailing using the default email application.

Disadvantages You cannot send more than one attachment with this method, nor can you attach a file from disk.

Further Information DoCmd.SendObject Method: http://msdn2.microsoft.com/en-us/library/Bb214075.aspx

2. Outlook

Example: Send an email to everyone in the membership list including two attachments

   Sub SendOutLookMail()
   'Requires reference to the Microsoft Outlook x.x Object Library
   'It is quite easy to pick an Outlook constant as a variable name, be careful.
   Dim oApp As Outlook.Application
   Dim oMail As Outlook.MailItem
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim strSubject As String
   Dim strBody As String
   Dim strBodyPersonal As String
   
       'Set up Outlook and an email.
       Set oApp = CreateObject("Outlook.Application")
       Set oMail = oApp.CreateItem(olMailItem) 'The value of olMailItem is 0
        
       'This is the subject line for the email.
       strSubject = "Membership List"
        
       'This is the message with a little HTML.
       strBody = "<P>The membership list for <FONT color=#ff0000>" _
            & Format(Date, "mmmm yyyy") & "</FONT> is attached.</P>" _
            & "<P>What do you think of the new logo?</P><P>Regards, LTD</P>""
       
       'This is the query (SQL string) for people in the membership list who are
       'still current.
       strSQL = "Select LastName, FirstName, EmailAddress From tblMembers " _
           & "WHERE CurrentMember=True"
        
       'Open a recordset using the SQL string.
       Set rs = CurrentDb.OpenRecordset(strSQL)
        
       'Starting at the beginning of the list ...
        Do While Not rs.EOF
           
           'Personalize the message ...
           strBodyPersonal = "Hi " & rs!FirstName & vbCrLf _
               & strBody
           
           With oMail
               .To = rs!EmailAddress
               .Subject = strSubject
               
               'Add attachments
               .Attachments.Add "C:\Docs\MembershipList.PDF"
               .Attachments.Add "C:\Docs\NewLogo.JPG"
               
               'Use .HTMLBody instead of .Body because of the HTML
               .HTMLBody = strBodyPersonal
           
               'Displays the email in Outlook.  Change to send if you want to
               'send without viewing the email.
               .Display
           End With
       
           'On to the next record ...
           rs.MoveNext
       
       'And repeat.
       Loop
        
       'Clean up
       Set oMail = Nothing
       Set oApp = Nothing
        
   End Sub

Example: Send a single email including a report in the body of the email

   Sub SendOutLookMailHTML()
   'Requires reference to the Microsoft Outlook x.x Object Library
   '
   'The reference for the FileSystemObject Object is Windows Script Host Object Model
   'but it not necessary to add the reference for this procedure. However, because
   'there is no reference, we need to set up some values:
   Const ForReading = 1, ForWriting = 2, ForAppending = 3
    
   Dim fs As Object
   Dim f As Object
   Dim strFile As String
   Dim oApp As Outlook.Application
   Dim oMail As Outlook.MailItem
   Dim strSubject As String
   Dim strBody As String
   
       'Set up Outlook and an email.
       Set oApp = CreateObject("Outlook.Application")
       Set oMail = oApp.CreateItem(olMailItem) 'The value of olMailItem is 0
       
       'This is the subject line for the email.
       strSubject = "Membership List"
       
       'Output the report to disk
       strFile = "C:\Docs\MembershipList.rtf"
       DoCmd.OutputTo acOutputReport, "rptMembershipList", acFormatHTML, strFile
       
       'This is the message with a little HTML.
       strBody = "<P>Here is the membership list for <FONT color=#ff0000>" _
        & Format(Date, "mmmm yyyy") & "</FONT>.</P><P>Regards, LTD</P>"
       
       'Using the FileSystemObject Object ...
       Set fs = CreateObject("Scripting.FileSystemObject")
       'Read the output file ...
       Set f = fs.OpenTextFile(strFile, ForReading)
       'And add it to the end of the email.
       strBody = strBody & f.ReadAll
       
       With oMail
           'Set up the email
           .To = "a@b.c"
           .Subject = strSubject
           
           'Use .HTMLBody instead of .Body because of the HTML
           .HTMLBody = strBody
           
           'Displays the email in Outlook.  Change to send if you want to
           'send without viewing the email.
           .Display
       End With
        
       'Clean up
       Set oMail = Nothing
       Set oApp = Nothing
      
   End Sub
   

The above example illustrates adding a report to the body of an email, but you can add anything that can be output: forms, queries, tables, reports and even modules.

Example: Use Outlook Events with Access

The two examples above use Display, which means that the user can alter the email before it is sent. You may wish to keep a copy of these emails in your database. There are several ways to do this, one of which is to use Outlook Events. For this example you will need a Class Module called clsOlMail with this code:

   'Requires reference to the Microsoft Outlook x.x Object Library
   Dim WithEvents conItems As Outlook.Items
    
   Private Sub Class_Initialize()
       Set oApp = Outlook.Application
       Set oNS = oApp.GetNamespace("MAPI")
       Set conFolder = oNS.GetDefaultFolder(olFolderSentMail)
       Set conItems = conFolder.Items
   End Sub
    
   Private Sub Class_Terminate()
       Set conItems = Nothing
       Set conFolder = Nothing
       Set oNS = Nothing
       Set oApp = Nothing
   End Sub
    
   Sub ConItems_ItemAdd(ByVal Item As Object)
   Dim frm As Form
       
       Set frm = Forms!frmEmailDetails
       
       frm.txtSenderName = Item.SenderName
       frm.txtSentOn = Item.SentOn
       frm.txtTo = Item.To
       frm.txtCreationTime = Item.CreationTime
       frm.txtBCC = Item.BCC
       frm.txtCC = Item.CC
       frm.txtSentOnBehalfOfName = Item.SentOnBehalfOfName
       frm.txtSubject = Item.Subject
       frm.txtBody = Item.Body
   End Sub

You will also need a form called frmEmailDetails with these textboxes:

txtSenderName, txtSentOn, txtTo, txtCreationTime, txtBCC, txtCC, txtSentOnBehalfOfName, txtSubject, txtBody

And this code:

   Private oEvent As clsOLMail
   'Requires reference to Microsoft Outlook x.x Object Library
    
   Public oApp As Outlook.Application
   Public oNS As Outlook.NameSpace
   Public conFolder As Outlook.MAPIFolder
    
   Private Sub Form_Open(Cancel As Integer)
       Set oEvent = New clsOlMail
   End Sub 

Open the form and send an email through Outlook, you can use one of the examples shown above. The form fields should fill with the relevant details from the sent email. You are likely to get an Outlook security warning.

Advantages You can do a lot with Outlook. The above examples only touch the surface.

Disadvantages The Outlook Object Model Guard can be a nuisance and the various Outlook libraries make referencing difficult but there are reasonably simple means to get around these problems.

Further Information How to use Automation to send a Microsoft Outlook message using Access 2000: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B209948

OL2000: Using Outlook Events in Another Program: http://support.microsoft.com/kb/225502

3. CDO

Example: Send a single email with an attachment using CDO

   Private Sub SendEmailCDO()
   'Requires reference to Microsoft CDO for Windows 2000
   Dim cdoConfig As Object
   Dim strSubject As String
   Dim strBody As String
   Dim strFile As String
   Dim cdoMessage As Object
   
       'Set up detail of the mail server
       Set cdoConfig = CreateObject("CDO.Configuration")
       With cdoConfig.Fields
           .Item(cdoSendUsingMethod) = 2 'cdoSendUsingPort
           .Item(cdoSMTPServerPort) = 25
           .Item(cdoSMTPServer) = "smpt.themailserver.com"
           .Item(cdoSendUserName) = "abc@themailserver.com"
           .Item(cdoSendPassword) = "password"
           .Update
       End With
       
       'This is the subject line for the email.
       strSubject = "Membership List"
       
       'This is the message with a little HTML.
       strBody = "<P>Here is the membership list for <FONT color=#ff0000>" _
        & Format(Date, "mmmm yyyy") & "</FONT>.</P><P>Regards, LTD</P>"
       
       'Location of Attachment
       strFile = "C:\Docs\MembershipList.rtf"
       
       'Set up the email message
       Set cdoMessage = CreateObject("CDO.Message")
       With cdoMessage
           .Configuration = cdoConfig
           .Subject = strSubject
           .From = "me@here.com"
           .To = "someone@there.com"
           .HTMLBody = strBody
           .AddAttachment strFile
           .Send
       End With
       
   End Sub

Advantages You can send an email without being troubled by Outlook.

Disadvantages You may not be able to send email to all addresses.

Further Information Where to acquire the CDO Libraries (all versions): http://support.microsoft.com/kb/171440/en-us Sending mail from Excel with CDO: http://www.rondebruin.nl/cdo.htm How to send HTML formatted mail using CDO for Windows 2000 and a remote SMTP service: http://support.microsoft.com/kb/286431/en-us