VBA macro to send emails using Gmail

Sending emails via VBA can be very useful. You can create powerful Excel macros, which help you automate your tasks and do your job faster and more efficiently.

In this guide, I will show how to create a simple Excel macro with VBA, which you can use to send emails via Gmail.

VBA script: send emails using Gmail

This is the complete script that you need to start sending emails via your Gmail account.

I have made the macro very easy to use, just change the configuration and you are good to go.

The configuration is available at the beginning of the macro, right after the following line:

'CONFIGURATION - EDIT THIS

Full VBA macro

'Macro to send emails using Gmail
'From: https://qdatalab.com
Sub SendEmail()

    Dim from, recipient, cc, bcc, password, subject, body, attachment As String, enable_html As Boolean
    
    'CONFIGURATION - EDIT THIS
    from = "[email protected]" 'Insert your own email
    recipient = "[email protected]" 'Insert recipient email
    cc = "" 'Insert CC email recipient (optional)
    bcc = "" 'Insert BCC email recipient (optional)
    password = "your password" 'Insert your Gmail password or App password (if you have 2-factor authentication enabled)
    subject = "Email subject" 'Email subject
    body = "Body text" 'The body text of the email
    attachment = "" 'Local path to file you want to attach to the email (optional)
    enable_html = False 'Set to True if you want to add HTML to the body text of the email (optional)

    'NO NEED TO EDIT ANYTHING BELOW THIS
    On Error GoTo Err
    Dim mailObj, configObj As Object, fields As Variant, msConfigURL As String
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    'Create objects
    Set mailObj = CreateObject("CDO.Message")
    Set configObj = CreateObject("CDO.Configuration")
    'Load default configuration
    configObj.Load -1
    Set fields = configObj.fields

    'Set email properties
    With mailObj
        .subject = subject
        .from = from
        .to = recipient
        .cc = cc
        .bcc = bcc
    End With
    
    'Add attachment if set
    If Not attachment = "" Then
        With mailObj
            .Addattachment attachment
        End With
    End If
    
    'Add body to email
    If enable_html = True Then
        With mailObj
            .htmlbody = body
        End With
    Else
        With mailObj
            .textbody = body
        End With
    End If

    'Set config properties
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        'Enable MTP authentication
        .Item(msConfigURL & "/smtpauthenticate") = 1
        'Set the SMTP server and port Details
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        'Set the credentials of email account
        .Item(msConfigURL & "/sendusername") = from
        .Item(msConfigURL & "/sendpassword") = password
        'Update the configuration fields
        .Update
    End With
    
    mailObj.Configuration = configObj
    mailObj.Send
    
    'Release object memory
    Set mailObj = Nothing
    Set configObj = Nothing
    
    Exit Sub

Exit_Err:
    'Release object memory
    Set mailObj = Nothing
    Set configObj = Nothing
    End

'Error handling
Err:
    Select Case Err.Number
        Case -2147220973
            MsgBox "Error: check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
        Case -2147220975
            MsgBox "Error: check your credentials." & vbNewLine & Err.Number & ": " & Err.Description
        Case Else
            MsgBox "An error ocurred." & vbNewLine & Err.Number & ": " & Err.Description
    End Select

    Resume Exit_Err

End Sub

Configuration

You need to fill in the following information:

• Your email
• Password or app password for your email account (see instructions below)
• Recipient email
• Subject of the email
• Text of the email

Besides this, there are some optional fields:

• CC recipient email
• BCC recipient email
• Attachment file
• Enable HTML in the body text

If you want to add an attachment to the email, you just need to add the local path to the attachment variable. Example:

attachment = "C:\Documents\filename.xls"

You can add any file you want.

If you want to enable HTML in the body text of the email, you need to set the enable_html variable to True:

enable_html = True

Windows Region

Some regions in Windows, will create conflicts due to the encoding. The code is tested to work on “English (United States)” and “English (United Kingdom)”, but others might work as well.

Creating an app password

If you have 2-factor authentication enabled on your Gmail account, you need to create an app password to use instead of your normal password.

Luckily, this is a fairly simple process.

1. Go to your Google account settings: https://myaccount.google.com
2. Click Security
3. Under the Signing in to Google tab, click on App passwords
4. Select app “Mail” and select device “Windows Computer” and click the GENERATE button
5. You have now generated an app password that you can use in your macro.

Creating dynamic macros to send emails

With the above VBA code, you should be able to create your own macros to send emails.

The code can easily be changed, to make it more dynamic. For example, you can create a sub/macro which takes email, subject, and text as arguments and automatically sends an email.

Below is an example of such macro.

'Macro to send emails using Gmail
'From: https://qdatalab.com
Sub SendEmail(recipient As String, subject As String, body As String, Optional enable_html As Boolean = False, Optional cc As String = "", Optional bcc As String = "", Optional attachment As String = "")
    
    Dim from, password As String
    
    'Configuration - EDIT THIS
    from = "[email protected]" 'Insert your own email
    password = "your password" 'Insert your Gmail password or app password (if you have 2-factor authentication enabled)

    'NO NEED TO EDIT ANYTHING BELOW THIS
    Dim mailObj, configObj As Object, fields As Variant, msConfigURL As String
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    'Create objects
    Set mailObj = CreateObject("CDO.Message")
    Set configObj = CreateObject("CDO.Configuration")
    'Load default configuration
    configObj.Load -1
    Set fields = configObj.fields

    'Set email properties
    With mailObj
        .subject = subject
        .from = from
        .to = recipient
        .cc = cc
        .bcc = bcc
    End With

    'Add attachment if set
    If Not attachment = "" Then
        With mailObj
            .Addattachment attachment
        End With
    End If
    
    If enable_html = True Then
        With mailObj
            .htmlbody = body
        End With
    Else
        With mailObj
            .textbody = body
        End With
    End If

    'Set config properties
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        'Enable MTP authentication
        .Item(msConfigURL & "/smtpauthenticate") = 1
        'Set the SMTP server and port Details
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        'Set the credentials of your Gmail account
        .Item(msConfigURL & "/sendusername") = from
        .Item(msConfigURL & "/sendpassword") = password
        'Update the configuration fields
        .Update
    End With
    
    mailObj.Configuration = configObj
    mailObj.Send
    
    'Release object memory
    Set mailObj = Nothing
    Set configObj = Nothing
End Sub

The above macro, can be called from any script.

There are two ways to insert the required arguments.

Example without named arguments:

Call SendEmail("[email protected]", "Email subject", "Email text")

Example with named arguments:

Call SendEmail(recipient:="[email protected]", subject:="Email subject", body:="Email text", enable_html:=True)

Please note, that if you do not use named arguments, you need to fill in the arguments in the same order as they are set in the macro.

Security

Since you are storing your login credentials in the VBA code, you should not share the macro or code with anyone, as they will then have access to your email account credentials.

I further encourage anyone that uses the above macro to put a password on the VBA project. This ensures that in the case that someone gains access to the Excel file containing the macro, they will not be able to read your login credentials from the code without the password for the project.

How to password protect a VBA project

To password protect the VBA project:

• In the VBA editor, go to the Tools menu and select VBAProject Properties…
• On the Protection tab, select the Lock project for viewing checkbox.
• Enter a password and confirm it and click OK.
• Save your workbook.