How to Send Emails Automatically from Excel Using VBA (Step-by-Step Guide with Code Example)

Oct 30, 2025

—

In today’s corporate environment, automation plays a key role in improving efficiency and accuracy. Many professionals rely on Microsoft Excel for managing data, reports, and daily business activities. However, when it comes to sending regular updates, invoices, performance reports, or reminders through email, manually sending them can be time-consuming.

This is where Excel VBA (Visual Basic for Applications) becomes an extremely powerful tool. With the help of VBA, you can automate the process of sending emails directly from Excel through Microsoft Outlook. Whether you want to send a personalized email to hundreds of clients or just send automated status reports, VBA can handle it all seamlessly.

This article explains in complete detail how to send emails automatically from Excel using VBA, including setup, code examples, error handling, and customization options to make your process efficient and professional.

Why Automate Emails from Excel?

Automating emails from Excel saves time, reduces human errors, and ensures timely communication. It is widely used in business environments for sending daily reports, payment reminders, data summaries, and more.

Advantages of Email Automation in Excel VBA	Description
Time Efficiency	Automates repetitive email-sending tasks instantly.
Error Reduction	Eliminates manual copy-paste and sending errors.
Personalization	Automatically customizes email content for each recipient.
Data Integration	Uses Excel data directly to populate email body and subject lines.
Scheduling Capability	Can send reports automatically at specific times or events.
Professional Communication	Maintains consistency and formatting in email reports.

Prerequisites for Email Automation in Excel VBA

Before you start sending emails automatically, ensure the following requirements are met:

Requirement	Details
Microsoft Excel Installed	Excel 2013 or later versions recommended.
Microsoft Outlook Installed	Outlook must be configured with an active email account.
Macro-Enabled Workbook	Save your Excel file as `.xlsm` to allow VBA macros.
Enable VBA Developer Tab	Go to File → Options → Customize Ribbon → Enable Developer Tab.
Security Settings	Enable “Trust access to the VBA project object model” under Macro Security.

Step-by-Step Guide: How to Send Emails Automatically from Excel VBA

Let’s go through the process of setting up Excel VBA to send emails automatically through Outlook.

Step 1: Prepare Your Excel Data

Your Excel sheet should include columns like these:

Name	Email Address	Subject	Message Body
John Smith	john.smith@domain.com	Monthly Report	Dear John, your monthly report is attached.
Priya Mehta	priya.mehta@domain.com	Payment Reminder	Hello Priya, please find your pending invoice attached.
Rakesh Kumar	rakesh.kumar@domain.com	Performance Update	Hi Rakesh, here is your performance summary.

This structure helps VBA loop through each row and send customized emails.

Step 2: Open the VBA Editor

Press ALT + F11 to open the VBA Editor.
Go to Insert → Module to add a new module.
In the module window, you will write the email automation code.

Step 3: Write the VBA Code

Below is a simple VBA code example to send emails from Excel through Outlook automatically.

Sub SendEmailsFromExcel()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim ws As Worksheet
    Dim i As Integer
    
    'Set your worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    'Create Outlook object
    On Error Resume Next
    Set OutlookApp = GetObject(Class:="Outlook.Application")
    If OutlookApp Is Nothing Then
        Set OutlookApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    'Loop through each row
    For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        
        'Create email
        Set OutlookMail = OutlookApp.CreateItem(0)
        
        'Email details
        With OutlookMail
            .To = ws.Cells(i, 2).Value
            .Subject = ws.Cells(i, 3).Value
            .Body = ws.Cells(i, 4).Value
            .Display 'Change .Display to .Send for automatic sending
        End With
        
        'Clear object
        Set OutlookMail = Nothing
    Next i
    
    'Release Outlook object
    Set OutlookApp = Nothing
    
    MsgBox "Emails have been successfully created!", vbInformation

End Sub

Explanation of Key Lines:

Code Line	Purpose
`OutlookApp = CreateObject("Outlook.Application")`	Launches Microsoft Outlook.
`.To = ws.Cells(i, 2).Value`	Reads email address from column B.
`.Subject = ws.Cells(i, 3).Value`	Reads subject line from column C.
`.Body = ws.Cells(i, 4).Value`	Reads message content from column D.
`.Display`	Displays the email before sending (use `.Send` for auto send).

Step 4: Attach Files Automatically (Optional)

If you want to send emails with attachments, modify the code as follows:

Sub SendEmailsWithAttachment()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim ws As Worksheet
    Dim i As Integer
    Dim attachmentPath As String

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set OutlookApp = CreateObject("Outlook.Application")

    For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        Set OutlookMail = OutlookApp.CreateItem(0)
        
        attachmentPath = ws.Cells(i, 5).Value 'Column 5 for attachment path
        
        With OutlookMail
            .To = ws.Cells(i, 2).Value
            .Subject = ws.Cells(i, 3).Value
            .Body = ws.Cells(i, 4).Value
            
            'Attach file if exists
            If Dir(attachmentPath) <> "" Then
                .Attachments.Add attachmentPath
            End If
            
            .Send 'Automatically sends the email
        End With
    Next i

    MsgBox "All emails with attachments have been sent successfully."

End Sub

Example Data Structure:

Name	Email Address	Subject	Message Body	Attachment Path
Rahul Jain	rahul@domain.com	Invoice	Please find attached invoice.	C:\Reports\Invoice1.pdf
Nisha Rao	nisha@domain.com	Report	Attached is your weekly report.	C:\Reports\WeekReport.xlsx

Step 5: Test Before Sending

Always test your macro before sending actual emails:

Use .Display instead of .Send to preview the email.
Verify recipients, subject lines, and attachments.
Once verified, replace .Display with .Send.

Advanced Tip: Personalized Email Body

To make emails more professional, you can insert variables such as recipient names within the message body:

.Body = "Dear " & ws.Cells(i, 1).Value & "," & vbNewLine & _
        "This is a reminder for your pending task. Kindly complete it before the deadline." & vbNewLine & _
        "Regards," & vbNewLine & "Team MIS"

This line adds the recipient’s name dynamically, making every message personalized.

Step 6: Automate Email Sending on Workbook Open or at Scheduled Times

You can trigger the email automation code automatically when the Excel file opens or at specific intervals using the Workbook_Open event.

Example:

Private Sub Workbook_Open()
    Call SendEmailsFromExcel
End Sub

This will automatically execute the macro when you open the Excel workbook. Ensure macros are enabled for it to work properly.

Error Handling and Troubleshooting

Issue	Possible Reason	Solution
Outlook not opening	Outlook not installed or configured	Ensure Outlook is installed and default profile is active.
Permission denied	Macro security blocking	Enable “Trust access to VBA project” in Excel settings.
Invalid email address	Missing or wrong format	Check email list and validate addresses.
File not attached	Wrong file path	Ensure attachment path exists and file name is correct.
Email not sent	`.Display` used instead of `.Send`	Change to `.Send` for automatic sending.

Practical Applications of Email Automation in Excel

Application	Description
Sales Reports	Send sales performance data daily or weekly automatically.
Payment Reminders	Send pending invoice reminders to clients automatically.
Project Updates	Share progress updates to team members regularly.
Attendance Reports	Send attendance summaries to HR automatically.
Inventory Alerts	Notify teams when stock levels fall below the threshold.

Performance Optimization Tips

Avoid sending large numbers of emails simultaneously; batch them if possible.
Use a single Outlook session instead of re-opening Outlook for every mail.
Limit attachment sizes to prevent email sending delays.
Maintain clean, structured data in Excel to prevent runtime errors.
Add logging to track which emails were successfully sent.

Conclusion

Automating emails using Excel VBA is one of the most effective ways to streamline communication and reporting within an organization. With just a few lines of VBA code, you can send hundreds of personalized emails, attach reports, and manage communication efficiently.

This approach is widely used by MIS professionals, data analysts, accountants, and administrators for sending automated updates without manual intervention. Whether you are managing client reports or sending daily summaries, Excel VBA email automation can save significant time while maintaining professionalism and accuracy.

Disclaimer

This article is for educational purposes only. The VBA scripts provided should be tested with dummy data before use in real business environments. Microsoft Outlook and Excel versions may differ slightly in behavior. Always ensure compliance with your organization’s email and data security policies before automating email processes.

About the author

I’m Himanshu Dhar, a dedicated MIS trainer proudly associated with I Turn Institute. My journey in the realm of data management and automation spans over 14 enriching years. My passion for this field extends to freelancing projects, where I bring real-world practicality into my teaching. My impact in education is marked by the success of over 25,000 students I’ve had the privilege of mentoring, hailing from diverse backgrounds. Beyond the classroom, I’ve had the honor of conducting corporate training sessions and seminars, catering to the unique needs of businesses. Let’s unlock your potential together in the realm of data analysis and automation.