Send Email from Access using VBScript and Email Queue¶

In previous section, I introduced how to send HTML email in a MS Access VBA project. In this section, I will introduce how to send email based on Access data using VBScript directly without VBA/Macro.

By default, Macro is disabled by MS Access, so it is better to use VBScript to read the data from Access database and send email directly from VBScript.

Create Sample Access File (accdb)¶

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

Installation SMTP Component¶

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.

Create VBScript file¶

After the installation is completed, you can create a VBScript by your favorite text editor. I prefer to use Visual Code or Notepad. Create a file name SendEmailFromAccess.vbs, input the following codes:

Option Explicit

' If you get error with connecting database (drive not found)
' Please install https://www.microsoft.com/en-us/download/details.aspx?id=13255 on your machine
'
Dim dbConnection
dbConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & GetScriptDirectory() & "\Sample.accdb;Uid=;Pwd=;"

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

SendHtmlMailFromAccess dbConnection

Function GetScriptDirectory()
    GetScriptDirectory = Left(WScript.ScriptFullName, InStr(WScript.ScriptFullName, WScript.ScriptName) - 2)
End Function

Sub SendHtmlMailFromAccess(dbConnection)

    WScript.Echo("Open " & dbConnection & " ...")

    Dim conn, rs, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.Open dbConnection

    Set rs = CreateObject("ADODB.Recordset")
    sql = "select * from users"

    rs.Open sql, conn
    rs.MoveFirst

    Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, attachment
    bodyFormat = 1 'HTML body format
    attachment = "" ' you can specify a file to be attached

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

    ' Use a body template to build body text based on current workbook
    bodyTemplate = BuildHtmlBody(rs)

    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, attachment) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
        rs.MoveNext

    Loop

    WScript.Echo("Total " & emailSent & " email(s) sent.")
End Sub

Function BuildHtmlBody(rs)

    Dim html, name, address, age, department

    html = "<!DOCTYPE html><html><body>"
    html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBScript. <br />"
    html = html & "Here is your data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"

    name = Trim(rs("Name"))
    address = Trim(rs("Email"))
    age = Trim(rs("Age"))
    department = Trim(rs("Department"))

    html = html & "<tr>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & name & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & address & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & age & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & department & "</td>"
    html = html & "</tr>"


    html = html & "</table></div></body></html>"
    BuildHtmlBody = html
End Function

Function SendMailTo(sender, name, address, subject, body, bodyFormat, attachment)
    Dim oSmtp
    Set oSmtp = CreateObject("EASendMailObj.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

    ' 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=connecttype
    oSmtp.ConnectType = 4

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

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

    ' Add attachment from local disk
    If attachment <> "" And oSmtp.AddAttachment(attachment) <> 0 Then
        WScript.Echo("Failed to add attachment with error:" & oSmtp.GetLastErrDescription())
        SendMailTo = False
        Exit Function
    End If

    WScript.Echo("Connecting " & oSmtp.ServerAddr & " ...")

    If oSmtp.SendMail() <> 0 Then
        WScript.Echo("Failed to send email to " & address & "; " & oSmtp.GetLastErrDescription())
        SendMailTo = False
    Else
        WScript.Echo("Message to " & address & " has been submitted to server.")
        SendMailTo = True
    End If

    Set oSmtp = Nothing

End Function

Read Data from MS Access DB and Send Email from VBScript¶

Save above file to the same folder of Sample.accdb file.
Open windows command and go to the folder by cd command.
Run the following command

cscript SendEmailFromAccess.vbs

You will see the status and result in Console output. With VBScript, you don’t have to enable Macro in MS Access, but you can still read data from Access and send email.

Note

You can use one VBScript to handle multiple Access files/DB by changing connection string in this VBScript file.

Important

If you got any error, please refer to This tutorial Troubleshooting section.

Filter Data¶

You can also send email to specified recipients based on the table data like this:

Option Explicit

' If you get error with connecting database (drive not found)
' Please install https://www.microsoft.com/en-us/download/details.aspx?id=13255 on your machine
'
Dim dbConnection
dbConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & GetScriptDirectory() & "\Sample.accdb;Uid=;Pwd=;"

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

SendHtmlMailFromAccessToIT dbConnection

Function GetScriptDirectory()
    GetScriptDirectory = Left(WScript.ScriptFullName, InStr(WScript.ScriptFullName, WScript.ScriptName) - 2)
End Function

Function BuildHtmlBody(rs)

    Dim html, name, address, age, department

    html = "<!DOCTYPE html><html><body>"
    html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBScript. <br />"
    html = html & "Here is your data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"

    name = Trim(rs("Name"))
    address = Trim(rs("Email"))
    age = Trim(rs("Age"))
    department = Trim(rs("Department"))

    html = html & "<tr>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & name & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & address & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & age & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & department & "</td>"
    html = html & "</tr>"


    html = html & "</table></div></body></html>"
    BuildHtmlBody = html
End Function

Public Sub SendHtmlMailFromAccessToIT(dbConnection)
    WScript.Echo("Open " & dbConnection & " ...")

    Dim conn, rs, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.Open dbConnection

    Set rs = CreateObject("ADODB.Recordset")
    ' Select the users in IT department
    sql = "select * from users WHERE Department='IT'"

    rs.Open sql, conn
    rs.MoveFirst

    Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, attachment
    bodyFormat = 1 'HTML body format
    attachment = "" ' you can specify a file to be attached

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

    ' Use a body template to build body text based on current workbook
    bodyTemplate = BuildHtmlBody(rs)

    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, attachment) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
        rs.MoveNext

    Loop

    WScript.Echo("Total " & emailSent & " email(s) sent.")
End Sub

Function SendMailTo(sender, name, address, subject, body, bodyFormat, attachment)
    Dim oSmtp
    Set oSmtp = CreateObject("EASendMailObj.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

    ' 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=connecttype
    oSmtp.ConnectType = 4

    ' 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

    ' Add attachment from local disk
    If attachment <> "" And oSmtp.AddAttachment(attachment) <> 0 Then
        WScript.Echo("Failed to add attachment with error:" & oSmtp.GetLastErrDescription())
        SendMailTo = False
        Exit Function
    End If

    WScript.Echo("Connecting " & oSmtp.ServerAddr & " ...")

    If oSmtp.SendMail() <> 0 Then
        WScript.Echo("Failed to send email to " & address & "; " & oSmtp.GetLastErrDescription())
        SendMailTo = False
    Else
        WScript.Echo("Message to " & address & " has been submitted to server.")
        SendMailTo = True
    End If

    Set oSmtp = Nothing

End Function

Run the following command again

cscript SendEmailFromAccess.vbs

Send Email using EASendMail Service Queue¶

EASendMail Service is a light and fast email delivery service which works with EASendMail SMTP Component to enable your application to send mass emails in background queue service.

Along with its ability to picking recipients from database in background and sending email in specified datetime, it eases your task in developing featured email application such as newsletter application. We strongly recommend you to use EASendMail Service with your ASP/Web Application.

Important

To work with EASendMail Service, please download EASendMail and EASendMail Service at first, and then install both on your machine. If you are using web hosting service and you don’t have permission to install service on that server, EASendMail service is not suitable for you.

With EASendMail email queue feature, you do not have to code for multiple threadings. EASendMail Service can send email in background with multiple threadings automatically. You just need to adjust the maximum worker threads in EASendMail Service Manager to increase the performance. Please click here to learn more detail about EASendMail Service.

If your networking connection to your SMTP server is not very fast, EASendMail Service is absolutely solution for you. You just need to submit the email to EASendMail service queue, it is very fast because EASendMail service uses shared memory to accept email from EASendMail component, and then the service will send email in background service. It is very important to improve the response time for ASP/ASP.NET web application.

Option Explicit
' If you got error with Connecting database
' Please install https://www.microsoft.com/en-us/download/details.aspx?id=13255 on your machine
'
Dim dbConnection
dbConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & GetScriptDirectory() & "\Sample.accdb;Uid=;Pwd=;"

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

SendHtmlMailFromAccessToQueue dbConnection

Function GetScriptDirectory()
    GetScriptDirectory = Left(WScript.ScriptFullName, InStr(WScript.ScriptFullName, WScript.ScriptName) - 2)
End Function

Sub SendHtmlMailFromAccessToQueue(dbConnection)
    WScript.Echo("Open " & dbConnection & " ...")

    Dim conn, rs, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.Open dbConnection

    Set rs = CreateObject("ADODB.Recordset")
    sql = "select * from users"

    rs.Open sql, conn
    rs.MoveFirst

    Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, attachment
    bodyFormat = 1 'HTML body format
    attachment = "" ' you can specify a file to be attached

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

    ' Use a body template to build body text based on current workbook
    bodyTemplate = BuildHtmlBody(rs)

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

        If Not SendMailToQueue(sender, name, address, subject, body, bodyFormat, attachment) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
        rs.MoveNext

    Loop

    WScript.Echo("Total " & emailSent & " email(s) sent.")

End Sub

Function BuildHtmlBody(rs)

    Dim html, name, address, age, department

    html = "<!DOCTYPE html><html><body>"
    html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBScript. <br />"
    html = html & "Here is your data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"

    name = Trim(rs("Name"))
    address = Trim(rs("Email"))
    age = Trim(rs("Age"))
    department = Trim(rs("Department"))

    html = html & "<tr>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & name & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & address & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & age & "</td>"
    html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & department & "</td>"
    html = html & "</tr>"


    html = html & "</table></div></body></html>"
    BuildHtmlBody = html
End Function

Function SendMailToQueue(sender, name, address, subject, body, bodyFormat, attachment)
    Dim oSmtp
    Set oSmtp = CreateObject("EASendMailObj.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

    ' 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=connecttype
    oSmtp.ConnectType = 4

    ' 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

    ' Add attachment from local disk
    If attachment <> "" And oSmtp.AddAttachment(attachment) <> 0 Then
        WScript.Echo("Failed to add attachment with error:" & oSmtp.GetLastErrDescription())
        SendMailTo = False
        Exit Function
    End If

    WScript.Echo("Connecting " & oSmtp.ServerAddr & " ...")

    ' To use this method, please download and install
    ' https://www.emailarchitect.net/webapp/download/easendmailservice.exe
    If oSmtp.SendMailToQueue() <> 0 Then
        WScript.Echo("Failed to send email to " & address & "; " & oSmtp.GetLastErrDescription())
        SendMailToQueue = False
    Else
        WScript.Echo("Message to " & address & " has been submitted to EASendMail Service.")
        SendMailToQueue = True
    End If

    Set oSmtp = Nothing
End Function

Run the following command again

cscript SendEmailFromAccess.vbs

Download Sample VBScript Access Poject¶

You can download Sample.accdb and SendEmailFromAccess.vbs here, all codes and files in this tutorial are included.

Free Email Support

Not enough? Please contact our technical support team.

Support@EmailArchitect.NET

Remarks

We usually reply emails in 24hours. The reason for getting no response is likely that your smtp server bounced our reply. In this case, please try to use another email address to contact us. Your Gmail, Hotmail email account is recommended.

Tutorial Index

Send Email from Access using VBScript and Email Queue¶

Create Sample Access File (accdb)¶

Installation SMTP Component¶

Create VBScript file¶

Read Data from MS Access DB and Send Email from VBScript¶

Filter Data¶

Send Email using EASendMail Service Queue¶

Download Sample VBScript Access Poject¶

Share

See Also