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)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()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()This copies a range of cells from Excel and anycodings_outlook pastes them into a Word document with anycodings_outlook formatting preserved. The code works for anycodings_outlook this. However, I also want to paste the data anycodings_outlook into the body of an email with the cell anycodings_outlook styles.,This copies a range of cells from Excel anycodings_outlook and pastes them into a Word document anycodings_outlook with formatting preserved. The code anycodings_outlook works for this. However, I also want to anycodings_outlook paste the data into the body of an email anycodings_outlook with the cell styles.,I believe [PasteExcelTable]2 can be used to anycodings_outlook make this happen with Outlook as it does for anycodings_outlook Word files and perhaps someone knows how anycodings_outlook this can be done.,I know this could be accomplished with VBA, anycodings_outlook but I want to use Python and to see whether anycodings_outlook PasteExcelTable will work for this. If it is anycodings_outlook not possible, I will capture an image of the anycodings_outlook data range and paste that to the email.
This copies a range of cells from Excel and anycodings_outlook pastes them into a Word document with anycodings_outlook formatting preserved. The code works for anycodings_outlook this. However, I also want to paste the data anycodings_outlook into the body of an email with the cell anycodings_outlook 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'm not defining the range correctly. Here's anycodings_outlook an example of one of my attempts:
new_mail.Body = ws.Range("A1:B2").PasteExcelTable(False, False, True)After hours of research and anycodings_outlook experimentation I found out how to use anycodings_outlook PasteExcelTable in Outlook. I hope this anycodings_outlook can be helpful. You only need to have anycodings_outlook 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)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()I was unable to use PasteExcelTable, but anycodings_outlook I was able to accomplish this with anycodings_outlook 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()📄 URL: https://github.com/israel-dryer/Outlook-Python-Tutorial/wiki/Copy-%26-paste-Excel-table-to-Outlook ,In this tutorial, I'm going to show you how to copy and paste a range from an Excel spreadsheet into an Outlook message.,There are no ads in this search engine enabler service. The button and/or link at the top will take you directly to GitHub.,Before we get started, you'll need to make sure you install the pillow library. This will be used the process the range in Excel, which will be copied to the clipboard as an image.
pip install pillow
import os
import win32com.client as client
from PIL
import ImageGrab
# make sure you use either a raw string OR escape characters
for the backslash.
# You CANNOT use forward slash format with `win32com`.
workbook_path = r 'C:\path\to\your\workbook.xlsx'
# start an instance of Excel
excel = client.Dispatch('Excel.Application')
# open the workbook
wb = excel.Workbooks.Open(workbook_path)
# select the sheet you want...use whatever index method you want to use....
# # # by item
sheet = wb.Sheets.Item(1)
# # # by index
sheet = wb.Sheets[0]
# # # by name
sheet = wb.Sheet['Sheet1']
# copy the target range
copyrange = sheet.Range('A1:G13')
copyrange.CopyPicture(Appearance = 1, Format = 2)
# grab the saved image from the clipboard and save to working directory
ImageGrab.grabclipboard().save('paste.png')# get the path of the current working directory and create image path
image_path = os.getcwd() + '\\paste.png'
# create a html body template and set the **src** property with `{}` so that we can use
# python string formatting to insert a variable
html_body = """
<div>
Please review the following report and response with your feedback.
</div>
<div>
<img src={}></img>
</div>
"""
# startup and instance of outlook
outlook = client.Dispatch('Outlook.Application')
# create a message
message = outlook.CreateItem(0)
# set the message properties
message.To = someone@email.com
message.Subject = 'Please review!'
message.HTMLBody = html_body.format(image_path)
# display the message to review
message.Display()
# save or send the message
message.Send()09/13/2021
This example pastes an Excel table into the active document. The parameters specify that the pasted table is linked to the Excel file, retains the original Excel formatting, and is pasted as RTF. This example assumes that the Clipboard contains an Excel table.
Sub PasteExcelFormatted() Selection.PasteExcelTable _ LinkedToExcel: = True, _ WordFormatting: = False, _ RTF: = True End Sub