VBA & Macro Problems

How to Split Excel Data by Email Address and Automate Outlook Attachments

Kushani Nimanthika Oct 7, 2026 869 views

Question details

The user needs to split an Excel dataset based on email addresses rather than departments, generate uniquely named monthly workbooks, save them to local and SharePoint folders, and automatically send them via Outlook.

How to Split Excel Data by Email Address and Automate Outlook Attachments

Product: Excel
Device & OS: not provided
Scenario: Automating the distribution of grouped Excel data to specific email recipients and backing up the generated workbooks to a SharePoint directory.
Observed behavior: The current macro incorrectly splits data by department, saves locally without prompting, fails to format the filename properly (e.g., Jan24_a.xlsx), and does not initialize Outlook to send the emails.

Before you start

Before running the macro, ensure you have enabled the Microsoft Outlook Object Library in your VBA editor by navigating to Tools > References, and verify that your SharePoint destination path is synced or accessible.

Solution 1Recommended

Use VBA to Group Data by Email and Automate Outlook

Modify your VBA macro to group records by email address, format the filename using the date and username, and correctly initialize Outlook application objects.

To achieve the desired automation, the VBA script needs to shift its grouping logic from the department column to the email address column. It also requires string manipulation to extract the username for the filename and valid Outlook object instantiation to dispatch the emails.

Add Outlook Reference

Open the VBA editor by pressing Alt + F11, click on Tools > References, and check the box for 'Microsoft Outlook Object Library' to allow Excel to communicate with Outlook.

Change Grouping Key

Update your macro's loop and filtering logic to target the 'Email Address' column instead of the 'Department' column when generating new workbooks.

Format the Filename

Use VBA functions to extract the month/year and the username prefix. For example, use Format(DateValue, "mmmyy") for the date and Split(EmailAddress, "@")(0) to get the text before the @ symbol (e.g., Jan24_a.xlsx).

Save to Local and SharePoint

Use ActiveWorkbook.SaveAs with your designated local Desktop path, and duplicate the SaveAs command using your verified SharePoint URL or synced folder path.

Initialize and Send via Outlook

Create the Outlook objects using Set OutApp = CreateObject("Outlook.Application") and Set OutMail = OutApp.CreateItem(0). Attach the newly saved workbook using OutMail.Attachments.Add and execute OutMail.Send.

Use VBA to Group Data by Email and Automate Outlook

SharePoint Path Formatting: If saving directly to a SharePoint URL fails in VBA, map the SharePoint document library to a local synced OneDrive folder and use the local C:\Users\... path in your code.

Solution 2

Use Power Automate as a No-Code Alternative

Use Microsoft Power Automate to filter Excel tables and trigger Outlook emails without writing VBA macros.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

While complex Outlook and SharePoint macro integrations are specific to the Microsoft ecosystem, WPS Office provides a highly compatible, lightweight, and free alternative for everyday data processing, macro execution, and spreadsheet management.

1. Download the Installer: Visit the official WPS Office website and download the free installation package.
2. Install the Software: Run the downloaded executable file and follow the straightforward on-screen installation prompts.
3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm files to continue working seamlessly.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Built-in VBA and macro support in advanced versions for automating repetitive data tasks.Lightweight application that runs smoothly even on older devices.Familiar tabbed user interface, requiring zero learning curve for Excel users.

Frequently Asked Questions

Why is my VBA macro creating files but not sending emails through Outlook?

If your macro creates files but fails to generate emails, you likely have not initialized the Outlook objects in your code. Ensure you are using CreateObject("Outlook.Application") and have enabled the 'Microsoft Outlook Object Library' in the VBA editor under Tools > References.

How do I extract the username from an email address in a VBA macro?

You can use the built-in VBA Split function. For instance, Split("user@example.com", "@")(0) creates an array using the '@' symbol as a delimiter and extracts the first item, returning 'user'.

Why does ActiveWorkbook.SaveAs fail when targeting a SharePoint folder?

SharePoint paths in VBA must be correctly formatted. Often, standard web URLs (https://...) cause authentication or formatting errors. The most reliable workaround is to sync the SharePoint library to your local machine using OneDrive, then point your VBA macro to the synced local folder path.

Can I automatically format the current date as month and year for my filename?

Yes, you can use the VBA Format function. For example, using Format(Date, "mmmyy") will dynamically convert today's date into the 'Jan24' format required for your filename.

Excel & Data Problems