How to Create and Email Separate Excel Workbooks by City Using VBA
Question details
The user wants to split a master Excel dataset by city into individual workbooks and automatically email each file to a specific recipient using a VBA macro.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the process of generating location-specific reports from a master dataset and distributing them via email.
- Observed behavior
- The goal is to successfully execute a VBA script that loops through a city list, filters data, saves new workbooks, and attaches them to Outlook emails without manual intervention.
Ensure you have a clean list of unique city names and corresponding email addresses in your workbook. Also, verify that Microsoft Outlook is installed and configured on your computer, as the VBA script relies on the Outlook object model to send emails.
Use a VBA Macro to Split Data and Send via Outlook
A complete VBA script will automate the process of filtering the main dataset, creating a new workbook for each city, saving it, and creating an Outlook email with the file attached.
This method requires enabling the Developer tab in Excel and inserting a new module. The macro will loop through your specified city range, apply an AutoFilter, copy the visible cells to a new workbook, and trigger Outlook to send the email.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor.
In the top menu, go to Insert > Module. A blank window will appear where you can paste your VBA script.
Paste your macro code and update the script variables to match your specific setup. Ensure you change the worksheet name, the column letter containing the cities, your designated save folder path, and the column containing recipient email addresses.
In the VBA Editor menu, go to Tools > References. Scroll down and check 'Microsoft Outlook Object Library' to ensure the email integration works correctly.
Press F5 to run the macro. It is highly recommended to use the '.Display' method instead of '.Send' in the Outlook portion of your code during initial testing to review the drafts before they are actually emailed.
Automate Data Processing with WPS Spreadsheet
WPS Office provides robust support for VBA and macros, allowing you to seamlessly run complex scripts to filter data, split workbooks, and automate reporting tasks exactly like Microsoft Excel.
- 1. Open Your Dataset: Download and install WPS Office, then open your master dataset in WPS Spreadsheet.
- 2. Access the VBA Environment: Navigate to the 'Developer' tab on the ribbon and click 'Macro' to open the VBA editor.
- 3. Run the Automation Script: Insert a new module, paste your data-splitting and emailing script, and press F5 to automate your workflow.
Frequently Asked Questions
Why is my VBA macro giving a 'User-defined type not defined' error when trying to email?
This usually happens because the Outlook Object Library is not enabled. Open the VBA Editor, go to Tools > References, and check the box for 'Microsoft Outlook Object Library'.
Can I send the emails using a different client instead of Outlook?
Yes, but the VBA code must be significantly modified. Standard Excel VBA email scripts are optimized for Outlook. Using Gmail or an SMTP server requires utilizing CDO (Collaboration Data Objects) within your VBA script.
How do I stop the screen from flashing while the macro runs?
Add 'Application.ScreenUpdating = False' at the very beginning of your VBA script, and set it back to 'True' at the end. This improves performance and prevents distracting screen flicker.
How can I save the new workbooks without emailing them?
You can modify the code by deleting or commenting out (using an apostrophe) the block of code that creates the Outlook application object and attaches the file. The script will then simply filter and save the workbooks to your designated folder.
