Send Email from MS Access using VBA and VBScript - Tutorial

This tutorial introduces how to send email from MS Access using VBA and VBScript. It also demonstrates SSL, HTML Email, Email Queue and Exchange Web Service (EWS) usage.

Send email from MS Access using VBA and SMTP protocol

To better demonstrate how to send email using SMTP protocol in Access + VBA, let’s open MS Access, select blank database, and name it as Sample.accdb. Create a table named Users like this:

vba_access.jpg

After you input the data, create a form named Users_From like this:

Menu -> Create -> Multiple Items

vba access create form

Right click Users_Form -> Design View, Add two buttons by Design -> Select Button control:

vba access add button

When you put the button on the form, it will popup a dialog box, simply click Cancel, then right click the button -> properties, set first button name to btnSend and Caption to Send Mail, set second button name to btnCancel and Caption to Cancel.

vba access named button.

Installation

EASendMail is a SMTP component which supports all operations of SMTP/ESMTP protocols (RFC 821, RFC 822, RFC 2554). Before you can use the following example codes, you should download the EASendMail Installer and install it on your machine at first.

Add Reference

To use EASendMail SMTP ActiveX Object in VBA project, the first step is “Add reference of EASendMail to your project”. Open VBA IDE by press Alt+F11, Please select menu -> Tools -> References -> and select EASendMailObj ActiveX Object, click OK, the reference will be added to current VBA project, and you can start to use it to send email in your project.

add reference in VBA

[Access + VBA - Send email - Example]

After the reference is added, click Form_Users_From and input the following codes:

Please enable Marco if you closed and re-opened this database, otherwise the codes cannot be executed.

access enable marco

Important

You need to access the Trust Center in the Access Options dialog box. Click the Microsoft Office Button, and then click Access Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.

' To use the following codes, please download and install
' https://www.emailarchitect.net/webapp/download/easendmail.exe on your machine

Option Compare Database

Private WithEvents oSmtp As EASendMailObjLib.Mail
Private CurrentEmailIsFinished As Boolean
Private HasErrorWithEmail As Boolean
Private ErrorDescription As String
Private CancelSending As Boolean

Private Sub InitVariables()
    CurrentEmailIsFinished = True
    HasErrorWithEmail = False
    ErrorDescription = ""
    CancelSending = False
End Sub

Private Sub btnCancel_Click()
    btnFocus.SetFocus
    btnCancel.Enabled = False
    If Not (oSmtp Is Nothing) Then
        oSmtp.Terminate
        CancelSending = True
        CurrentEmailIsFinished = True
    End If
End Sub

Private Sub Form_Load()
    btnCancel.Enabled = False
    btnSend.Enabled = True
    btnFocus.TabStop = False
    btnFocus.Transparent = True
    InitVariables
End Sub

Private Sub btnSend_Click()
    btnFocus.SetFocus
    btnCancel.Enabled = True
    btnSend.Enabled = False

    SendMailFromAccess

    btnCancel.Enabled = False
    btnSend.Enabled = True
End Sub

Private Sub oSmtp_OnAuthenticated()
    SysCmd acSysCmdSetStatus, "Authenticated"
End Sub

Private Sub oSmtp_OnClosed()
    CurrentEmailIsFinished = True
End Sub

Private Sub oSmtp_OnConnected()
    SysCmd acSysCmdSetStatus, "Connected"
End Sub

Private Sub oSmtp_OnError(ByVal lError As Long, ByVal ErrDescription As String)
    HasErrorWithEmail = True
    CurrentEmailIsFinished = True
    ErrorDescription = ErrDescription
End Sub

Private Sub oSmtp_OnSending(ByVal lSent As Long, ByVal lTotal As Long)
    SysCmd acSysCmdSetStatus, "Sending " & lSent & "/" & lTotal & " ..."
End Sub

Public Sub SendMailFromAccess()

    Dim sender, Name, address, subject, bodyTemplate, body, bodyFormat
    bodyFormat = 0 'Text body format

    ' Please change sender address to yours
    sender = "test@emailarchitect.net"
    subject = "Test email from MS Access and VBA"

    ' Use a body template to build body text based on recipient's name
    bodyTemplate = "Dear {name}," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
        "This is a test email from MS Access using VBA, do not reply."

    Dim rs, sql
    Set rs = CreateObject("ADODB.Recordset")

    sql = Me.RecordSource
    If Me.FilterOn Then
        sql = sql & " WHERE " & Me.Filter
    End If

    rs.Open sql, CurrentProject.Connection
    rs.MoveFirst

    Dim emailSent
    emailSent = 0

    Do While Not rs.EOF
        Name = Trim(rs!Name)
        address = Trim(rs!Email)
        body = Replace(bodyTemplate, "{name}", Name)

        If Not SendMailTo(sender, Name, address, subject, body, bodyFormat) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
        rs.MoveNext

    Loop

    SysCmd acSysCmdSetStatus, "Total " & emailSent & " email(s) sent."

End Sub

Function SendMailTo(sender, Name, address, subject, body, bodyFormat)

    Set oSmtp = New EASendMailObjLib.Mail
    oSmtp.LicenseCode = "TryIt"

    ' Please change server address, user, password to yours
    oSmtp.ServerAddr = "mail.emailarchitect.net"
    oSmtp.UserName = "test@emailarchitect.net"
    oSmtp.Password = "yourpassword"

    ' Set server port, if 25 port doesn't work, try to use 587 port
    oSmtp.ServerPort = 25
    oSmtp.SSL_init

    ' Using TryTLS,
    ' If smtp server supports TLS, then TLS connection is used; otherwise, normal TCP connection is used.
    ' https://www.emailarchitect.net/easendmail/sdk/?ct=ssl_starttls
    oSmtp.SSL_starttls = 2

    ' If your server is Exchange 2007 or later version, you can use EWS protocol.
    ' https://www.emailarchitect.net/easendmail/sdk/?ct=protocol
    ' Set Exchange Web Service Protocol - EWS - Exchange 2007/2010/2013/2016
    ' oSmtp.Protocol = 1

    oSmtp.FromAddr = sender
    oSmtp.AddRecipient Name, address, 0

    oSmtp.subject = subject
    oSmtp.bodyFormat = bodyFormat
    oSmtp.BodyText = body

    ' You can add attachment like this:
    ' Add attachment from local disk
    ' If oSmtp.AddAttachment("d:\test.jpg") <> 0 Then
        ' Application.StatusBar = "Failed to add attachment with error:" & oSmtp.GetLastErrDescription()
        ' SendMailTo = False
        ' Exit Function
    'End If

    SysCmd acSysCmdSetStatus, "Connecting " & oSmtp.ServerAddr & " ..."

    oSmtp.Asynchronous = 1
    InitVariables
    CurrentEmailIsFinished = False
    oSmtp.SendMail

    Do While Not CurrentEmailIsFinished
        ' Wait for the email sending, you can do other thing here
        DoEvents
    Loop

    If CancelSending Then
        SysCmd acSysCmdSetStatus, "Operation was terminated by user!"
        SendMailTo = False
    ElseIf HasErrorWithEmail Then
        SysCmd acSysCmdSetStatus, "Failed to send email to " & address & "; " & ErrorDescription
        SendMailTo = False
    Else
        SysCmd acSysCmdSetStatus, "Message to " & address & " has been submitted to server."
        SendMailTo = True
    End If

    Set oSmtp = Nothing
End Function

Close VBA IDE and back to Access, double click this form to display the form, and then click Send Mail.

You will see the status and result at Access status bar.

access status bar

Send Email based on Data Filter

Because above VBA codes use current Access Data Connection and Recordset, so you can send email to specified recipients based on Access Filter.

Go to Menu -> Home -> Filter Tab -> Advanced -> Advanced Filter/Sort

  • Field -> Department
  • Criteria -> IT
  • Close Filter Settings
  • Click Toggle Filter
access filter

You can see the form only displays the persons in IT department. If you click Send Email, only person in IT department will receive the email.

Common SMTP Transport Error

When you execute above example codes, if it returned an error about “Networking connection/Socket” or “No such host”, it is likely that your SMTP server address is not correct. If it threw an exception about “5xx Relay denied”, it is likely that you did not set user authentication. Another common error is “5xx Must issue a STARTTLS command first” or “No supported authentication marshal found!”, that is because your SMTP server requires user authentication under SSL connection. You can set the SSL connection to solve this problem. You can learn more detail in Troubleshooting section.

Where can I get my SMTP email server address, user and password?

Because each email account provider has different server address, so you should query your SMTP server address from your email account provider. To prevent spreading email from the server, most SMTP servers also require user authentication. User name is your email address or your email address without domain part, it depends on your email provider setting.

Finally, if you have already set your account in your email client such as Outlook or Window Mail, you can query your SMTP server address, user in your email client. For example, you can choose menu -> “Tools” - > - “Accounts” - > “Your email account” - > “Properties” - > “Servers” in Outlook express or Windows Mail to get your SMTP server, user. Using EASendMail to send email does not require you have email client installed on your machine or MAPI, however you can query your exist email accounts in your email client.

Send email in VB 6.0 project

Gmail, Hotmail, Outlook.COM, Office365 and Yahoo SMTP Server Settings

Here are common settings for well-known email providers:

Gmail SMTP Server

Gmail SMTP server address is smtp.gmail.com. It requires implicit SSL or explicit SSL (TLS) connection, and you should use your Gmail email address as the user name for ESMTP authentication. For example: your email is gmailid@gmail.com, and then the user name should be gmailid@gmail.com.

Server Port SSL/TLS
smtp.gmail.com 25, 587 TLS
smtp.gmail.com 465 SSL

Important

If you got authentication error, you need to enable Allowing less secure apps or Sign in using App Passwords.

The Gmail IMAP and SMTP servers have been extended to support authorization via the industry-standard OAuth 2.0 protocol. Using OAUTH protocol, user can do authentication by Gmail Web OAuth instead of inputting user and password directly in application. This way is more secure, but a little bit complex.

Using Gmail SMTP OAUTH

Hotmail/Outlook SMTP Server

Hotmail/Live/Outlook.com SMTP server address is smtp.live.com. It requires explicit SSL (TLS) connection to do user authentication, and you should use your Hotmail/Live/Outlook.com email address as the user name for ESMTP authentication. For example: your email is liveid@hotmail.com, and then the user name should be liveid@hotmail.com.

Server Port SSL/TLS
smtp.live.com 25, 587 TLS

Office365 SMTP Server

By default, Office 365 SMTP server uses 587 port and explicit SSL (TLS) connection.

Server Port SSL/TLS
smtp.office365.com 25, 587 (recommended) TLS

Yahoo SMTP Server

Yahoo SMTP server address is smtp.mail.yahoo.com. It supports both Normal/Implicit SSL/Explicit SSL (TLS) connection to do user authentication, and you should use your Yahoo email address as the user name for ESMTP authentication. For example: your email is myid@yahoo.com, and then the user name should be myid@yahoo.com.

If you want to use implicit SSL connection with Yahoo SMTP server, you must set the port to 465.

Server Port SSL/TLS
smtp.mail.yahoo.com 25, 587 TLS
smtp.mail.yahoo.com 465 SSL

Important

If you got authentication error, you need to enable Allowing less secure apps in your Yahoo account. Although Yahoo supports OAUTH, but it doesn’t provide mail permission, so OAUTH is not a solution for Yahoo mail.

Email Address Syntax and Multiple Recipients

The following example codes demonstrates how to specify display name and email address by different syntax.

' For single email address (From, ReplyTo, ReturnPath), the syntax can be:
' ["][display name]["]<email address>.
' For example:
"Tester, T" <test@adminsystem.com>
Tester <test@adminsystem.com>
<test@adminsystem.com>
test@adminsystem.com

' For mulitple email address (To, CC, Bcc), the syntax can be:
' [single email],[single email]...
' (,;\r\n) can be used to separate multiple email addresses.
' For example:
"Tester, T" <test1@adminsystem.com>, Tester2 <test2@adminsystem.com>,
   <test3@adminsystem.com>, test4@adminsystem.com

[VBA - Email syntax - Example]

Here are some examples:

oSmtp.FromAddr = "Tester<test@adminsystem.com>"
oSmtp.FromAddr = "test@adminsystem.com"
oSmtp.FromAddr = "<test@adminsystem.com>"

' Using AddRecipientEx to add To, Cc and Bcc in VB 6.0/Visual Basic 6.0
' Multiple addresses are separated with (,)
' The syntax is like this: "test@adminsystem.com, test1@adminsystem.com"
oSmtp.AddRecipientEx "test1@adminsystem.com, test2@adminsystem.com", 0
oSmtp.AddRecipientEx "Test1<test@adminsystem.com>, Test2<test2@adminsystem.com>", 0

' You can also add carbon copy (CC) or blind carbon copy (BCC) in the email.
oSmtp.AddRecipientEx "CC recipient<cc@adminsystem.com>", 1
oSmtp.AddRecipientEx "Bcc recipient<bcc@adminsystem.com>", 2

From, ReplyTo, Sender and Return-Path

From, Reply-To, Sender and Return-Path are common email headers in email message. You should always set From property at first, it is a MUST to identify the email sender. The following table lists the header and corresponding properties:

Header Property
From Mail.FromAddr
Reply-To Mail.ReplyTo
Sender Mail.Sender
Return-Path Mail.ReturnPath
  • From

    This property indicates the original email sender. This is what you see as the “FROM” in most mail clients.

  • Reply-To

    This property indicates the reply address. Basically, when the user clicks “reply” in mail client, the Reply-To value should be used as the recpient address of the replied email. If you don’t set this property, the Reply address is same as From address.

  • Sender

    This property indicates the who submit/send the email. When the user received the email, the email client displays: From: “sender address” on behalf of “from address”. If you don’t set this property, the Sender address is same as From address. Sender property is common used by mail listing provider. This property also takes effect to DKIM/DomainKeys signature, if Sender is different with From address, then you should sign DKIM/DomainKeys based on Sender domain instead of From address domain.

  • Return-Path

    This property indicates the delivery notification report address. If you don’t set this property, the Return-Path address is same as From address. This property also takes effect to SPF record, if Return-Path is different with From address, then remote SMTP server checkes SPF record of Return-Path instead of From address.

[VBA - From, ReplyTo, Sender and Return-Path in Email - Example]

The following example codes demonstrate how to specify From, Reply-To, Sender and Return-Path in Email. With the following example codes:

  • If the email couldn’t be delivered to recipient, a non-delivery report will be sent to report@emailarchitect.net.
  • If the user received the email, the email client will display: sender@emailarchitect.net on behalf of from@adminsystem.com.
  • If the user click “reply”, the replied email will be sent to reply@adminsystem.com.
' [VB, VBA - From, ReplyTo, Sender and Return-Path]
Dim oSmtp As New EASendMailObjLib.Mail
oSmtp.LicenseCode = "TryIt"
oSmtp.FromAddr = "from@adminsystem.com"
oSmtp.ReplyTo = "reply@adminsystem.com"
oSmtp.Sender = "sender@emailarchitect.net"
oSmtp.ReturnPath = "report@emailarchitect.net"

'[VBScript, ASP - From, ReplyTo, Sender and Return-Path]
Dim oSmtp
Set oSmtp = Server.CreateObject("EASendMailObj.Mail")
oSmtp.LicenseCode = "TryIt"
oSmtp.FromAddr = "from@adminsystem.com"
oSmtp.ReplyTo = "reply@adminsystem.com"
oSmtp.Sender = "sender@emailarchitect.net"
oSmtp.ReturnPath = "report@emailarchitect.net"

Mail Priority

If you want to set Higher or Lower priority to your email, you can use Priority prority

' Set high priority
oSmtp.Priority = 1 'High priority

Troubleshooting

When you send email in above simple VB project, if it returned an error, please have a look at the following tips:

“No Such Host” Error

This error means DNS server cannot resolve SMTP server, you should check if you input correct server address. If your server address is correct, you should check if your DNS server setting is correct.

Common “Socket/Networking Connection” Error

This error means there is a problem with networking connection to SMTP server. You can use Windows built-in Telnet command to detect the networking connection.

Using Telnet to detect networking connection to SMTP server

Note

Notice: in Windows 2008/Windows 8 or later version, Telnet Client is not installed by default, you should enable this command in Control Panel -> Programs and Features -> Turn Windows feature on or off -> have Telnet Client checked.

Under DOS command prompt, input “telnet [serveraddress] [port]”:

telnet mail.emailarchitect.net 25
press enter.

If the networking connection to your SMTP server is good, it should return a message like 220 .... If it returns Could not open connection to ..., that means the networking connection to SMTP server is bad, or outbound 25 port is blocked by anti-virus software, firewall or ISP. Please have a look at the following screenshot:

detect SMTP connection using telnet

SMTP 25, 587, 465 port

25 port is the default SMTP server port to receive email. However, some ISP block outbound 25 port to prevent user to send email directly to other SMTP server. Therefore, many email providers also provide an alternative port 587 to receive email from such users. 465 port is the common port used to receive email over implicit SSL connection. If you use telnet to test 465 port, it doesn’t return the “220...”, because it requires SSL hand shake. But if the connection is ok, telnet returns a flash cursor.

“5xx ... IP address block or on black list or bad reputation” Exception

This error means SMTP server blocks your IP address or email content. You can try to set user/password in your codes to do user authentication and try it again. If email client set user authentication, most SMTP servers do not check client source IP address in black list.

“5xx user authenticaton” Error

TThis error means user authentication is failed, you should check whether you input correct user/password. Password is always case-sensitive.

“5xx relay denied” Error

For anti-spam policy, most SMTP servers do not accept the email to outbound domain without user authentication. You should set user/password in the codes and try it again.

“5xx Must issue a STARTTLS command first”

This error means SMTP server requires SSL/TLS connection. You should enable SSL/TLS connection like this:

' If your smtp server requires TLS connection, please add this line
oSmtp.SSL_init

“No supported authentication marshal found!”

This error means SMTP server doesn’t support user authentication or it requires user authentication over SSL/TLS connection. You can try to remove user/password in your codes and try it again.

Other error returned by SMTP server

If SMTP server returns an error, it usually returns description about this error. Some descriptions also include a HTTP link, you can go to this linked web page to learn more detail. You can also use the following codes to generate a log file to learn all SMTP session between client and server.

[VBA - Using log file to detect SMTP server response - Example]

oSmtp.LogFileName = "d:\smtp.txt"

32bit/x64 ActiveX DLL

Seperate builds of run-time dll for 32 and x64 platform

File Platform
Installation Path\EASendMailObj.dll 32 bit
Installation Path\x64\EASendMailObj.dll 64 bit

Distribution

  • Standard EXE

    For VB6, C++, Delphi or other standard exe application, you can distribute EASendMailObj.dll with your application to target machine without COM-registration and installer. To learn more detail, please have a look at Registration-free COM with Manifest File.

  • Script

    For ASP, VBScript, VBA, MS SQL Stored Procedure, you need to install EASendMail on target machine by EASendMail installer, both 32bit/x64 DLL are installed and registered.

Next Section

In this section, I introduced how to send text email in MS Access VBA project using SMTP protocol. At next section I will introduce how to send HTML email to specified recipients based on MS Access data.

Appendix