How to Send Email from a Selected Row Using Excel VBA Macros
Question details
The user needs an Excel macro to send an email via Outlook, populating the recipient, subject, and body fields dynamically using data from the currently selected spreadsheet row.
- Product
- Microsoft Excel, Microsoft Outlook
- Device & OS
- not provided
- Scenario
- Automating the process of sending row-specific data via email directly from an active spreadsheet dataset.
- Observed behavior
- Using VBA, the macro will read the active row index, extract values from specific columns (e.g., recipient email, subject text), create an Outlook application object, and execute the send command.
Ensure Microsoft Outlook is installed, configured with an active email account, and set as your default email client. Additionally, confirm that the Developer tab is enabled in your Excel ribbon to access the VBA editor.
Create and Run a VBA Macro to Send Row Data via Outlook
This method uses Visual Basic for Applications (VBA) to capture the active row's data and pass it to an Outlook application instance to send an email automatically.
This script leverages the Outlook Object Model to draft an email directly from Excel. It identifies the row of your currently selected cell and uses predefined column letters to extract the email address, subject, and body text.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
In the VBA editor, click on 'Insert' in the top menu and select 'Module' to create a blank script window.
Copy and paste the following script. This code utilizes the Outlook Application object to draft and send an email: Sub MailRow() Dim lngR As Long Dim OutApp As Object Dim OutMail As Object lngR = ActiveCell.Row Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = Cells(lngR, "I").Value .Subject = Cells(lngR, "D").Value .Body = "You need to look at this: " & Cells(lngR, "C").Value & " " & Cells(lngR, "H").Value & Cells(lngR, "A").Value & Chr(10) & "Regards, Me" .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub
Modify the column letters in the code (like 'I' for the To field, and 'D' for the Subject) to perfectly match the columns in your actual worksheet where the data is stored.
Close the VBA editor. Select any cell in the row you wish to email, press Alt + F8 to open the Macro dialog, select the 'MailRow' macro, and click 'Run'.
Run Your Excel VBA Macros Seamlessly in WPS Spreadsheet
WPS Office offers robust support for VBA macros, allowing you to execute your automated email scripts and other Excel-compatible modules without rewriting your code. Experience a lightweight, highly compatible alternative for your daily data tasks.
- 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm or .xls file containing the Outlook email macro.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to view all macro and VBA editor tools.
- 3. Execute Your Script: Click 'Macros', select your 'MailRow' script from the dialog list, and hit 'Run' to send the email directly via Outlook.
Frequently Asked Questions
How can I preview the email before it sends automatically?
In your VBA code, simply change the `.Send` command to `.Display`. This will open the Outlook email draft on your screen instead of sending it immediately, allowing you to review the contents and click send manually.
Why do I receive an 'ActiveX component can't create object' error?
This error typically occurs if Microsoft Outlook is not installed on your local machine, or if the VBA environment is unable to connect to the Outlook application. Ensure the desktop version of Outlook is open and configured correctly before running the macro.
Can I attach a file to the email using this macro?
Yes, you can easily add an attachment by inserting a new line inside the `With OutMail` block. Add `.Attachments.Add "C:\path\to\your\file.pdf"` right above the `.Send` or `.Display` command.
How do I send the email to multiple recipients from the same row?
If your target cell (e.g., Column I in the example code) contains multiple email addresses separated by semicolons (e.g., user1@test.com; user2@test.com), the Outlook application object will automatically recognize and parse them as multiple recipients.
