To send emails from Microsoft Excel only requires a few simple scripts. Add this functionality to your spreadsheets and you can really enhance how much you can accomplish in Excel.
We’ve covered a lot of great Excel macros, which can accomplish the same things VBA scripts can, but without the need for programming knowledge. But there are many advanced things you can only do with VBA, like creating a spreadsheet report with all of your PC information.
Why Send Email From Excel?
There are a lot of reasons why you might want to send an email from inside Microsoft Excel.
Maybe you have staff that updates documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates are done. Or you might have a spreadsheet of contacts and you want to send one email to all of them at once.
You’re probably thinking that scripting an email broadcast from Excel is going to be complicated. That’s not the case at all.
The technique in this article will make use of a feature that’s been available in Excel VBA for a long time, Collaboration Data Objects (CDO).
CDO is a messaging component used in Windows since very early generations of the OS. It used to be called CDONTS, and then with the advent of Windows 2000 and XP, was replaced with “CDO for Windows 2000”. This component is already included in your VBA installation within Microsoft Word or Excel and is ready for use.
Using the component makes sending emails from within Windows products with VBA extremely easy. In this example, you’ll use the CDO component in Excel to send out an email that will deliver the results from a specific Excel cell.
Step 1: Create a VBA Macro
The first step is to go to the Excel Developer tab.
Inside the Developer tab, click on Insert in the Controls box, and then select a command button.
Draw it into the sheet and then create a new macro for it by clicking on Macros in the Developer ribbon.
When you click the Create button, it’ll open the VBA editor.
Add the reference to the CDO library by navigating to Tools > References in the editor.
Scroll down the list until you find Microsoft CDO for Windows 2000 Library. Mark the checkbox and click OK.
When you click OK, make note of the name of the function where you’re pasting the script. You’ll need it later.
Step 2: Set Up the CDO “From” and “To” Fields
To do this, you first need to create the mail objects and set up all of the fields that are necessary to send the email.
Keep in mind that while many of the fields are optional, the From and To fields 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 = "rdube02@gmail.com"
strTo = "rdube02@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))
The cool thing about this is that you can create up any string you want to customize a full email message and assign it to the strBody variable.
Piece together components of the message by using the & string to insert data from any of the Microsoft Excel sheets right into the email message, just like shown above.
Step 3: Configure CDO to Use an External SMTP
The next section of code is where you will configure CDO to use any external SMTP server to send the email.
This example is a non-SSL setup through Gmail. CDO is capable of SSL, but that’s outside the scope of this article. If you need to use SSL, this advanced code in Github can help.
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@website.com"
.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: Finalize the CDO Setup
Now that you’ve configured the connection to the SMTP server for sending the email, all you have to do is fill in the appropriate fields for the CDO_Mail object, and issue the Send command.
Here is how you do that:
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
There won’t be any pop-up boxes or security alert messages, which can happen when you resort to using the Outlook mail object.
CDO simply puts together the email and utilizes your SMTP server connection details to fire off the message. It’s the easiest way to incorporate email into Microsoft Word or Excel VBA scripts.
To connect your command button to this script, go into the code editor and click on Sheet1 to view the VBA code for that worksheet.
Type the name of the function where you pasted the script above.
Here’s what the message looked like that I received in my inbox:
Note: If you receive an error that reads The transport failed to connect to the server, make sure you’ve entered the correct username, password, SMTP server, and port number in the lines of code listed underneath With SMTP_Config.
Take It Further and Automate the Whole Process
It’s all well and good to be able to send email from Excel at the touch of a button. However, you might want to use this functionality on a regular basis, in which case it would make sense to automate the process.
To do so, you’ll need to make a change to the macro. Head to the Visual Basic Editor and copy and paste the entirety of the code we put together.
Next, select ThisWorkbook from the Project hierarchy.
From the two dropdown fields at the top of the code window, select Workbook and select Open from the Methods dropdown.
Paste the email script above into Private Sub Workbook_Open().
This will run the macro whenever you open up the Excel file.
Next, open up Task Scheduler.
You’re going to use this tool to ask Windows to open up the spreadsheet automatically at regular intervals, at which point your macro will be initiated, sending the email.
Select Create Basic Task… from the Action menu and work your way through the wizard until you reach the Action screen.
Select Start a program and click Next.
Use the Browse button to find Microsoft Excel’s location on your computer, or copy and paste the path into the Program/script field.
Then, enter the path to your Microsoft Excel document into the Add arguments field.
Complete the wizard, and your scheduling will be in place.
It’s worth running a test by scheduling the action for a couple of minutes in the future, then amending the task once you can confirm that it’s working.
Note: You may have to adjust your Trust Center settings to ensure that the macro runs properly.
To do so, open the spreadsheet and navigate to File > Options > Trust Center.
From here, click Trust Center Settings, and on the next screen set the radio dial to Never show information about blocked content.
Make Microsoft Excel Work for You
Microsoft Excel is an incredibly powerful tool, but learning how to get the most out of it can be a little intimidating. If you want to truly master the software, you’ll need to be comfortable with VBA, and that’s no small task.
However, the results speak for themselves. With a little VBA experience under your belt, you’ll soon be able to make Microsoft Excel perform basic tasks automatically, giving you more time to concentrate on more pressing matters.
It takes time to build expertise with VBA, but you’ll soon see the fruits of your labors if you can stick with it.
One great place to start is our authoritative tutorial on using VBA in Excel. Once you’re done with that, this simple script to send emails from Excel will feel like child’s play.
Read the full article: How to Send Emails From an Excel Spreadsheet Using VBA Scripts
Read Full Article
No comments:
Post a Comment