How to send an email to multiple recipients in a list from Excel via Outlook?
In many workplace scenarios, you may encounter situations where you have a list of email addresses stored in a column of an Excel worksheet, such as when distributing newsletters, sending invoices, or notifying participants of an event. Instead of manually entering each address or opening Outlook to create the email, Excel can streamline this process by sending messages to all recipients directly. This guide provides detailed solutions for sending emails to multiple recipients from Excel at once, integrating with Outlook to save time and reduce errors.
Send an email to multiple recipients from Excel with VBA code
Send an email to multiple recipients with the current workbook as an attachment by using VBA code
Alternative solution: Send email addresses separated by semicolons using an Excel formula
Send an email to multiple recipients from Excel with VBA code
If you want to send the same email to a group of people at once and have their addresses listed in a worksheet column, you can use VBA code to automate this task. This method is especially helpful for batch notifications or group updates without needing to individually add recipients in Outlook. Please follow these steps:
1. Press ALT + F11 together to open the Microsoft Visual Basic for Applications window. If you haven't enabled the Developer tab, you can find instructions in Excel's settings.
2. In the VBA window, click Insert > Module. This opens a blank Module window for you to input your code. Paste the following code inside:
VBA code: Send email to multiple recipients
Sub sendmultiple()
'updateby Extendoffice
Dim xOTApp As Object
Dim xMItem As Object
Dim xCell As Range
Dim xRg As Range
Dim xEmailAddr As String
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the addresses list:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xOTApp = CreateObject("Outlook.Application")
For Each xCell In xRg
If xCell.Value Like "*@*" Then
If xEmailAddr = "" Then
xEmailAddr = xCell.Value
Else
xEmailAddr = xEmailAddr & ";" & xCell.Value
End If
End If
Next
Set xMItem = xOTApp.CreateItem(0)
With xMItem
.To = xEmailAddr
.Display
End With
End Sub
3. Press F5 (or click Run) to execute the code. You will see a prompt box that asks you to select the list of addresses from your worksheet. This step lets you easily select all the cells containing the needed email addresses, ensuring the code only includes recipients that match an email format (*@*). See screenshot:
4. After selecting the addresses, click OK. Outlook will open a new Message window with the chosen addresses automatically filled into the To field. You can now specify the subject and write your message according to your needs. The window allows further editing if needed, for personalization or clarification. See screenshot:
5. Once your message is complete, click the Send button to send the email to all listed recipients simultaneously. This approach ensures you do not miss any addresses and speeds up group communication.
Tip: Make sure you have Outlook installed and set as your default email client for this method to work properly. If you have issues such as the prompt not appearing, double-check your macro security settings in Excel.
Applicable scenarios: Use this method for team announcements, event notifications, or sending identical information to several people. The advantage is speed and accuracy with minimal manual entry, while the limitation is the lack of message personalization for individual recipients.
Troubleshooting: If the Outlook message window does not open, check that Outlook is running normally and is the default mail handler. Also, ensure that all selected cells contain valid email addresses—error handling is included, but empty or incorrectly formatted cells should be avoided for best results.
Send personalized emails to multiple recipients with different attachments: For more advanced requirements, such as sending unique attachments, customized content, or adding CC/Bcc automatically, the Kutools for ExcelSend Emails feature makes it quick and easy to deliver personalized emails from Excel via Outlook. This function enables you to tailor emails for each recipient, add separate attachments, and include CC/Bcc recipients based on your needs for more flexible mass mailing. Click to download Kutools for Excel!
|
Send an email to multiple recipients with the current workbook as an attachment by using VBA code
If you need to send your current Excel workbook to a group of recipients as an attachment, the following VBA solution can help automate this process. This method is ideal for sharing reports, financial summaries, or collaborative documents with multiple stakeholders.
1. Open Excel and press ALT + F11 to launch the Microsoft Visual Basic for Applications environment.
2. In the VBA editor, navigate to Insert > Module, then copy and paste the code below into the module window:
VBA code: Send email to multiple recipients with the current workbook as an attachment
Sub EmailAttachmentRecipients()
'updateby Extendoffice
Dim xOutlook As Object
Dim xMailItem As Object
Dim xRg As Range
Dim xCell As Range
Dim xEmailAddr As String
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the arresses list:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xOutlook = CreateObject("Outlook.Application")
Set xMailItem = xOutlook.CreateItem(0)
For Each xCell In xRg
If xCell.Value Like "*@*" Then
If xEmailAddr = "" Then
xEmailAddr = xCell.Value
Else
xEmailAddr = xEmailAddr & ";" & xCell.Value
End If
End If
Next
With xMailItem
.To = xEmailAddr
.CC = ""
.Subject = ""
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set xOutlook = Nothing
Set xMailItem = Nothing
End Sub
3. Press F5 to run the code. A prompt will appear for you to select the email address cells. This mechanism helps you easily include all relevant recipients from your worksheet and ensures that only properly formatted email addresses are added. See the following screenshot:
4. After confirming the selection, click OK. Outlook will automatically create a new Message window, listing the selected recipients in the To field and adding the currently open workbook as an attachment. You can then personalize the message content and subject line before sending. See screenshot:
5. Click the Send button to send your email—with the workbook attached—to all selected recipients in one go.
Precaution: If your workbook contains sensitive information, verify your recipient list carefully before sending. If you encounter an error in attaching the file, ensure the workbook is saved and not open in another program, and that Outlook is correctly set up for sending emails via automation.
Use cases: This solution is widely utilized for distributing periodic reports, submitting collaborative workbooks, or sharing reference files with teams or clients. It is efficient for bulk document delivery, but does not allow separate attachments per recipient.
Common issues: If the prompt window does not open, or the email doesn't send, ensure you have granted macro permissions and that your workbook is saved before running the code. Double-check for empty cells in your selection, as the code skips content not matching standard email formats.
Alternative solution: Send email addresses separated by semicolons using an Excel formula
If you want to quickly compile a list of email addresses into a single cell—separated by semicolons for easy copying into Outlook’s To, CC, or Bcc fields—Excel formulas can provide a fast and simple solution, especially if you do not wish to use macros.
1. Suppose your email addresses are listed in column A, rows 1 through 7. Enter the following formula in a blank cell (for example, B1):
=TEXTJOIN(";",TRUE,A1:A7)2. Press Enter. The result will be a single text string containing all email addresses from the selected range, separated by semicolons. You can then copy this cell and paste it directly into Outlook's recipient fields.
Formula parameters:
- TEXTJOIN: Concatenates a range of cells using the specified delimiter—in this case, a semicolon.
- TRUE: Excludes any blank cells in the range. All valid email addresses will be concatenated without any gaps.
- A1:A10: Adjust this range to fit your list of email addresses.
Advantages: This approach does not require enabling macros, making it suitable for environments where VBA is restricted. It is useful for quickly copying a compiled list into any email platform, not just Outlook.
Limitations: Unlike VBA, it does not auto-create an email or automate the sending process, but improves efficiency when dealing with recipient lists.
Tip: If your list changes, update the formula range accordingly. For formats other than semicolon delimiters, replace ";" with your preferred character.
Related article:
How to send personalized mass emails to a list from Excel via Outlook?
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in