VBA Code to Send Email from Excel Using Gmail SMTP

You can send email from Excel VBA through Gmail’s SMTP server using the Windows CDO library: create a Gmail app password, configure SMTP on port 465 with SSL, then call Send. The example below sends a plain-text message; it is intended for Excel for Microsoft 365 on Windows, because CDO is a Windows component and does not work in Excel for the web or as written on Mac.

VBA code for sending a Gmail message

The sample uses late binding, so you do not need to add a CDO reference in the VBA editor. Replace the example addresses and password placeholder before running it. The password should be a Google app password, not your normal Google account password.

Option Explicit

Sub SendEmailWithGmail()
    Dim CDOConfig As Object
    Dim CDOMessage As Object
    Dim gmailAddress As String
    Dim appPassword As String
    Dim recipient As String

    On Error GoTo SendFailed

    ' Replace these examples with your Gmail details and recipient.
    gmailAddress = "youraddress@gmail.com"
    appPassword = "PASTE_YOUR_16_CHARACTER_APP_PASSWORD"
    recipient = "recipient@example.com"

    Set CDOConfig = CreateObject("CDO.Configuration")
    Set CDOMessage = CreateObject("CDO.Message")

    With CDOConfig.Fields
        .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/smtpserverport") = 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = gmailAddress
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = appPassword
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Update
    End With

    With CDOMessage
        Set .Configuration = CDOConfig
        .From = gmailAddress
        .To = recipient
        .Subject = "Message from Excel"
        .TextBody = "Hello," & vbCrLf & vbCrLf & _
                    "This message was sent from an Excel VBA macro."
        .Send
    End With

    MsgBox "Email sent.", vbInformation
    Set CDOMessage = Nothing
    Set CDOConfig = Nothing
    Exit Sub

SendFailed:
    MsgBox "Email was not sent. Error " & Err.Number & ": " & _
           Err.Description, vbExclamation
    Set CDOMessage = Nothing
    Set CDOConfig = Nothing
End Sub

Before running any macro, save a separate copy of the workbook. Excel does not provide an undo command for macro actions, and a future version of a sending macro could email many rows or make other changes at once.

Set up the workbook and run the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A regular .xlsx file cannot retain VBA code.
On Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, use Developer > Visual Basic; depending on the keyboard settings, Option+F11 or Fn+Option+F11 may also open it.
In the editor, choose Insert > Module and paste the code into the new module.
Replace the Gmail address, app-password placeholder, and recipient with the correct values. Review the subject and message text too.
Return to Excel and run SendEmailWithGmail from Developer > Macros. If Excel displays a security warning, only enable macros for a workbook you trust.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. The Windows instructions above also do not apply directly to Mac: the CDO objects used in the example are Windows-specific. A cross-platform workflow needs a different sending method, such as a service with an API or an automation platform.

Create a Gmail app password

Gmail SMTP requires authentication. For the example’s username-and-password method, use a Google app password if your account offers that option. App passwords are separate, randomly generated credentials for an app or device; they are not your normal sign-in password. Google generally requires 2-Step Verification before an app password can be created, and some account types or security configurations do not offer app passwords. A Google Workspace administrator can also restrict the available methods.

Sign in to the Google Account that will send the email, open its Security settings, and look for App passwords after enabling 2-Step Verification. The precise Google account interface can change. Generate a password for this use, then enter it in the macro in place of the placeholder. If Google shows the password in groups, enter the characters without display spaces. Do not try your everyday Google password if app-password access is unavailable; it will commonly be rejected, and repeatedly retrying does not fix an account-policy restriction.

An app password still grants access to the account through the supported sign-in method, so treat it as a credential. Revoke it from Google Account security settings when it is no longer needed, if it is exposed, or when you retire the macro. For company or school accounts, ask the Workspace administrator which approved sending method is available rather than attempting to bypass a policy.

How the SMTP settings work

SMTP is the protocol an email program uses to submit outgoing mail. This macro connects to Gmail’s SMTP host, authenticates as the sending account, and submits the message for delivery. The sendusing value selects network SMTP, while the server name identifies Gmail’s submission service. The sender address in .From should normally match the authenticated Gmail account or an address configured as an approved send-as alias.

smtp.gmail.com is Gmail’s SMTP server for authenticated sending.
Port 465 is used here with SSL enabled. Gmail also documents port 587 for TLS connections, but this particular CDO example uses the 465 configuration.
smtpauthenticate = 1 requests username-and-password authentication. The username is the full Gmail address, and the password is the app password.
.TextBody creates a plain-text email. It avoids HTML formatting issues and is suitable for simple notifications.

SMTP submission is not the same as a guarantee that the recipient will read or even receive the message. Gmail accepts the message for processing, but it can still be delayed, filtered as spam, rejected by a recipient’s mail system, or affected by account sending limits and policies. For recurring or bulk mail, check Google’s current account-specific limits and rules rather than assuming that an automation macro can send without restriction.

Personalize the recipient, subject, and message

For a one-off notification, hard-coded message text is convenient. For a spreadsheet-driven message, assign values from cells to VBA variables before setting the CDO message properties. For example, a macro could read a recipient from a designated cell and a subject from another cell. Validate that the recipient cell is not blank and contains an address in the expected format before sending; VBA string handling alone does not establish that an address exists or can receive mail.

You can put multiple recipients in the .To property using semicolons, or set carbon-copy and blind-carbon-copy properties as needed. Confirm the exact recipient list before sending, especially if a worksheet has many rows. For personalized mail, a safer design is to test with your own address first and add a confirmation step before the final send. Avoid sending sensitive spreadsheet contents to an address simply because it appears in a cell.

To send formatted HTML instead of plain text, CDO has an HTML body property, but message content must be correctly encoded and any inserted spreadsheet values should be handled carefully. Plain text is the simpler choice when formatting is unnecessary. If a message needs a file attachment, CDO supports adding an attachment by file path; check that the file exists, that the recipient should receive it, and that it contains no unintended data before attaching it.

Common errors and practical fixes

The macro’s error handler displays the error number and description from VBA or CDO. Those details can narrow the cause, but the same connection error may have more than one explanation. Check the following before changing code:

Authentication fails: Confirm that the full Gmail address is the username and that the app password is current and entered without accidental spaces. Check that 2-Step Verification and the account’s security policies allow this method.
Connection times out: Verify internet access and check whether a firewall, VPN, antivirus product, or company network blocks outbound SMTP connections on port 465.
CDO cannot be created: An error such as “ActiveX component can’t create object” indicates that the CDO component is unavailable or inaccessible on that Windows installation. This is a platform dependency, not a Gmail password problem.
The sender is rejected: Make sure the From address is the authenticated account or a properly configured Gmail send-as address. Google may reject an unapproved sender identity.
The code says it sent, but no message appears: Check Gmail’s Sent folder, the recipient’s spam folder, spelling of the recipient address, and any bounce or delivery notice. A successful SMTP submission is not proof of inbox delivery.

Do not respond to a failure by disabling account protections or installing an unknown “SMTP fix.” First identify whether the error is an authentication, network, component, sender-identity, or recipient-delivery issue. If a managed account disallows this authentication route, use an administrator-approved alternative.

Security and reliability considerations

The sample keeps the app password in the VBA source, which makes it easy to understand but unsuitable as a secure credential store. Anyone who can inspect the workbook or its VBA project may be able to retrieve a hard-coded secret. Protecting a sheet or hiding a module is not equivalent to securely storing credentials. Do not distribute a workbook containing a live app password, and do not commit it to a shared source-code repository.

For a personal, low-risk experiment, use a dedicated account where appropriate, restrict access to the workbook, and revoke the app password when finished. In a business workflow, consult the organization’s security team. OAuth-based access through an approved API or a managed mail service is generally a better fit for centrally administered automation, although implementation and permissions require more setup than this short macro.

Also account for accidental repeat sends. If a macro loops through rows, a misplaced range or repeated run can send duplicates. Add a test mode, explicit confirmation, and a sent-status column or other duplicate prevention where appropriate. Keep a small test recipient list until the message content, addressing, attachments, and stopping conditions have all been checked.

Alternatives to CDO and Gmail SMTP

CDO is a compact approach for Windows VBA, but it is not the only choice and is not suitable for every environment. If Outlook desktop is configured, VBA can automate Outlook to create and send a message through an Outlook profile. That approach depends on Outlook being installed and configured, may trigger security or organizational controls, and sends through the account configured in Outlook rather than automatically using Gmail SMTP.

For a robust Gmail integration, the Gmail API uses OAuth authorization rather than putting an account password in a workbook. It involves Google Cloud project configuration, API permissions, OAuth consent, and token handling, so it is more involved than the CDO sample. A business may instead use an administrator-approved Google Workspace SMTP relay or an approved workflow platform, depending on its policy and needs. Excel for the web users can consider Power Automate or another approved service because VBA does not run in the browser.

Choose based on where the workbook runs, who maintains it, and how sensitive the data and credentials are. A one-time personal notification on Windows may justify a small SMTP macro. A shared process, high-volume mailing, regulated data, or a workbook used across Windows, Mac, and web generally calls for an OAuth or managed automation design rather than embedding a Gmail secret in VBA.

Frequently Asked Questions

Can I use my regular Gmail password in Excel VBA?
No. Google does not generally accept a normal account password for this kind of SMTP sign-in. Use an app password only if Google makes it available for the account, or choose an OAuth-based or administrator-approved alternative.

Does this Gmail VBA code work on a Mac?
Not as written. It creates Windows CDO objects, which are Windows-specific. Excel for Mac can run VBA, but this code’s mail component is not a cross-platform solution; use another supported mail or API workflow.

Can Excel for the web run this macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel on Windows for this CDO example, or use an online automation service designed for browser-based workbooks.

Can the macro attach a file or send HTML email?
Yes, CDO supports attachments and HTML message bodies, but the sample deliberately sends plain text and has no attachment. Add those features only after validating file paths, content, recipients, and formatting, and test with a safe address first.

Why does Gmail reject the app password?
Check that it belongs to the same account as the SMTP username, is current, and was entered correctly. The account may not support app passwords, or a Workspace administrator or Google security setting may prohibit them. In that case, use an approved alternative instead of weakening account security.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.