VBA 将sheets表格复制到outlook邮件正文 发送邮件
时间: 2024-01-22 10:20:34 浏览: 28
以下是 VBA 代码,可以将 Excel 中的 Sheet 复制到 Outlook 邮件正文并发送邮件:
```VBA
Sub SendSheetInEmail()
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Dim excelSheet As Worksheet
Dim tempFile As String
'Create a temporary file to store the sheet as an HTML file
tempFile = Environ$("temp") & "\" & ActiveSheet.Name & ".html"
ActiveSheet.PublishObjects.Add(xlSourceSheet, tempFile, ActiveSheet.Name, "", xlHtmlStatic).Publish (True)
'Create a new email
Set outlookApp = New Outlook.Application
Set outlookMail = outlookApp.CreateItem(olMailItem)
'Set the email properties
With outlookMail
.To = "recipient@example.com"
.Subject = "Sheet " & ActiveSheet.Name & " from " & ThisWorkbook.Name
.HTMLBody = "Hello," & vbCrLf & _
"Please find attached the sheet " & ActiveSheet.Name & " from " & ThisWorkbook.Name & "." & vbCrLf & _
"Best regards,<br>" & _
"Your Name"
.Attachments.Add tempFile
.Display 'or .Send to directly send the email
End With
'Delete the temporary file
Kill tempFile
'Clean up
Set outlookMail = Nothing
Set outlookApp = Nothing
End Sub
```
此代码将当前活动的 Sheet 复制为 HTML 文件,将该文件作为附件添加到邮件中,并在邮件正文中添加一条消息。 若要将多个 Sheet 添加到邮件正文中,则需要将每个 Sheet 复制为 HTML 文件,并将所有文件合并为单个文件,然后将该文件添加到邮件正文中。
请注意,此代码需要 Outlook 客户端才能发送邮件。 如果没有 Outlook 客户端,则需要使用其他方法发送邮件,例如使用 SMTP 服务器发送邮件。