VBA - How to Send Email in Excel without Outlook

Well, you can send emails from Excel using Outlook and a simple Macro. But no worries if you don't have access to Outlook or for some reason don't want to use Outlook from Excel. You can still send emails (with attachments etc.) from Excel using your Gmail account and VBA. I'll show you how.

send email from excel through gmail using vba

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.

microsoft cdo library in vba

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") = "xyx@gmail.com"
        .Item(sConfigURL & "sendpassword") = "xxxxxxxxxxezvc"       ' App Password.

        .Update  ' Update the fields.
    End With

    ' Set email properties.
    With oGmail
       .From = "xyz@gmail.com"
       .To = "arunbanik21@rediffmail.com"
       .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") = "xyx@gmail.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.)

app passwords for gmail 2-step verification

4) Under Select app, choose Mail.
5) Under Select device, choose Windows Computer.
6) Click the Generate button.

generate app password in your google account

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.

google app password sample

Finally, Set Email Properties and Send Email

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 = " xyz@gmail.com"
    .To = "arunbanik21@rediffmail.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.

add attachement to email using vba

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.

← PreviousNext →