VBA & Macro Problems

Use VBA to Print and Email an Excel Workbook with One Button in WPS Office

Steve K Nov 13, 2026 869 views

Question details

The user wants to create a single VBA macro that prints the active Excel workbook and emails it to a fixed recipient without opening the Outlook address-entry dialog.

Use VBA to Print and Email an Excel Workbook with One Button

Product: WPS Spreadsheets and Microsoft Outlook
Device & OS: not provided
Scenario: The user is automating a repetitive workflow: click one button to print the current workbook and send it by email to a predetermined address.
Observed behavior: The current code uses Application.Dialogs(xlDialogSendMail).Show, which opens the interactive Outlook address-entry dialog and requires manual recipient selection.

Before you start

Make sure the workbook is saved in a macro-enabled format such as .xlsm and that macros are enabled. Confirm the recipient email address, email subject, and the exact printer name are correct before adding them to the VBA code.

Solution 1Recommended

Replace the Interactive SendMail Dialog with Direct PrintOut and SendMail Code

Use this method to automate the entire print-and-email process from one macro button without the recipient dialog.

The interactive xlDialogSendMail dialog asks you to choose a recipient every time. Replacing it with the SendMail method passes the recipient and subject directly, while ActiveWorkbook.PrintOut sends the workbook to the selected printer first.

Depending on your Outlook security policy and antivirus settings, Outlook may still show a security prompt. The macro cannot always suppress that prompt because it is controlled by Outlook, not WPS Office.

Open the VBA editor

In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, then click Insert > Module to create a new module.

Paste the print and email macro

Copy and paste the following code into the module: Sub PrintAndEmailWorkbook() ActiveWorkbook.PrintOut Copies:=1, Collate:=True, ActivePrinter:="Your Printer Name" ActiveWorkbook.SendMail Recipients:="recipient@example.com", Subject:="Here is the file" End Sub

Customize printer and recipient

Replace Your Printer Name with the exact printer name shown in your system and replace recipient@example.com with the fixed email address. Adjust Subject if needed.

Run the macro

Close the editor and press Alt+F8, select PrintAndEmailWorkbook, and click Run. You can also assign the macro to a button from the Developer tab for one-click use.

Replace the Interactive SendMail Dialog with Direct PrintOut and SendMail Code

Outlook security prompt: If Outlook still displays a security prompt, contact your email administrator. WPS Office cannot suppress Outlook programmatic access warnings from the macro side.

Print and Email a Workbook with One VBA Button in WPS Office

Open your macro-enabled workbook in WPS Office Spreadsheets, add the same VBA logic, and run it from a single button to print the workbook and send it through Outlook. WPS Office keeps the familiar VBA and Excel format workflow while remaining free and lightweight.

1. Download and install WPS Office: Download WPS Office from the official website, install it, and open the macro-enabled workbook in WPS Office Spreadsheets.
2. Open the VBA editor: Press Alt+F11 in WPS Spreadsheets, insert a new module, and paste the PrintOut and SendMail macro code.
3. Set the printer and recipient: Replace the ActivePrinter name and recipient email address with your fixed values, then close the editor.
4. Assign the macro to a button: Go to the Developer tab, insert a button, assign the macro to it, and click the button to print and email the workbook.

One-click VBA automation for printing and sending the active workbookSupports Excel .xlsm and .xls workbook formatsFree, lightweight Office suite with a familiar ribbon interfaceSeamless migration from Microsoft Office without reinstalling OutlookCompatible with VBA macros and Microsoft Office files

microsoft office alternative - wps office

Frequently Asked Questions

Does WPS Office support ActiveWorkbook.SendMail?

Yes, WPS Office Spreadsheets supports VBA and the SendMail method in many Windows setups that use a configured MAPI email client such as Microsoft Outlook. If the method is unavailable, check that your Outlook profile is configured and that the workbook is macro-enabled.

How do I prevent the Outlook address dialog from appearing?

Replace Application.Dialogs(xlDialogSendMail).Show with ActiveWorkbook.SendMail Recipients:="fixed@example.com", Subject:="..." so the recipient is supplied directly instead of through the dialog.

Can I choose a specific printer before sending?

Yes. Use ActiveWorkbook.PrintOut with the ActivePrinter parameter set to the exact printer name, or set Application.ActivePrinter before calling PrintOut in the same macro.

Why does Outlook still show a security prompt?

Outlook’s programmatic access security is controlled by Outlook and your email administrator. The VBA macro cannot always remove this prompt; you may need to adjust Outlook security policy, use a trusted add-in, or ask your administrator.

Excel & Data Problems