在Outlook中使用Python创建多个excel表

Question

我需要用3个excel表格发送Outlook电子邮件。

• 我有一个excel文件- master_file.csv(这个文件是由熊猫数据框架的自动数据填充)

• 在这个文件中，我有一个工作表(Sheet1)与3个表

• 这些表的列数总是相同的:

  • table_1 from A to R

  • table_2 from S to AJ

  • table_3 from AK to BD

• 行数每次都在变化，所以行范围应该取决于填充的单元格(可能是XlDirectionDown)

• 这些表格在Excel文件中有自己的格式-这种格式需要复制到电子邮件

Email应该像这样:

"文本">

"表1">

的Text">

"表2">

"Text">

"表3">

"Text">

我已经试过下面的代码，但不能把这些都弄清楚，我碰到了100个选项，没有一个是有效的。

当表格需要根据行中填充的单元格确定时，您可以帮助我解决将excel表格添加到outlook电子邮件中的问题吗?

import sys
from pathlib import Path
import win32com.client as win32
from PIL import ImageGrab
excel_path = str(Path.cwd() / 'master_file.xlsm')
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:R11").CopyPicture(Appearance=1, 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 = '[e-mail]'
new_mail.Attachments.Add(Source=image_path)
body = "<h1>Email text...</h1><br><br> <img src=test.png>"
new_mail.HTMLBody = (body)
new_mail.Display()
wb.Close()```

Answer 1

我在回答我自己的问题，因为我找到了一个解决方案，也许它会对别人有帮助。实际上有两种解决方案，但在我看来，第二种更适合于漂亮的电子邮件。

第一个解决方案:我们有一个csv/excel文件。

import sys
from pathlib import Path
import win32com.client as win32
from PIL import ImageGrab
import xlwings as xw
# open raw data file
filename_read = 'master_file.csv'
wb = xw.Book(filename_read)
sht = wb.sheets[0]
# find the numbers of columns and rows in the sheet
num_col = sht.range('A1').end('right').column
num_row = sht.range('A4').end('down').row
# collect data
content_list = sht.range((1,1),(num_row,num_col-1))

excel_path = str(Path.cwd() / 'master_file.xlsm')
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(f'A1:R{num_row}').CopyPicture(Appearance=1, Format=win32c.xlBitmap)
img = ImageGrab.grabclipboard()
image_path1 = str(Path.cwd() / 'test.png')
img.save(image_path1)
win32c = win32.constants
ws.Range(f'S1:AJ{num_row}').CopyPicture(Appearance=1, Format=win32c.xlBitmap)
img = ImageGrab.grabclipboard()
image_path2 = str(Path.cwd() / 'test2.png')
img.save(image_path2)
outlook = win32.gencache.EnsureDispatch('Outlook.Application')
new_mail = outlook.CreateItem(0)
new_mail.To = '[e-mail]'
new_mail.Attachments.Add(Source=image_path1)
new_mail.Attachments.Add(Source=image_path2)
body = "<h1>Hello team,</h1> <br><br> <h2> Here are data for yesterday.</h2> <br><br> <h2>Call Metrics:</h2> <br><br> <img src=test.png width=1700 height=600> <br><br> <h2>Back office metrics:</h2> <img src=test2.png width=1700 height=600> "
new_mail.HTMLBody = (body)
new_mail.Display()
wb.Close()

这个代码正在搜索一个表端并拍摄它的照片并将其发送到电子邮件。当你总是有相同的行数时，这是一个很好的解决方案。但是如果改变很多(比如一次30行，第二次100行)，如果你像我一样设置一个固定的宽度和高度，图像就会太小或太大。

第二个解决方案:为一个表创建一个excel(即"table_1.xlsm"， "table_2.xlsm")放一个简单的VBA代码

Sub auto_open()
Application.ScreenUpdating = False
Application.AlertBeforeOverwriting = False
Application.DisplayAlerts = False


Range("A4:R200").Clear


Workbooks.Open "C:Usersxxxxxxxmaster_source.csv"
Windows("master_source.csv").Activate
'This range below select data till rows are filled'
Range("A2:R2", Range("B2:R2").End(xlDown).End(xlToRight)).Select
Range("A2").Activate
Selection.Copy
Windows("table_1.xlsm").Activate
Sheet1.Select
Range("A4").Select
Sheet1.Paste
Windows("master_source.csv").Application.CutCopyMode = False
Windows("master_source.csv").Close

Range("A2:R2", Range("B2:R2").End(xlDown).End(xlToRight)).Borders.LineStyle = XlLineStyle.xlContinuous
Range("A2:R2", Range("B2:R2").End(xlDown).End(xlToRight)).HorizontalAlignment = xlCenter
Range("A2:R2", Range("B2:R2").End(xlDown).End(xlToRight)).VerticalAlignment = xlCenter

ActiveWorkbook.Save

Application.Quit

End Sub

另存为网页- (!! !)这一点很重要——只保存一张纸。如果你保存整个工作簿，它将保存为框架，这是Outlook不支持的。保存它并勾选AutoRepublish(每次保存后它都会更新我们的HTML文件)。然后这个Python代码

import os, time, sys
from datetime import datetime, timedelta, date
from pathlib import Path
import win32com.client as win32
#seting up yesterday date and date format
d = date.today() - timedelta(days=1)
dt = d.strftime("%d/%m/%y")
#saving copy of the file for future usage
filepath = Path(f'C:/Users/xxxxxxxx/Agent report {d}.csv')
filepath.parent.mkdir(parents=True, exist_ok=True)
master_df.to_csv(filepath)
#updating 3 tables - it opens every table file and then VBA doing it's job automatically as it is "auto_open"
for x in range(1, 4):
os.system(f'start "excel" "C:\xxxxxxxxxxx\table_{x}.xlsm"')
time.sleep(10)


outlook = win32.gencache.EnsureDispatch('Outlook.Application') 
mail = outlook.CreateItem(0)

mail.To = '[e-mail]'
mail.Subject = f'Agent report for {dt}'

table1 = open(r'C:xxxxxxxxxxxxxxxxxxxxxxtable_1.htm').read()
table2 = open(r'C:xxxxxxxxxxxxxxxxxtable_2.htm').read()
table3 = open(r'C:xxxxxxxxxxxxxxxxxxxxxtable_3.htm').read()
mail.HTMLBody = f"""
<html>
<head></head>
<body>

Hello team,<br><br> 
Below are metrics for your agents for previous day<br><br>

<b>First Metrics:</b><br><br>

{table1}<br><br>


<b>Second metrics:</b><br><br>

{table2}<br><br>


<b>Third statistics:</b><br><br>

{table3}<br>

Reference:<br>

Kind regards,<br>
</body>
</html>
"""
mail.Send()