Building an Access database provides many benefits because it not only gives users an easy way to manage their data, but Access can also be used to manipulate external applications through code. One of those extremely helpful benefits is its ability to send out an email message. Access is not an email client, so it doesn’t really send out the email message itself. Instead, it can be used to automate external email client programs, such as Outlook, to actually send out the message on its behalf. But what if Outlook is not installed on the user’s computer? Normally, that is not a problem as long as the user has an email client installed on the machine. The SendObject method or the EmailDatabaseObject macro action will try to use whatever email client is installed as the default to send out the message. This article will address those situations where the user does not have any email client installed or if bypassing the security warning in Outlook is desired.

Collaboration Data Objects (CDO)

Microsoft developed a technology called Collaboration Data Objects (CDO) to help simplify the creation of messaging applications. CDO for Windows 2000 (CDOSYS.DLL) implements Application Programming Interface (API) specifications for manipulating Internet messages. It is an integral part of the Windows 2000 series of operating systems, so there is no need to install any additional software. We can use CDO to send out email messages through an email server using the SMTP protocol. If you want to learn more about CDO, you can visit this MSDN article: http://msdn.microsoft.com/en-us/library/ms527525(v=exchg.10).aspx

Requirements

Before you start writing the code to send an email message in Access using CDO, you will need access to an email server and the following information about that server:

• The server’s SMTP address (this could be a domain name or an IP address)
• The server’s SMTP port number
• Your username and password

You might also need the following, depending on your server’s setup requirements:

• Authentication method (e.g. clear text)
• Connection security (e.g. SSL)
• Connection timeout

And of course, you will need the following to send out the email message:

• The recipient’s email address
• Your email address

Putting it all together (show me the code)

The following code illustrates how to send an email message from Access using CDO.

Public Sub SendEmail()
'Sample CDO code to send email using Gmail’s SMTP server settings

Dim cdoConfig As Object
Dim cdoMessage As Object

Set cdoConfig = CreateObject("CDO.Configuration")
Set cdoMessage = CreateObject("CDO.Message")

'setup server configuration
With cdoConfig.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'smtp address
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'smtp port number
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'clear text authentication
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'smtp over the network
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 'timeout in seconds

    'following settings are optional
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = "Access Hosting"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "AccessHosting@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpuserreplyemailaddress") = "AccessHosting@gmail.com"
    
    'warning: it’s a security risk to hard-code your username and password
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
    .Update
End With

'build and send email message
With cdoMessage
    Set .Configuration = cdoConfig
    .Subject = "Test Message"
    .From = "Access Hosting"
    .To = "recipient@company.com"
    .TextBody = "This is only a test..."
    '.HTMLBody = "

Use this to send email as HTML.

” ‘.AddAttachment = “c:\foldername\filename.ext” .Send End With Set cdoConfig = Nothing Set cdoMessage = Nothing End Sub

Conclusion

MS Access is not only an excellent tool for managing records in a database, but it is also capable of manipulating external programs such as Outlook. However, having Outlook installed on the machine is not really required to send out email messages using Access. As demonstrated using the code above, Access is also capable of sending out email messages using CDO, which is already part of the Windows operating system and included in Access Hosting’s $29/month RDP hosting plan. Using the above code does not require the user to install any additional software to send out an email message from within Access.  Remember that you can test this solution and other Access Database code on our RDP free trial today!