VBA & Macro Problems

How to Generate Outlook Emails for Matching Excel Batch Rows Using VBA

Maira Mehtab Sep 21, 2026 869 views

Question details

The user wants to use Excel VBA to prompt for a batch number and automatically generate Outlook emails with attachments exclusively for rows that contain the matching batch value.

Product: Excel, Outlook
Device & OS: not provided
Scenario: Automating bulk email generation from a spreadsheet dataset where emails should only be sent for specific grouped rows (e.g., 'batch 1') at a time.
Observed behavior: The user struggles to write the correct VBA If statement to filter the rows dynamically and currently has to manually edit the macro code for different batches like batch 2 or batch 3.

Before you start

Ensure you have enabled the Developer tab in your Excel ribbon and added the Microsoft Outlook Object Library reference in the VBA editor to allow Excel to interact with Outlook.

Solution 1Recommended

Filter and Generate Emails using Application.InputBox

Use Application.InputBox to prompt the user for the desired batch number when the macro runs, then use an If statement inside a For loop to process only the matching rows.

Instead of hardcoding the batch number into your VBA script, you can use Application.InputBox. This creates a popup dialog asking the user to type the batch they want to process.

By setting up a For loop that iterates through your data rows, you can use an If statement to compare the value in your batch column (e.g., Column G) against the user's input. The Outlook email generation code will only trigger when a match is found.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Enable the Outlook Object Library

In the top menu, click 'Tools', then select 'References'. Scroll down and check the box for 'Microsoft Outlook XX.X Object Library', then click 'OK'.

Create the InputBox Prompt

Declare a string variable for your input. Add the code: batchNum = Application.InputBox("Enter the batch number to process:", "Batch Selection", Type:=2). The Type:=2 argument ensures the input is treated as text.

Set Up the Loop and If Statement

Create a For loop to iterate through your rows (e.g., For i = 2 To lastRow). Inside the loop, add an If statement to check the relevant column: If Cells(i, "G").Value = batchNum Then.

Generate the Email

Inside the If statement block, initialize your Outlook MailItem, set the .To, .Subject, and .Body properties using the row data, add attachments with .Attachments.Add, and use .Display or .Send to finalize.

Adjust Column References: Be sure to change the column letter in Cells(i, "G") to match the exact column where your batch numbers are stored in your workbook layout.

WPS Spreadsheet Automation

Automate Batch Emails Efficiently with WPS Spreadsheet

WPS Office provides robust VBA and Macro support natively, allowing you to seamlessly run your Excel macros to filter data and send targeted batch emails without compatibility issues.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook containing the email dataset.
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to access your macro environment.
3. Insert the Macro Code: Paste your Application.InputBox and Outlook automation script into a new module, adjusting the column references as necessary.
4. Run and Input Batch: Click the Run button. When prompted by the input box, type your batch identifier (e.g., 'batch 1') to generate the emails.

Fully compatible with Microsoft Excel macro formats (.xlsm, .xlsb).Built-in VBA editor for writing, debugging, and executing macros.Lightweight architecture ensures fast performance when processing large datasets.Free to use for everyday spreadsheet automation and data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'User-defined type not defined' error in my VBA macro?

This error typically occurs when the Microsoft Outlook Object Library is not enabled. Go to Tools > References in the VBA editor and ensure the checkbox for the Outlook Object Library is checked.

What does Type:=2 mean in Application.InputBox?

The Type argument specifies the data type returned by the input box. Type:=2 forces the input to be evaluated and returned as a text string, which is ideal for alphanumeric batch numbers like 'batch 1'.

How can I send the emails automatically instead of just viewing them?

In your Outlook MailItem code block, replace the .Display command with .Send. This will automatically dispatch the emails without opening them on your screen for manual review.

How do I prevent the macro from failing if the user cancels the input box?

You can add an If statement right after the InputBox line to check if the result is 'False' or an empty string. If it is, use 'Exit Sub' to stop the macro gracefully without causing errors.

Excel & Data Problems