How to Automatically Add an Email Recipient in an Excel Macro
Question details
The user wants to automatically prepopulate the email recipient address when an Excel macro opens the email dialog with the current workbook attached.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating email workflows directly from a spreadsheet using VBA macros.
- Observed behavior
- Needs to trigger the default email application dialog with a specific recipient address and subject line already filled in.
Ensure that you have an active email client (such as Outlook) configured as the default mail app on your system, and that your workbook is saved as a macro-enabled file (.xlsm).
Using the xlDialogSendMail Method in VBA
The most straightforward way to attach the current workbook and specify a recipient is by using the built-in SendMail dialog in VBA.
By calling the xlDialogSendMail dialog, you can pass arguments directly. The first argument sets the recipient's email address, and the optional second argument allows you to set the email subject line.
Press Alt + F11 in your open workbook to launch the Visual Basic for Applications (VBA) editor.
Click 'Insert' from the top menu bar and select 'Module' to create a new blank script window.
Enter the code: Application.Dialogs(xlDialogSendMail).Show "recipient@example.com", "Your Subject Here" inside your Sub routine.
Close the VBA editor and press Alt + F8 to select and run your new macro. Your default email app will open with the recipient and subject already filled out.

Automate Workflows and Macros with WPS Spreadsheet
WPS Spreadsheet provides excellent built-in support for VBA and macros, allowing you to automate repetitive tasks like sending emails with attachments natively. It is highly compatible with Microsoft Excel's VBA syntax.
- 1. Download and Install WPS Office: Download the free WPS Office suite from the official website and follow the installation prompts.
- 2. Enable Developer Tools: Open WPS Spreadsheet, navigate to the 'Developer' tab, and ensure Macros are enabled for your document.
- 3. Run your VBA Code: Click 'Visual Basic', insert your email macro code, and run it exactly as you would in Microsoft Excel.

Frequently Asked Questions
Can I add email body text using xlDialogSendMail?
No, the xlDialogSendMail method only supports adding the recipient and the subject line. If you need to include custom body text, you will need to use a different method, such as automating the Outlook application directly using CreateObject("Outlook.Application").
Why is my macro returning an error when trying to open the email dialog?
This error typically occurs if there is no default email client set on your operating system. Ensure that an email application like Outlook or Windows Mail is installed and configured as the default mail handler in your system settings.
How do I send the email automatically without showing the dialog box?
Instead of using Application.Dialogs(xlDialogSendMail).Show, you can use the command: ActiveWorkbook.SendMail "recipient@example.com", "Subject". This will send the email immediately without displaying the email client window, provided your system's security settings permit automated sending.




