VBA & Macro Problems

How to Send One Email per Supplier from Excel Using VBA

Bushra ParveenBushra Parveen Nov 3, 2026 869 views

Question details

The user needs to create an Excel VBA macro that groups data rows by supplier and automatically sends customized, multi-paragraph Outlook emails for each.

How to Send One Email per Supplier from Excel Using VBA
Product
Excel
Device & OS
not provided
Scenario
Automating bulk emails to suppliers from an Excel dataset using Outlook, ensuring each supplier receives exactly one consolidated email.
Observed behavior
The goal is to properly format the email body with text templates, placeholders, line breaks, and attachments using the Outlook Body property via VBA.
Before you start

Ensure you have the Microsoft Outlook Object Library enabled in your Excel VBA Editor (Tools > References) and sort your dataset by the supplier name column to simplify the VBA grouping logic.

Solution 1Recommended

Configure the VBA Email Body Formatting with Line Breaks

Use string concatenation and the vbCrLf function to structure professional, multi-paragraph emails directly within your VBA macro.

When automating emails to suppliers, generating a readable email body is crucial. Instead of sending one continuous block of text, you can structure your message into paragraphs.

By assigning a properly concatenated text string to the Outlook Object's Body property, you can use the VBA constant vbCrLf (Carriage Return Line Feed) to insert line breaks where needed.

1
Open the VBA Editor

Press ALT + F11 in your Excel workbook to open the Visual Basic for Applications (VBA) editor.

2
Construct the Email Body String

Within your supplier grouping loop, define the email text string. Assign your text to the Body property and use vbCrLf to simulate pressing the Enter key. For example: OutMail.Body = "Hello," & vbCrLf & vbCrLf & "This is the first paragraph." & vbCrLf & "This is the second paragraph."

3
Insert Dynamic Placeholders

Replace static names with your loop variables (e.g., cell values containing the supplier's name) using the ampersand (&) operator to concatenate variables with the text string.

Configure the VBA Email Body Formatting with Line Breaks
HTML Formatted Emails: If you need advanced formatting like bold text or font colors, assign your string to OutMail.HTMLBody instead of OutMail.Body. If using HTMLBody, you must use the HTML tag <br> for line breaks instead of vbCrLf.
Free Microsoft Office alternative

Try WPS Office for Your Data Management Needs

While Outlook VBA automation relies heavily on Microsoft's COM ecosystem, you can manage, sort, and group your supplier data seamlessly using WPS Spreadsheet. WPS Office is a free, lightweight suite that offers robust data analysis tools and high format compatibility.

  1. 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet and seamlessly open your existing .xlsm or .xlsx supplier data files.
  3. 3. Analyze and Group Data: Use powerful built-in data grouping and PivotTable tools to organize your supplier records efficiently.
Highly compatible with Microsoft Excel formats including .xlsx and .xlsm files.Built-in advanced data sorting, filtering, and PivotTable features to easily group supplier data.Free to download and lightweight on system resources.Familiar tabbed user interface ensures a zero learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

How do I add an attachment to the automated VBA email?

Within your VBA macro, use the OutMail.Attachments.Add method followed by the file path enclosed in quotes (e.g., OutMail.Attachments.Add "C:\Documents\Invoice.pdf") right before the .Send or .Display command.

Why are my VBA line breaks not working in the Outlook email?

If you are using the .HTMLBody property to format your email instead of .Body, Outlook will process the text as HTML and ignore standard VBA line breaks (vbCrLf). You must replace vbCrLf with the HTML line break tag <br>.

Can I preview the emails before the VBA macro sends them?

Yes. Instead of using the OutMail.Send method inside your loop, replace it with OutMail.Display. This will open each generated email draft on your screen, allowing you to manually review the formatting and placeholders before clicking send.