Using PasteExcelTable to copy Excel data to the body of an Outlook email with Python

excel vba copy range to email body
vba code to copy and paste data from excel to outlook
python copy excel table to email
how to paste excel table in outlook mail using vba
vba copy from excel to outlook with formatting
send excel sheet as email body
excel vba copy pivot table to email body
how to copy cells in excel using python

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.

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)

Send Excel data in outlook body with borders, Send Excel data in outlook body with borders Send data from Excel to the body of your Duration: 3:34 Posted: Apr 1, 2018 Using PasteExcelTable to copy Excel data to the body of an Outlook email with Python. 0. How can I refer a hyperlink to a cell value in the body of an email?

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.

Automating Windows Applications Using COM, Introduction to using python and Microsoft's COM technology to automate Windows applications. This example shows how to do some data manipulation , open up a Outlook email, attach a file Use com to copy the files around excel = win32.gencache. Body = X.PasteExcelTable(False, False, False) Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode

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()

Excel vba to copy table to outlook body - MSDN, I have a table in excel workbook's (sheet1), now i want to copy the table and Here is an example of adding a table into the body of an email. I want to copy data from a fixed range in an Excel sheet and paste on an email body. Below is the code I have come up with. However I am not able to paste the specified range A11:H12. Private Sub

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()

Read a spreadsheet and send html emails with python, Send an html email using a built in library from python called Since we're already sending emails, let's shift our focus to reading an excel spreadsheet and work across all the email clients I've tested (hotmail, gmail, yahoo, outlook). DOCTYPE html> <html> <body> <p style="text-align: center"> Hello,� : Send an html email using a built in library from python called “smtplib” (simple-mail-transfer-protocol–SMTP). Goal #2: We’ll send multiple emails with dynamically filled data by importing information from an excel file. (cool, dude) Estimated time: 30 minutes to 1 hour, depending on experience.

how to copy from excel to outlook and keep the format?, I assume you mean pasting Excel data into email body. 1) In order to not mess formats I select part of the sheet I want to copy, classic Ctrl+C and use special� VBA to copy/paste from Excel to Outlook I have a spreadsheet that I want to, periodically, paste a range of cells into an email. I already have the code that creates the email, but can I select a range, that can vary in size from email to email, and paste that into the body of the email?

Embedding an Excel sheet in an email, Open your email client and then just paste it in the body of your message. First you paste in Outlook & after that copy that Excel data & again paste into Gmail. copy excel sheet header and a row in the outlook mail “body” and send to multiple users (each row to each user+ header which is common for all) Please suggest some code which could convert the datatable to string such as its tabular format is not lost. Also, Screen scraping or copy is not working for that excel sheet.

How do I paste multiple set of ranges as image in Outlook? : excel, Display 'display email End With 'prepare email body using word editor for custom PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False ' third text . Move wdParagraph, 1 'copy a range and paste a picture ThisWorkbook. This spreadsheet heavily uses named ranges and named formulas. when you start e-mail marketing , You need opt-in email address list. You have opt-in list. You are using email client software and If you can export your list from your email client, You will have good list. Now I am trying to explain my codes to write all emails into test file from your outlook profile.

Comments
  • Possible duplicate of How to paste excel data table into outlook through vba
  • 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?
  • 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.
  • I accomplished this without using PasteExcelTable and instead took the HTMLBody approach. I may edit the question to add this code.