How to Send Emails From an Excel Spreadsheet Using VBA Scripts

Rate this post
How to Send Emails From an Excel Spreadsheet Using VBA Scripts

It simply takes a few basic scripts to send emails from Microsoft Excel. When you add this capability to your spreadsheets, Excel becomes considerably more powerful.

Excel macros may do many of the same functions as VBA scripts, but without requiring as much programming experience. VBA allows you to construct more complicated processes, such as making a spreadsheet report containing all of your PC’s data.

Would you rather see this instruction as a video? We’ve got your back!

Why Send Email From Excel?

You may wish to send an email from inside Microsoft Excel for a variety of reasons.

Perhaps you have employees that update papers or spreadsheets every week and would want to be notified via email when they do so. You might also have a spreadsheet of contacts and wish to send a single email to all of them at once.

You may believe that scripting an email broadcast from Excel would be difficult. That is not at all the case. The method in this post takes advantage of a long-available feature in Excel VBA called Collaboration Data Objects (CDO).

CDO is a communications component that has been utilized in Windows from the very first versions of the operating system. It was formerly known as CDONTS, but with the introduction of Windows 2000 and XP, it was renamed “CDO for Windows 2000.” This component is already installed and ready to use in your VBA installation in Microsoft Word or Excel.

Using the component makes it relatively simple to send emails from inside Windows programs using VBA. In this example, you’ll utilize Excel’s CDO component to send an email with the contents of a particular Excel cell.

Step 1: Prepare Your Gmail Account

We’ll use Gmail to send email from Microsoft Excel, but you may modify the macro below to work with other email clients. Because Gmail no longer allows third-party app access, you must first activate Gmail’s 2-step authentication.

Click App passwords from your Google account’s Security page, under Sign in to Google. Locate the Pick app drop-down option on the App passwords screen and select Mail. Select Windows Computer from the Select device menu. Then press the GENERATE button.

Make a note of the 16-character app password; you’ll need it when configuring the macro.

Step 2: Create a VBA Macro

Save the Excel file you’ll be working with as a Macro-Enabled Workbook, i.e. in the XLSM format, before you begin.

  How to Automatically Forward Emails From Outlook to Gmail (and Vice-Versa)

We’ll need the Excel Developer tab first. If you don’t see it, follow these steps to activate it:

  1. Go to File > Options.
  2. Check the Developer box under Customize the Ribbon > Main Tabs.
  3. To save your changes, click OK.

Click Insert in the Controls box within Excel’s Developer tab, and then choose a command button.

Create a new macro for it by clicking on Macros in the Developer ribbon after drawing it into the sheet.

When you click the Create button, it’ll open the VBA editor.

Navigate to Tools > References in the editor to add the reference to the CDO library.

Continue to the bottom of the list until you locate Microsoft CDO for Windows 2000 Library. Check the box and then click OK.

Make a note of the name of the function where you’re copying the script when you click OK. It will come in handy later.

Step 3: Configure Your Macro

You can now build the mail objects and configure all of the information required to send an email. Remember that although many of the entries are optional, the From and To fields are mandatory. Copy and paste the following code snippets into your Module1 (Code) window.

This is what the complete code looks like:

Sub Send_Emails()
Dim NewMail As CDO.Message
Dim mailConfig As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
On Error GoTo Err:
'early binding
Set NewMail = New CDO.Message
Set mailConfig = New CDO.Configuration
'load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.From = ""
.To = ""
.CC = ""
.BCC = ""
.Subject = "Send Email From an Excel Spreadsheet"
.TextBody = "This is the body of your email. And here is some added data:" & Str(Sheet1.Cells(2, 1))
.Addattachment "c:\data\email.xlsx" 'Optional file attachment; remove if not needed.
.Addattachment "c:\data\email.pdf" 'Duplicate the line for a second attachment.
End With
msConfigURL = ""
With fields
.Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication
.Item(msConfigURL & "/smtpauthenticate") = 1'SMTP authentication Enabled
.Item(msConfigURL & "/smtpserver") = "" 'Set the SMTP server details
.Item(msConfigURL & "/smtpserverport") = 465'Set the SMTP port Details
.Item(msConfigURL & "/sendusing") = 2 'Send using default setting
.Item(msConfigURL & "/sendusername") = "" 'Your gmail address
.Item(msConfigURL & "/sendpassword") = "password" 'Your password or App Password
.Update 'Update the configuration fields
End With
NewMail.Configuration = mailConfig

MsgBox "Your email has been sent", vbInformation
'Release object memory
Set NewMail = Nothing
Set mailConfig = Nothing
Select Case Err.Number
Case -2147220973'Could be because of Internet Connection
MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
Case -2147220975'Incorrect credentials User ID or password
MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description
Case Else 'Report other errors
MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description
End Select
Resume Exit_Err
End Sub

And these are the sections and fields you need to customize:

  • Using NewMail: This part provides all of the settings for sending your email, including the email body. The . The From box must include your Gmail address, but the other fields may be filled in whatever you see fit. For the body, you may put together the message by inserting data from any of the Microsoft Excel sheets directly into the email message, as seen above. You may also include one or more files.
  • With fields: This is where you adjust your Gmail account’s SMTP settings. When duplicating the code, leave the smtpserver and smtpserverport fields alone. Fill up the boxes using your Gmail account and the 16-digit app password.
  How to Set Up Your Own Secure Email Server

Step 4: Test Your Macro

To test the macro, navigate to Run > Run Sub/User Form or press F5 in the VBA editor. If your email is not sent, you should get an error notice. Otherwise, you’ll get a message confirming that your email was properly delivered.

If you see an error message that says If the transport fails to connect to the server, double-check that you’ve typed the right username, password, SMTP server, and port number in the lines of code specified under the With fields.

Step 5: Connect the Command Button to Your Script

Go to the code editor and double-click Sheet1 to examine the VBA code for that worksheet. Choose your button, for example, CommandButton1, from the drop-down menu on the left, then describe the action on the right; click works. Then, in the function where you put the script above, enter the name Send Emails.

When you return to your spreadsheet, click the button to send the email.

Here is an example of how the message should appear in your inbox:

Take It Further and Automate the Whole Process

It’s great to be able to send email from Excel with the click of a button. However, if you want to utilize this capability often, it makes sense to automate the process. You’ll need to make a tweak to the macro to do this. Go to the Visual Basic Editor and copy and paste the whole code you’ve written thus far.

Then, from the VBAProject hierarchy, double-click ThisWorkbook.

Select Workbook from the two selection boxes at the top of the code window, then Open from the Methods dropdown.

  How to Prevent Your Emails From Going to Spam: 6 Tips

Paste the email script above into Sub Workbook_Open().

This will run the macro whenever you open up the Excel file.

Then, launch Task Scheduler. You’ll use this tool to tell Windows to open the spreadsheet at regular intervals, at which time your macro will execute and send the email.

Select Create Basic Task… from the Action menu and go through the wizard until you reach the Action screen.

Click Next after selecting Start a program. To locate Microsoft Excel on your computer, use the Browse button, or copy and paste the path into the Program/script area. Then, in the Add arguments section, add the path of your Microsoft Excel document. When you finish the wizard, your schedule will be in place.

It’s recommended testing the action by scheduling it for a few minutes in the future, then modifying the job after you’ve confirmed that it’s functioning.

To guarantee that the macro works successfully, you may need to alter your Trust Center settings.

To do this, open the spreadsheet and go to File > Options > Trust Center. Click Trust Center Settings… from here, then on the following page, change the radio dial to Never display information about banned material.

Make Microsoft Excel Work for You

Microsoft Excel is an extremely powerful tool, but learning how to use it effectively may be scary. To properly understand the program, you’ll need to be familiar with VBA, which is no easy undertaking.

The outcomes, on the other hand, speak for themselves. With a little VBA expertise under your belt, you’ll soon be able to automate simple processes in Microsoft Excel, allowing you more time to focus on more urgent concerns.

You are looking for information, articles, knowledge about the topic How to Send Emails From an Excel Spreadsheet Using VBA Scripts on internet, you do not find the information you need! Here are the best content compiled and compiled by the team, along with other related topics such as: Email.

Similar Posts