And automated email sending with VBA macros sends up rad flags in any case - although we have a workaround through a monitored mail server our admin guys set up for our needs. Ideally, having CDO support on OS X Mac Excel 2011 would be easiest as a drop-in port of the functionality. Dec 23, 2020 A group I am involved with utilizes and excel spreadsheet with macros, to send out weekly status emails. A number of us use it without problems. Recently, I shared a version of this spreadsheet with someone who works on a mac. He hasn't been able to enable the content. Is there a different procedure in a mac environment. He downloaded excel 365. Mar 29, 2017 I'm using Excel for Mac, but I'm unable to find Send Email to Recipient option in this. Neither are the following vba scripts working: Sub MailworkbookExcel20111 'For Excel 2011 for the Mac and Apple Mail 'Note: The workbook must be saved once Dim wb As Workbook If Val (Application.Version). Under Customize the Ribbon, select Main Tabs and then check Developer. Click Save and then close Excel Preferences. On the Developer tab, click Record Macro. Note: To create a macro from VBE, click Visual Basic. In the code window of the module, type the macro code that you want to use. In the Macro name box, enter a name for the macro.

Here is a method for how to send Emails using a VBA macro in Excel. In this post, I won’t go through a step by step guide on how sending emails using Excel is possible using macros and VBA options in Microsoft Excel.

This is because the spreadsheet template I would like to share was created for a very specific purpose, and is unlikely to be useful to the average blog reader.

What I’d like to do instead is give you an example of why and how I needed to send out emails (with attachments) using Excel. This could perhaps be an inspiration for you to make some process with your workflow or colleagues more efficient.

I learnt the basic formula from the template from this YouTube video below.

I’ll explain how I used the template detailed in the video above, and used it to accomplish the task I needed to automate.

What I needed the macro to do for me

What I needed to do was the following.

From a database, I could download separate sales reports for each country. I can also download the data (which contains website visitors, leads and sales) into one table in Excel.

Below is the table with the data (the data currently in there is all random numbers generated by the formula =RANDBETWEEN()).

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. The Macro text is below.

I’ve provided the Excel file template if you’d like to experiment and practice using the file.

Unfortunately I’ve had to remove the macro from the file, as I’m unable to upload a .xlsm file to this WordPress site.

Download Excel file template

Download the template file by clicking on the link below:

Hope this post was useful for those interested in automating some tasks involving Excel and Outlook!

My Other Blog Posts

If you enjoyed this post you might enjoy these other blog posts I’ve written.

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:

Send email from MS Excel using VBA and SMTP protocol¶

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.

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¶

Macros In Excel Definition

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.

How To Use Macros In Excel To Send Email For Mac Download

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!”¶

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¶

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

Distribution¶

Macros In Excel Examples

• 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

What Are 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.