Instructions for sending emails from Excel spreadsheets using VBA scripts

Using VBA scripts in Excel does not require any programming knowledge. VBA helps you do many advanced tasks such as creating a spreadsheet report containing all PC information, emailing directly from a spreadsheet ...

To send email from Microsoft Excel just need a few simple scripts. Do not be afraid to add this feature to your spreadsheet if you want to really get the most out of Excel office software .

Email from Excel

There are many reasons why you want to send email right at Microsoft Excel. For example: Your employees update documents and spreadsheets weekly and you want to receive email notifications when that action is complete or sometimes, you want to email all contacts in a same workbook. at…

You must have thought of using a complicated Excel emailing script, but it's actually very simple. This article will guide you to take advantage of a feature that has long been available in Excel VBA, called Collaboration Data Objects (CDO) .

Instructions for sending emails from Excel spreadsheets using VBA scripts

CDO is a messaging component used in Windows right from the early versions. It used to be called CDONTS. After Windows 2000 & XP was born, "CDO for Windows 2000" replaced it. This component is included in the VBA installation in Microsoft Word or Excel and is available for use at any time.

Using this component to email in Windows products with VBA is extremely easy. For example, this article uses CDO in Excel to send emails that contain results in one cell.

Step 1: Create a VBA Macro

First, go to Excel Developer tab > click Insert in Controls panel , then select a command button.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Drag it into this worksheet, then create a new macro by clicking Macros in the Developer ribbon.

Instructions for sending emails from Excel spreadsheets using VBA scripts

When you click the Create button , it opens the VBA editor.

Add a reference to the CDO library by navigating to Tools> References in the editor.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Scroll down the list until you see Microsoft CDO for Windows 2000 Library . Check the checkbox and click OK .

Instructions for sending emails from Excel spreadsheets using VBA scripts

When you click OK , note the name of the function where the script is pasted, because you will need it later.

Step 2: Set the CDO fields "From" and "To"

To do this, you first need to create email-related objects and set up all the required email delivery fields.

Remember that many fields may or may not, but From and To are required.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String

strSubject = "Results from Excel Spreadsheet"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are:" & Str (Sheet1 .Cells (2, 1))

The interesting thing is that you can create any desired string to customize the entire email message and assign it the strBody variable.

Piecing together the contents of an email message using a string & to insert data from any Microsoft Excel spreadsheet right in an email (as shown above).

Step 3: Configure CDO to use external SMTP

The next piece of code is where you configure CDO to use any external SMTP server to send email.

This example does not set up SSL via Gmail, CDO can replace SSL but it is outside the scope of this article. If you need to use SSL, the advanced code in Github can help you.

Set CDO_Mail = CreateObject ("CDO.Message")
On Error GoTo Error_Handling

Set CDO_Config = CreateObject ("CDO.Configuration")
CDO_Config.Load -1

Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp. gmail.com "
.Item (" http://schemas.microsoft.com/cdo/configuration/smtpauthenticate ") = 1
.Item (" http://schemas.microsoft.com/cdo/configuration/sendusername ") =" [email protected] "
.Item (" http://schemas.microsoft.com/cdo/configuration/sendpassword ") =" password "
.Item (" http://schemas.microsoft.com/cdo/configuration/smtpserverport ") = 25
.Item (" http://schemas.microsoft.com/cdo/configuration/smtpusessl ") = True
.Update
End With

With CDO_Mail
Set .Configuration = CDO_Config
End With

Step 4: Complete CDO setup

You have now configured your SMTP server to send email. All you have to do is fill in the appropriate fields for the CDO_Mail object and issue the Send command .

Here's how to do it:

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

None of the pop-ups or security alerts appear as you usually see when using the Outlook email object.

CDO simply puts email components side by side and uses server connection details to enable sending information. This is the easiest way to combine emails into Microsoft Word or Excel VBA scripts.

To connect the command button to this script, access the code editor and click Sheet1 to see the VBA code of that worksheet.

Type the name of the function where you pasted the script above.

Instructions for sending emails from Excel spreadsheets using VBA scripts

This is the information you receive in the inbox.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Note : If you receive the error The transport failed to connect to the server , make sure you have entered the correct username, password, SMTP server and port number in the code line listed at With SMTP_Config .

Enhance and automate the whole process

Sending emails from Excel with one button is really safe and convenient. If you want to use this feature often, you can automate it.

To do this, you need to change the macro. Go to the Visual Basic Editor, copy and paste the entire code gathered. Next, select ThisWorkbook from the Project hierarchy .

From the two drop-down fields at the top of the code window, select Workbook and click Open from the Methods menu . Paste the email script above into Private Sub Workbook_Open () . This action will run the macro whenever you open the Excel file.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Next, open Task Scheduler . You will use this tool to ask Windows to automatically open spreadsheets at specified intervals. At that time, your macro will be activated and emailed.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Select Create Basic Task ... from the Action menu and follow the instructions until you reach the Action screen.

Select Start a program and click Next .

Instructions for sending emails from Excel spreadsheets using VBA scripts

Use the Browse button to find the Microsoft Excel location on the computer or copy & paste the path in the Program / script field .

Then enter the path to the Microsoft Excel document in the Add arguments field .

Complete instructions and schedule you will be made.

You should test this feature by scheduling the action in the next few minutes, then revising the task after you see it working effectively.

Note : You may need to adjust the Trust Center settings to make sure that macro runs as follows:

Open the spreadsheet and navigate to File> Options> Trust Center .

Here, click Trust Center Settings . On the next screen, select Never show information about blocked content .

Microsoft Excel is an extremely powerful tool, but learning how to use it can sometimes be challenging for many. If you want to master this software, you need to know how to use VBA. It is not easy.

However, with a little VBA experience, you can automate basic tasks on Microsoft Excel and have more time focusing on more important tasks.

Hope the article is helpful to you. Good luck!

Sign up and earn $1000 a day ⋙

Leave a Comment

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Discover the latest updates in Zooper Widget for Android. Zooper Widget is a compact, highly customizable, and battery-friendly widget with endless possibilities.

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS, alongside Dubsmash and Musical.ly, provides iPhone and iPad users with a new platform to create lip sync videos and dub videos readily.

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

Learn how to share audio using TeamViewer, including the latest updates for 2024 and 2025, making remote communication more effective.

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Learn the latest tips on how to pet a cat in Adorable Home for optimal gameplay in 2024 and 2025. Discover what positions give you hearts.

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

Explore the latest features of OrangeHRM Open Source, a free HR management system tailored to meet evolving business needs in 2024 and 2025.

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

Fix IDM not catching file download links. Discover solutions for downloading videos, music, and more with the latest IDM updates in 2024 and 2025.

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

Discover the latest version of The File Converter for Android, an application that converts various file formats efficiently.

Updated Zalo Web Login Features for 2024-2025

Updated Zalo Web Login Features for 2024-2025

Explore the latest features of Zalo Web, allowing users to chat and connect seamlessly on various browsers without installation.

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover is a powerful tool for removing passwords and restrictions from PDF files. Download the latest version for 2024 to experience enhanced features.

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Step-by-step instructions to completely remove Internet Download Manager (IDM) from your system in 2024 and 2025. Learn to uninstall IDM effectively and cleanly.

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Discover Mindjet MindManager, an innovative software that helps individuals and teams streamline their work processes through effective mind mapping and project management.

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su DoKu 2.0.0 is an advanced Sudoku game design software that captivates enthusiasts with its simplicity and functionality. Download now and challenge yourself with Sudoku puzzles.

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Discover the new features of Google Photos for Android in 2024 and 2025, including faster backup and advanced search capabilities.

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Discover the latest version of IQmango DVD Ripper in 2024, a powerful software to easily rip, copy, convert, and back up DVDs.

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet is an easy-to-use group meeting scheduling tool for organizing collaborative meetings. Discover the latest features of When2meet for 2024 and 2025.