Add a Reference of Microsoft CDO in VBA
You'll need to first add a reference of Microsoft CDO for Windows library (cdosys.dll) in your VBA project.
Follow these steps.
1) Open VBA editor.
2) From the top menu click Tools -> References…. It will open the References dialog box.
3) Find Microsoft CDO for Windows 2000 Library and click the Check box.
4) Press the OK button.
CDO stands for Collaboration Data Objects. You can learn more about it here.
Adding a reference will ensure that you have access to all the properties and methods to send emails etc. from your VBA application.
The Macro to Send Email from Excel
Copy the below macro in your worksheet and press F5 (to the run macro).
Option Explicit Private Sub sendEmail() On Error GoTo ErrHandler ' Create a CDO object. (This is Early Binding, since we have added a reference) Dim oGmail As CDO.Message Set oGmail = New CDO.Message ' OR (using Late Binding). 'Set oGmail = CreateObject("CDO.Message") Dim sConfigURL As String sConfigURL = "http://schemas.microsoft.com/cdo/configuration/" ' Configure Gmail account with SMTP settings. With oGmail.Configuration.fields .Item(sConfigURL & "smtpauthenticate") = 1 .Item(sConfigURL & "smtpusessl") = True ' SMTP server and port Details. (Outgoing Mail) .Item(sConfigURL & "smtpserver") = "smtp.gmail.com" .Item(sConfigURL & "smtpserverport") = 465 ' Google... "gmail smtp server port" to know about gmail post. .Item(sConfigURL & "sendusing") = 2 ' Gmail username and password (Get App Password if 2-step verification is enabled in Gmail). .Item(sConfigURL & "sendusername") = "firstname.lastname@example.org" .Item(sConfigURL & "sendpassword") = "xxxxxxxxxxezvc" ' App Password. .Update ' Update the fields. End With ' Set email properties. With oGmail .From = "email@example.com" .To = "firstname.lastname@example.org" .CC = "" .BCC = "" .Subject = "This is a test message" .TextBody = "Hello, I am Arun Banik" ' Plain text message. ' Message with markup and style. '.HTMLBody = "<div style='padding: 10px; border: solid 1px #ddd; color: red;'>Hello, I am Arun Banik</div>" .Send ' Finally, send the email. End With Set oGmail = Nothing ErrHandler: If Err.Number = -2147220975 Then ' If you see this error, most probably your Gmail account has 2-step Verification enabled ' and you need to get an App Password. You can remove the App Password and create a new one. MsgBox "Invalid username or password" End If End Sub
I am assuming you have a Gmail account. If you don’t, create an account. Its free and is loaded with tons of features.
Configuring Gmail in Excel
You’ll need a Gmail account. The macro (or the code above) uses your Gmail username and password to emails.
See the above code again. While configuring Gmail account with SMTP settings, I have assigned a username and password.
.Item(sConfigURL & "sendusername") = "email@example.com" .Item(sConfigURL & "sendpassword") = "xxxxxxxxxxezvc" ' App Password.
The password, in particular, is very important here. If your Gmail account has 2-step verification enabled (it’s a security option in Gmail), then you need to get an App Password and assign it to the "field item" with password.
Get Gmail "App Password"
Getting your Gmail app password is simple. Just follow these steps.
1) Sign-in to your Gmail account.
2) Go to Manage Your Google Account. You can find it at right corner of your Gmail account.
3) Click the Security option in the left. Scroll down to find App passwords option under "Signing in to Google". Click "App passwords" option and it will open the app passwords page. (Or simply, Open App Passwords page.)
4) Under Select app, choose Mail.
5) Under Select device, choose Windows Computer.
6) Click the Generate button.
7) In the Generated app password dialog, you’ll find the password inside a yellow box. Copy it and assign to the field item (in your macro) for password.
After you have completed configuring Gmail, its time to configure the Email properties like address (to and from), the subject and body of the email, CC, BCC (if any) etc.
With oGmail .From = " firstname.lastname@example.org" .To = "email@example.com" .CC = "" .BCC = "" .Subject = "This is a test message" .TextBody = "Hello, I am Arun Banik" ' Plain text message. '.HTMLBody = "<div style='padding: 10px; border: solid 1px #ddd; color: red;'>Hello, I am Arun Banik</div>" .Send End With
If you have noticed, the body of the email can either be a "plain text" message or it can be an HTML body (with markup and style).
In-addition, you can attach a file (document or image) within the Email properties.
How to make this a little more Dynamic?
In the above example, I have hardcoded the "To" address while sending the email. You can however, extract email ids from your worksheet (if you a have list). Check this out.
Do you know...
You can email an entire table (or multiple tables) in Excel as an HTML table in the email body. Yes, you can. See this example.