How to Send Emails from Excel Using VBA

If you often need to send emails from Excel, you can automate the process using VBA. This feature is helpful if you need to send out notifications, reports, or reminders to multiple recipients from an Excel worksheet.

In this guide, we’ll explain how to set up a simple macro to send emails via Outlook directly from an Excel spreadsheet.

Prerequisites

Microsoft Outlook: You’ll need Outlook installed and configured on your computer.
VBA enabled: Make sure that macros and VBA are enabled in Excel.
Basic knowledge of VBA: While this guide explains the steps in detail, some familiarity with VBA will help you understand and tweak the code.

How to Set Up a Basic VBA Macro to Send Emails

Step 1: Open the Excel Workbook

Open the Excel workbook from which you want to send emails.
Go to the Developer Tab. If you don’t see it, you can enable it by:
- Clicking File > Options > Customize Ribbon, then check Developer.

Step 2: Open the VBA Editor

Click on Visual Basic (or press Alt + F11) to open the VBA Editor.
In the VBA Editor, go to Insert > Module. This will create a new module where you can write the VBA code.

Step 3: Write the VBA Code

Here’s an example of VBA code to send an email using Outlook:

vbaCopyEditSub SendEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range
    Dim emailAddress As String
    Dim emailSubject As String
    Dim emailBody As String

    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    ' Define email parameters
    emailSubject = "Test Email from Excel"
    emailBody = "Hello, this is a test email sent from Excel using VBA."

    ' Loop through the email addresses in Column A
    For Each cell In Range("A2:A5") ' Adjust the range as needed
        emailAddress = cell.Value

        If emailAddress <> "" Then
            With OutlookMail
                .To = emailAddress
                .Subject = emailSubject
                .Body = emailBody
                .Send ' Send email
            End With
        End If
    Next cell

    ' Cleanup
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

    MsgBox "Emails Sent Successfully!"
End Sub

Step 4: Customize the Code

Range(“A2:A5”): Adjust this range to match the cells containing the email addresses you want to send to. For example, if your email addresses are in column B from row 2 to 10, change it to Range("B2:B10").
emailSubject: Set the subject of the email.
emailBody: Customize the body of the email.
.Send: This sends the email immediately. You can also use .Display if you want to review the email before sending it.

Step 5: Run the Macro

Close the VBA editor (Alt + Q).
Back in Excel, go to the Developer Tab and click Macros.
Select SendEmail and click Run.

This will send an email to all the addresses listed in the specified range.

How to Send Personalized Emails

You can personalize the email body for each recipient by referencing different cells for the recipient’s name or other details. Here’s an example of how to personalize the email:

vbaCopyEditSub SendPersonalizedEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range
    Dim emailAddress As String
    Dim recipientName As String
    Dim emailSubject As String
    Dim emailBody As String

    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    ' Define email subject
    emailSubject = "Personalized Email from Excel"
    
    ' Loop through the email addresses in Column A and Names in Column B
    For Each cell In Range("A2:A5") ' Adjust the range as needed
        emailAddress = cell.Value
        recipientName = cell.Offset(0, 1).Value ' Assuming the name is in Column B

        If emailAddress <> "" Then
            emailBody = "Hello " & recipientName & "," & vbCrLf & vbCrLf & _
                        "This is a personalized email sent from Excel using VBA."

            With OutlookMail
                .To = emailAddress
                .Subject = emailSubject
                .Body = emailBody
                .Send ' Send email
            End With
        End If
    Next cell

    ' Cleanup
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

    MsgBox "Personalized Emails Sent Successfully!"
End Sub

This code sends a personalized greeting (using the name in Column B) to each email address in Column A.

Best Practices for Sending Emails from Excel

Test with a few emails: Before sending out a large batch of emails, test the macro with a few email addresses to ensure everything works correctly.
Check your Outlook settings: Ensure that Outlook is set up properly and is the default mail client on your system.
Ensure Excel is saved: Always save your workbook before running macros. This helps in case of crashes or other issues.
Use Display instead of Send for testing: If you’re testing, use .Display instead of .Send to open the email draft before actually sending it.

Troubleshooting

Error: “Automation Error” or “Cannot create object”: This usually happens if Outlook is not installed or not set as the default email client. Ensure Outlook is properly configured.
Emails Not Sending: If the macro isn’t sending emails, check if the Outlook security settings are blocking automatic email sending. You may need to adjust security settings in Outlook.
Outlook Settings: Ensure that your Outlook is configured to allow external applications (like Excel) to send emails.

Related Resources

Frequently Asked Questions (FAQs)

Can I send emails from Excel without using Outlook?
While Outlook is the most common email client used with Excel, it’s possible to use other services like Gmail or SMTP servers by writing custom VBA code. However, this is more advanced and requires setting up specific configurations.

Can I send emails with attachments using Excel VBA?
Yes, you can send emails with attachments using the .Attachments.Add method. Here’s an example:

vbaCopyEditOutlookMail.Attachments.Add "C:\path\to\file.xlsx"

How do I stop an email from being sent automatically?
To stop the email from being sent immediately, replace .Send with .Display. This will open the email draft for you to review before sending.

Conclusion

Automating email sending from Excel using VBA is a great way to save time and streamline your email communications. Whether you’re sending reminders, reports, or notifications, the steps outlined above will help you create a basic automated email system. You can customize it further to suit your specific needs and make the process more efficient.

Let me know if you need more advanced features or help with specific email configurations!