How to send each sheet to different email addresses from Excel?
If you have an Excel workbook with multiple worksheets, each containing an email address in cell S1, you may want to send each sheet as a separate attachment to its respective recipient. This task can be tedious if done manually, especially when dealing with a large number of sheets. In this tutorial, we’ll show you how to use VBA code to automatically send each worksheet from your Excel file as an attachment to the email address specified in cell S1 of each sheet.
Send each sheet to different email addresses from Excel with VBA code
The following VBA code will allow you to send each worksheet as an attachment to the corresponding recipient listed in cell S1. Please follow these steps:
1. Press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. Then, click Insert > Module, and copy and paste the below VBA code into the window.
VBA code: Send each sheet as attachment to different email addresses
Sub Mail_Every_Worksheet()
'Updateby ExtendOffice
Dim xWs As Worksheet
Dim xWb As Workbook
Dim xFileExt As String
Dim xFileFormatNum As Long
Dim xTempFilePath As String
Dim xFileName As String
Dim xOlApp As Object
Dim xMailObj As Object
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
xTempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
xFileExt = ".xls": xFileFormatNum = -4143
Else
xFileExt = ".xlsm": xFileFormatNum = 52
End If
Set xOlApp = CreateObject("Outlook.Application")
For Each xWs In ThisWorkbook.Worksheets
If xWs.Range("S1").Value Like "?*@?*.?*" Then
xWs.Copy
Set xWb = ActiveWorkbook
xFileName = xWs.Name & " of " _
& VBA.Left(ThisWorkbook.Name, VBA.InStr(ThisWorkbook.Name, ".") - 1) & " "
Set xMailObj = xOlApp.CreateItem(0)
xWb.Sheets.Item(1).Range("S1").Value = ""
With xWb
.SaveAs xTempFilePath & xFileName & xFileExt, FileFormat:=xFileFormatNum
With xMailObj
'specify the CC, BCC, Subject, Body below
.To = xWs.Range("S1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add xWb.FullName
.Display
End With
.Close SaveChanges:=False
End With
Set xMailObj = Nothing
Kill xTempFilePath & xFileName & xFileExt
End If
Next
Set xOlApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
- S1 is the cell that contains the email address you want to send the email to. If your email addresses are in a different cell, such as A1, you can modify the code to reflect that change.
- You can specify the CC, BCC, Subject, Body to your own in the code;
- To send the email directly without opening the following new message window, you need to change .Display to .Send.
3. Then, press F5 key to run this code, and each sheet is inserted into the new message window as an attachment automatically, see screenshot:
4. Finally, click Send button to send each email one by one.
Kutools for Excel: Easily Send Personalized Emails in One Click!

Tired of sending customer emails one by one? With Kutools for Excel’s “Send Emails” feature, communication becomes faster and more professional! Simply prepare an Excel sheet with names, email addresses, registration codes, and insert placeholders—the system will automatically generate personalized emails and send hundreds with just one click. No more repetitive work!
- 💡 Dynamic placeholders (e.g., name, registration code) auto-fill personalized content for each recipient, ensuring every email feels uniquely tailored.
- 📎 Attach personalized files for accurate delivery
- 📤 Seamlessly integrates with Outlook for secure and reliable sending
- 📝 Save and reuse email templates for maximum efficiency
- 🎨 What-you-see-is-what-you-get editor that’s easy to use
- 🖋 Uses your Outlook signature—no extra setup, just hit send!
- Get Kutools For Excel Now!
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!