4

Formatted Excel range copied to a Word file

This copies a range of cells from Excel and pastes them into a Word document with formatting preserved. The code works for this. However, I also want to paste the data into the body of an email with the cell styles.

import sys
from pathlib import Path
import win32com.client as win32


excel_path = str(Path.cwd() / 'input.xlsx')
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False
wb = excel.Workbooks.Open(excel_path)
ws = wb.Worksheets(1)
ws.Range("A1:B2").Copy()
wb.Close()

word_path = str(Path.cwd() / 'output.docx')
word = win32.gencache.EnsureDispatch('Word.Application')
doc = word.Documents.Open(word_path)
doc.Content.PasteExcelTable(False, False, True)
doc.Close()

# That works, but now I want to paste the copied Excel range into the body of
# an email. The solution may look something like the following with the "X"
# as a "Selection" or "Range" object for the PasteExcelTable method.

outlook = win32.gencache.EnsureDispatch('Outlook.Application')
new_mail = outlook.CreateItem(0)
new_mail.To = 'person@email.com'

new_mail.Body = ws.Range("A1:B2").PasteExcelTable(False, False, True)

new_mail.Send()
sys.exit()

I believe [PasteExcelTable]2 can be used to make this happen with Outlook as it does for Word files and perhaps someone knows how this can be done.

I'm not defining the range correctly. Here's an example of one of my attempts:

    new_mail.Body = ws.Range("A1:B2").PasteExcelTable(False, False, True)

I know this could be accomplished with VBA, but I want to use Python and to see whether PasteExcelTable will work for this. If it is not possible, I will capture an image of the data range and paste that to the email.

Jeffrey Neil Willits
  • 51
  • 1
  • 2
  • 6
  • Possible duplicate of [How to paste excel data table into outlook through vba](https://stackoverflow.com/questions/31453759/how-to-paste-excel-data-table-into-outlook-through-vba) – Mikku Jul 14 '19 at 02:10

4 Answers4

3

After hours of research and experimentation I found out how to use PasteExcelTable in Outlook. I hope this can be helpful. You only need to have the Excel Table in the clipboard.

import win32com.client


outlook = win32com.client.Dispatch('Outlook.Application')
mail = outlook.CreateItem(0)
mail.To = "person@email.com"
mail.Subject = "Subject"
mail.Display()
inspector = outlook.ActiveInspector()
word_editor = inspector.WordEditor
word_range = word_editor.Application.ActiveDocument.Content
word_range.PasteExcelTable(False, False, True)
LVM
  • 31
  • 2
  • 1
    Hello @LVM, thanks so much.How this works without assign mail body statement?Besides, I found when excel sheet is pasted on the email body, not all, a few format(merged cells are not merged anymore or non-merged cell become merged, a few cells; font color is reversed) lose.How can I fix those? – Erik Johnsson Jun 07 '20 at 18:47
1

Hope this will help...

import win32com.client as win32
import pandas as pd

df = pd.read_excel('fullexelpath.xlsx', index_col=False, nrows = 5,  usecols = "A:D")

body = df.to_html()

outlook = win32.gencache.EnsureDispatch('Outlook.Application')
mail = outlook.CreateItem(0)
mail.To = 'abc@gmail.com'
mail.HTMLBody = (body)
mail.Send()
Hietsh Kumar
  • 1,197
  • 9
  • 17
0

This copies a range of cells from Excel and pastes them into a Word document with formatting preserved. The code works for this. However, I also want to paste the data into the body of an email with the cell styles.

There are three main ways for working with bodies in Outlook:

  1. Body - a plain text representing the message body.
  2. HTMLBody.
  3. Word editor. Outlook uses Word as an email editor. The Inspector class provides the WordEditor property which returns an instance of the Document class from the Word object model which represents the message body. Or just use the [GetInspector] method of the MailItem class to retrieve an instance of the Inspector class. You can read more about that in the Chapter 17: Working with Item Bodies .

So, basically you can re-use the same codebase used for Word in case of Outlook.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • Good references. With no mention of PasteExcelTable there, perhaps it cannot be used with Outlook. This surprises me though, since an Excel range can be manually copied and pasted into Outlook and PasteExcelTable makes this possible with Word. – Jeffrey Neil Willits Jul 14 '19 at 17:06
  • I accomplished this without using PasteExcelTable and instead took the HTMLBody approach. I may edit the question to add this code. – Jeffrey Neil Willits Jul 14 '19 at 20:05
0

I was unable to use PasteExcelTable, but I was able to accomplish this with HTMLBody using the following code:

import sys
from pathlib import Path
import win32com.client as win32
from PIL import ImageGrab

excel_path = str(Path.cwd() / 'input.xlsx')
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False
wb = excel.Workbooks.Open(excel_path)
ws = wb.Worksheets(1)

win32c = win32.constants
ws.Range("A1:B2").CopyPicture(Format=win32c.xlBitmap)
img = ImageGrab.grabclipboard()
image_path = str(Path.cwd() / 'test.png')
img.save(image_path)

outlook = win32.gencache.EnsureDispatch('Outlook.Application')
new_mail = outlook.CreateItem(0)
new_mail.To = 'person@email.com'
new_mail.Attachments.Add(Source=image_path)
body = "<h1>Email text...</h1><br><br> <img src=test.png>"
new_mail.HTMLBody = (body)
new_mail.Send()
wb.Close()
sys.exit()
Jeffrey Neil Willits
  • 51
  • 1
  • 2
  • 6