Send Email from Excel using VBA and VBScript - Tutorial¶

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.

[Excel + VBA - Send Email - Example]¶

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

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

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

Option Explicit

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

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

Private Sub oSmtp_OnAuthenticated()
    Application.StatusBar = "Authenticated"
End Sub

Private Sub oSmtp_OnClosed()
    CurrentEmailIsFinished = True
End Sub

Private Sub oSmtp_OnConnected()
    Application.StatusBar = "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)
    Application.StatusBar = "Sending " & lSent & "/" & lTotal & " ..."
End Sub

Public Sub SendMailFromWorkBook()

    Dim oSheet
    Set oSheet = ThisWorkbook.Sheets(1)
    Dim i, rows
    rows = oSheet.UsedRange.rows.Count

    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 Excel 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 Excel using VBA, do not reply."

    Dim emailSent
    emailSent = 0

    ' enumerate all email addresses in sheet1
    For i = 2 To rows
        name = Trim(oSheet.Cells(i, 1))
        address = Trim(oSheet.Cells(i, 2))
        body = Replace(bodyTemplate, "{name}", name)

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

        emailSent = emailSent + 1
    Next

    Application.StatusBar = "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

    ' 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

    ' 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

    Application.DisplayStatusBar = True
    Application.StatusBar = "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 HasErrorWithEmail Then
        Application.StatusBar = "Failed to send email to " & address & "; " & ErrorDescription
        SendMailTo = False
    Else
        Application.StatusBar = "Message to " & address & " has been submitted to server."
        SendMailTo = True
    End If

    Set oSmtp = Nothing
End Function

and click Module1 and change the codes to:

ThisWorkbook.SendMailFromWorkBook

Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box, select SendMail and click Run.

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

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.

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.office365.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.

Office365 SMTP Server¶

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

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.

[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

[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"

“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¶

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:

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.ConnectType = 1

“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"