VBA & Macro Problems

How to Send Excel Row or Column Data by Email Using VBA

Chanuka Geekiyanage Oct 13, 2026 868 views

Question details

The user needs an Excel VBA macro to extract data from specific columns or rows and automatically populate an Outlook email body, with the option to send individual emails per row.

How to Send Excel Row or Column Data by Email Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating email distribution where specific worksheet data, such as orders or inventory lists, needs to be sent to designated recipients via Outlook.
Observed behavior: Manual extraction and emailing of row and column data is tedious. A VBA script is required to concatenate cell values properly and integrate directly with the Outlook application.

Before you start

Ensure you have Microsoft Outlook installed and configured on your computer, and verify that your Excel Trust Center settings allow macros to run.

Solution 1Recommended

Send Data from a Specific Column in a Single Email

Use this macro to gather all values from a designated column and compile them into a single Outlook email message.

This solution is ideal when you need to send a compiled list (such as a list of product orders) to a single recipient. The macro loops through the specified column, concatenates the data using the vbCrLf function to create line breaks, and assigns the final string to the email body.

Open the VBA Editor

Open your Excel workbook and press the ALT + F11 keys on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on 'Insert' and select 'Module'. This will create a blank script window where you can paste your code.

Paste the Concatenation Code

Copy and paste the following code: Sub SendColumnData() Dim lastRow As Long, i As Long Dim body As String Dim outlookApp As Object, mail As Object lastRow = Cells(Rows.Count, "C").End(xlUp).Row For i = 2 To lastRow If Len(body) > 0 Then body = body & vbCrLf body = body & Cells(i, "C").Value Next i Set outlookApp = CreateObject("Outlook.Application") Set mail = outlookApp.CreateItem(0) With mail .To = "recipient@example.com" .Subject = "Data from Column C" .Body = body .Display End With End Sub

Run the Macro

Press the F5 key or click the green 'Run' button in the toolbar. The Outlook application will automatically generate and display a draft email containing your column data.

Send Data from a Specific Column in a Single Email

Testing with .Display: Always leave the code as '.Display' while testing. Change it to '.Send' only after verifying the recipients and message content to avoid sending emails prematurely.

Solution 2

Send Individual Emails for Each Row of Data

Use this approach when you need to send a distinct email for every row, dynamically pulling recipient addresses and subjects from the worksheet cells.

Advanced Macro Support

Execute VBA Macros and Automate Emails with WPS Spreadsheet

WPS Office fully supports advanced VBA macros, allowing you to run your Excel scripts seamlessly. You can automate email sending, manipulate large datasets, and integrate with COM objects directly within WPS Spreadsheet.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx workbook that contains your data.
2. Access the Developer Tab: Click on the 'Developer' tab in the top ribbon and select 'Visual Basic' or 'Macro' to open the code editor.
3. Paste the Automation Script: Insert a new module and paste your email automation VBA code using the standard Outlook COM object syntax.
4. Run and Automate: Run the script directly from the editor to automatically generate and send your emails without needing to switch back and forth.

High compatibility with Microsoft Excel VBA macros and scripting.100% compatible with standard .xls, .xlsx, and .xlsm file formats.Built-in developer tools for writing, editing, and debugging macros.Lightweight architecture for faster execution when looping through large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA code prompt an Outlook security warning?

Outlook includes built-in security features that warn you when an external application attempts to send emails on your behalf. You can bypass this by adjusting Outlook's Programmatic Access settings in the Trust Center, or by using a trusted COM add-in environment.

How can I change the email body from plain text to HTML in VBA?

Instead of using the `.Body` property in your Outlook MailItem, use the `.HTMLBody` property. When building your string, format it using standard HTML tags (like `<br>` for line breaks and `<b>` for bold text) instead of `vbCrLf`.

Can I attach a file to the email using this VBA macro?

Yes. You can add an attachment by including the `.Attachments.Add "C:\path\to\your\file.pdf"` line within the `With mail` block, placing it just before the `.Display` or `.Send` command.

What happens if I use .Send instead of .Display in the macro?

The `.Send` method commands Outlook to dispatch the email immediately without showing the draft window on your screen. It is highly recommended to use `.Display` during testing to ensure the generated content and recipients are correct before switching to full automation.

Excel & Data Problems