How to use Worksheet.MailEnvelope in the xlwings API way

The MailEnvelope property of a Worksheet object in Excel VBA is used to control email-related features when sending a worksheet via email. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. The MailEnvelope object allows you to customize the email subject, recipients, and message body when using Excel’s built-in email integration, typically via the SendMail method. It is particularly useful for automating email reports directly from an Excel workbook.

Syntax and Parameters
In xlwings, you access the MailEnvelope property via the api property of a Worksheet object. The basic syntax is:

worksheet.api.MailEnvelope

This returns a MailEnvelope object, which has several key properties and methods. The most commonly used include:

• Subject: Sets or gets the email subject line as a string.
• To: Sets or gets the primary recipients as a string (multiple addresses can be separated by semicolons).
• CC: Sets or gets the carbon copy recipients as a string.
• BCC: Sets or gets the blind carbon copy recipients as a string.
• Introduction: Sets or gets the introductory text in the email body as a string. This text appears above the worksheet in the email.
• Item.Send(): Sends the email. Note that this method may require an email client (like Outlook) to be configured and running.

These properties are straightforward to set by assigning string values. For example, worksheet.api.MailEnvelope.Subject = "Monthly Report" sets the subject. The Introduction property is especially useful for adding descriptive text.

Code Example
Below is a practical xlwings example that sets up and sends a worksheet via email. This assumes you have an active workbook and an email client set up.

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
# Access the first worksheet
ws = wb.sheets[0]

# Access the MailEnvelope property via api
envelope = ws.api.MailEnvelope

# Set email properties
envelope.Subject = "Q4 Sales Data"
envelope.To = "manager@example.com; team@example.com"
envelope.CC = "supervisor@example.com"
envelope.Introduction = "Please find the attached Q4 sales report. Key highlights include a 15% increase in revenue."

# Optional: Save or update the workbook before sending
wb.save()

# Send the email
envelope.Item.Send()