How to Send One Email per Supplier from Excel Using VBA
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.

- 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.
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.
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.
Press ALT + F11 in your Excel workbook to open the Visual Basic for Applications (VBA) editor.
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."
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.

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. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Excel File: Launch WPS Spreadsheet and seamlessly open your existing .xlsm or .xlsx supplier data files.
- 3. Analyze and Group Data: Use powerful built-in data grouping and PivotTable tools to organize your supplier records efficiently.

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.




