Send Email from Excel using VBScript and Email Queue¶

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

By default, Macro is disabled by Excel, so it is better to use VBScript to read the data from Excel file and send email directly without Macro.

Create Sample Excel file (xlsx)¶

To better demonstrate how to send email using VBScript and Excel, let’s open MS Excel and input the data like this:

After you input the data, click File -> Save As -> select Excel Workbook and save it as Sample.xlsx.

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 SendEmailFromExcel.vbs, input the following codes:

Option Explicit

Dim excelFile
excelFile = GetScriptDirectory() & "\Sample.xlsx"

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

SendMailFromExcel excelFile

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

' save copy of current XLSX file and send it as attachment.
Function GetXLSXCopyOfWorkBook(ThisWorkbook, excelFile)
    Dim destPath
    destPath =  Mid(excelFile, 1, Len(excelFile) - 5) & ".copy.xlsx"
    WScript.Echo "Save copy of excel to " & destPath
    ThisWorkbook.SaveCopyAs destPath
    GetXLSXCopyOfWorkBook = destPath
End Function

Sub SendMailFromExcel(excelFile)

    WScript.Echo("Load " & excelFile & " ...")

    Dim oApp, oWorkbook, oSheet
    Set oApp = CreateObject("Excel.Application")
    Set oWorkbook = oApp.Workbooks.Open(excelFile)

    Dim copyOfExcel
    ' Send current excel file as an attachment, if you don't want to
    ' send attachment, remove the next line.
    copyOfExcel = GetXLSXCopyOfWorkBook(oWorkbook, excelFile)

    Set oSheet = oWorkbook.Sheets(1) ' get first work sheet

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

    ' 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 VBScript, 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, copyOfExcel) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
    Next

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

    oApp.Quit
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, please 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

Read Data from Excel File and Send Email from VBScript¶

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

cscript SendEmailFromExcel.vbs

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

Note

You can use one VBScript to handle multiple excel files by changing excel file name in this VBScript file.

Important

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

Send HTML email¶

You can also send HTML email based on the Excel Sheet Data like this:

Option Explicit

Dim excelFile
excelFile = GetScriptDirectory() & "\Sample.xlsx"

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

SendHtmlMailFromExcel excelFile

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

Function GetXLSXCopyOfWorkBook(ThisWorkbook, excelFile)
    Dim destPath
    destPath =  Mid(excelFile, 1, Len(excelFile) - 5) & ".copy.xlsx"
    WScript.Echo "Save copy of excel to " & destPath
    ThisWorkbook.SaveCopyAs destPath
    GetXLSXCopyOfWorkBook = destPath
End Function

Sub SendHtmlMailFromExcel(excelFile)
    WScript.Echo("Load " & excelFile & " ...")

    Dim oApp, oWorkbook, oSheet
    Set oApp = CreateObject("Excel.Application")
    Set oWorkbook = oApp.Workbooks.Open(excelFile)

    Dim copyOfExcel
    copyOfExcel = GetXLSXCopyOfWorkBook(oWorkbook, excelFile)

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

    Dim sender, name, address, subject, bodyTemplate, body, bodyFormat

    bodyFormat = 1 'HTML body format

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

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

    Dim emailSent
    emailSent = 0

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

        emailSent = emailSent + 1
    Next

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

End Sub

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

    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 Excel using VBA. <br />"
    html = html & "Here is sheet1 data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
    For i = 1 To rows

        name = Trim(oSheet.Cells(i, 1))
        address = Trim(oSheet.Cells(i, 2))
        age = Trim(oSheet.Cells(i, 3))
        department = Trim(oSheet.Cells(i, 4))

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

    Next

    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

    ' 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 SendEmailFromExcel.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 Web application.

Option Explicit

Dim excelFile
excelFile = GetScriptDirectory() & "\Sample.xlsx"

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

SendHtmlMailFromExcelToQueue excelFile

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

Function GetXLSXCopyOfWorkBook(ThisWorkbook, excelFile)
    Dim destPath
    destPath =  Mid(excelFile, 1, Len(excelFile) - 5) & ".copy.xlsx"
    WScript.Echo "Save copy of excel to " & destPath
    ThisWorkbook.SaveCopyAs destPath
    GetXLSXCopyOfWorkBook = destPath
End Function

Sub SendHtmlMailFromExcelToQueue(excelFile)
    WScript.Echo("Load " & excelFile & " ...")

    Dim oApp, oWorkbook, oSheet
    Set oApp = CreateObject("Excel.Application")
    Set oWorkbook = oApp.Workbooks.Open(excelFile)

    Dim copyOfExcel
    copyOfExcel = GetXLSXCopyOfWorkBook(oWorkbook, excelFile)

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

    Dim sender, name, address, subject, bodyTemplate, body, bodyFormat

    bodyFormat = 1 'HTML body format

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

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

    Dim emailSent
    emailSent = 0

    For i = 2 To rows
        name = Trim(oSheet.Cells(i, 1))
        address = Trim(oSheet.Cells(i, 2))
        body = Replace(bodyTemplate, "{name}", name)

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

        emailSent = emailSent + 1
    Next

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

End Sub

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

    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 Excel using VBA. <br />"
    html = html & "Here is sheet1 data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
    For i = 1 To rows

        name = Trim(oSheet.Cells(i, 1))
        address = Trim(oSheet.Cells(i, 2))
        age = Trim(oSheet.Cells(i, 3))
        department = Trim(oSheet.Cells(i, 4))

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

    Next

    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 = "localhost"
    'oSmtp.UserName = "ivan@emailarchitect.com"
    'oSmtp.Password = "testersfd"

    ' Set server port, if 25 port doesn't work, try to 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 & " ...")

    ' 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 SendEmailFromExcel.vbs

Download Sample VBScript Excel Poject¶

You can download Sample.xlsx and SendEmailFromExcel.vbs here, all example codes 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 Excel using VBScript and Email Queue¶

Create Sample Excel file (xlsx)¶

Installation SMTP Component¶

Create VBScript File¶

Read Data from Excel File and Send Email from VBScript¶

Send HTML email¶

Send Email using EASendMail Service Queue¶

Download Sample VBScript Excel Poject¶

Share

See Also