• How To Use Macros In Excel To Send Email For Mac
• How To Use Macros To Send An Email From Excel
• Macros In Excel Definition
• How To Enable Macros In Excel

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

Sections:

Feb 21, 2019 I needed a way to copy and paste the table into an Excel sheet, which would then automatically create unique emails for 23 different Sales Offices. Each unique email would need to summarize the performance of the Sales Office and have a unique email attachment. I ended up with an email template below. I then created a VBA macro in Excel.

Send email from MS Excel using VBA and SMTP protocol¶

Are there users that run Office 2016 and use Mail as there mail program. Create some code that is working in Excel 2016 and I need some testers before I add some things and publish it. If you want to test it please drop me a mail, see mail address on my site (contact in the menubar). I use the Email icon in the Quick Access Toolbar above the ribbon to send an excel file daily. After I click on the Email icon, it opens a new message in Outlook with the attachment. It used to put the cursor in the To field automatically, but now my company has switched to Office 365. Dec 16, 2011 It also has option to send multiple emails via Outlook. The User Interface of this tool looks like this: Send Multiple Email To know more about How to send email using Excel Macro from GMAIL, Yahoo, Outlook etc, Read this Article. To help prevent macros that contain viruses from contaminating your system, by default Office for Mac displays a warning message whenever you try to open a document that contains a macro. This message appears regardless of whether the macro actually contains a virus.

To better demonstrate how to send email using SMTP protocol in Excel + VBA, let’s open MS Excel and input the data like this:

After you input the data, press Alt+F8, open the Macro dialog box, input SendMail and click Create

Input the following codes in Module1

Then you can close the VBA IDE and back to Excel and click File -> SaveAs -> select ExcelMacroEnabledWorkbook and save it as Sample.xlsm.

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 referenceof EASendMail to your project”. Open VBA IDE by press Alt+F11, Please select menu -> Tools -> References ->and select EASendMailObjActiveXObject, click OK, the reference will be addedto 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.

Important

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

and click Module1 and change the codes to:

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.

Common SMTP Transport Error¶

When you execute above example code, 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 notset user authentication. Another common error is “5xx Must issue a STARTTLS commandfirst” or “No supported authentication marshal found!”, that is becauseyour SMTP server requires user authentication under SSL connection. You can setthe SSL connection to solve this problem. You can learn more detail in Troubleshooting section.

TLS 1.2¶

TLS is the successor of SSL, more and more SMTP servers require TLS 1.2 encryption now.

If your operating system is WindowsXP/Vista/Windows7/Windows2003/2008/2008R2/2012/2012R2,and you got connection error with SSL/TLS connection,you need to enable TLS 1.2 protocol in your operating system like this:

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

Because each email account provider has different server address, so you shouldquery your SMTP server address from your email account provider. To prevent spreadingemail from the server, most SMTP servers also require user authentication. Username is your email address or your email address without domain part, it dependson your email provider setting.

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

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 begmailid@gmail.com.

Important

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

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

Hotmail/Outlook SMTP Server¶

Hotmail/Live/Outlook.com SMTP server address is smtp.live.com. It requires explicit SSL (TLS) connectionto do user authentication, and you should use your Hotmail/Live/Outlook.com email address as the username 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.

How To Use Macros In Excel To Send Email For Mac

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 addressas 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 portto 465.

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.

[VBA - Email Syntax - Example]¶

Here are some examples:

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:

• 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 sentto report@emailarchitect.net.
• If the user received the email, the email client will display: sender@emailarchitect.neton behalf of from@adminsystem.com.
• If the user click “reply”, the replied email will be sent to reply@adminsystem.com.

Mail Priority¶

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

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, TelnetClient is not installedby default, you should enable this command in ControlPanel -> ProgramsandFeatures -> TurnWindowsfeatureonoroff -> have TelnetClient checked.

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

If the networking connection to your SMTP server is good, it should return a messagelike 220.... If it returns Couldnotopenconnectionto..., that means thenetworking connection to SMTP server is bad, or outbound 25 port is blocked by anti-virussoftware, 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:

“No supported authentication marshal found!”¶

How To Use Macros To Send An Email From Excel

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]

32bit/x64 ActiveX DLL¶

Macros In Excel Definition

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

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

How To Enable Macros In Excel

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