📤 Send Emails Directly from Excel Using VBA — No Outlook Pop-Up Needed!

Managing personalized emails for clients, team members, or vendors can be time-consuming—especially when done manually. That’s why I created a powerful Excel VBA project that allows you to send emails directly from Excel, in a loop, with a predefined message body—without even opening Outlook!

🚀 What This Project Does

• ✅ Send emails directly from Excel using Outlook in the background
• ✅ Loop through a list of recipients
• ✅ Use a predefined email body (HTML or plain text)
• ✅ Automatically fill in subject lines and recipient names
• ✅ Avoid opening or displaying the Outlook window

🧠 How It Works

The core of the project is a VBA macro that reads data from your Excel sheet and sends emails using Outlook’s backend.

Option Explicit

Sub Auto_Email()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim ws As Worksheet
Dim ws_1 As Worksheet
Dim emailAddress As String
Dim subject As String
Dim body As String
Dim i As Long
Dim j As Long
Dim logo As String
Dim sin As String
Dim x As Long
Dim R_name As String
Dim final As String
Dim S_Name As String
Dim s_mail As String
Dim file_pth As String


'Set your worksheet
Set ws = ThisWorkbook.Sheets("Email Add") ' Change "Email Add" to your sheet name
Set ws_1 = ThisWorkbook.Sheets("Message") ' Change "Message" to your sheet name logo = ThisWorkbook.Path & "\" & "Wood logo.jpg"
x = ws_1.Cells(Rows.Count, 2).End(xlUp).Row
S_Name = ws.Cells(2, 8).Value
s_mail = ws.Cells(3, 8).Value
file_pth = ws.Cells(2, 11).Value
sin = "

Best regards,

" & S_Name & "
"
'Loop through each row in the worksheet
For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assuming email addresses start from row 2
ws.Cells(i, 5).ClearContents
If ws.Cells(i, 4).Value = "Y" Then
emailAddress = ws.Cells(i, 3).Value ' Assuming email addresses are in column A
subject = ws.Cells(2, 6).Value ' Assuming subject is in cell F2
R_name = ws.Cells(i, 2).Value
body = "Hi " & R_name & "

" 'ws_1.Cells(2, 2).Value ""

For j = 2 To x
body = body & ws_1.Cells(j, 2).Value & "
"
Next j
'img adding
body = body
'sin adding
body = body & sin & "
"
'Create Outlook application and mail item
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
'Set email properties
With OutlookMail
.To = emailAddress
.subject = subject
.htmlbody = body

.display

End With

'Release objects
Set OutlookMail = Nothing
Set OutlookApp = Nothing
ws.Cells(i, 5).Value = "Email Sent"
ws.Cells(i, 5).Interior.ColorIndex = 8

Else
ws.Cells(i, 5).Value = "Email Not sent "
ws.Cells(i, 5).Interior.ColorIndex = 3
End If



Next i


End Sub

📋 What You Need in Your Excel Sheet

S.NO	Name	Email Address	Active	STATUS	Subject	BLANK	Sender
1	John	john@example.com	Y or No	Not Required	Not Reuqired	Dummy Mail	sender Name & email address in next row
2	Chanchal	Chanchal@example.com	Y or No	Not Required	Not Reuqired	Dummy Mail	sender Name & email address in next row

✨ Key Features

• 🔁 Loop through unlimited recipients
• 📨 Send emails silently (no Outlook pop-up)
• 🧾 Predefined message body (customizable)
• 📅 Schedule or trigger emails with a button
• 🔒 Keeps your data and workflow inside Excel

🛠️ Use Cases

• Sending invoices or payment reminders
• Notifying team members of task updates
• Sharing reports or dashboards
• Sending birthday or anniversary greetings

🎨 Want to Customize this Template?

This project is fully customizable. You can:

• Add HTML formatting to the email body
• Attach files dynamically
• Include CC/BCC recipients
• Pull data from multiple sheets or sources

📞 Need a Custom Email Automation Tool?

If you'd like to customize this template for your business—whether it's branding, attachments, or advanced logic—feel free to connect with us. We’d love to help you streamline your workflow and save time.

👉 Contact us today to get started with your personalized Excel email automation solution!