将Excel范围作为图片粘贴到电子邮件中 [英] Pasting an Excel range into an email as a picture
问题描述
以下是我到目前为止的代码。我在哪里出错？
Sub Button193_Click（）
'
'Button193_Click宏
'
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
范围（C3：S52）。选择
Selection.Copy
End Sub
Sub CreateMail（）
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range $
设置objMail = objOutlook.CreateItem（0）
使用ActiveSheet
设置rngTo = .Range（E55）
设置rngSubject = .Range（E56）
设置rngBody = .Range（E57）
结束
与objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
。显示'而不是.Display，您可以使用。发送发送电子邮件_
或.Save在草稿文件夹中保存副本
结束与
设置objOutlook = Nothing
设置objMail = Nothing
设置rngTo = Nothing
设置rngSubject =没有
设置rngBody =没有
设置rngAttach =没有
End Sub
Sub Button235_Click（）
'
'Button235_Click宏
'
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
范围（A1：M27）。选择
Selection.Copy
End Sub
Sub RunThemAll（）
应用程序.RunButton193_Click
Application.RunCreateMail
End Sub
这是一个在Office 2010中测试的工作示例：
'复制范围感兴趣
Dim r As Range
设置r =范围（B2：D5）
r.Copy
'打开一个新的邮件项
Dim outlookApp As Outlook.Application
设置outlookApp = CreateObject（Out看看应用程序）
Dim outMail As Outlook.MailItem
设置outMail = outlookApp.CreateItem（olMailItem）
'获取其Word编辑器
outMail.Display
Dim wordDoc As Word.Document
设置wordDoc = outMail.GetInspector.WordEditor
'粘贴为图片
wordDoc.Range.PasteAndFormat wdChartPicture
'粘贴为一个表
'wordDoc.Range.PasteExcelTable LinkedToExcel：= False，WordFormatting：= False，RTF：= False
结果：
在上面的代码中，我使用早期绑定来访问自动完成;要使用此代码，您需要设置对Microsoft Outlook和Microsoft Word对象库的引用：工具> 引用... >设置如下所示的复选标记：
或者，您可以忘记引用并使用延迟绑定，声明所有Outlook和Word对象 As Object 而不是作为Outlook 。应用和作为Word.Document 等。
显然，您在执行上述方面遇到困难;该范围作为表格而不是电子邮件中的图片。我没有解释为什么会发生这种情况。
然后在Excel中将其替换为图像，然后将该图像剪切并粘贴到您的电子邮件中：
'复制感兴趣范围
Dim r As Range
设置r =范围（B2：D5 ）
r.Copy
'粘贴为图片并立即剪切
Dim p As Picture
设置p = ActiveSheet.Pictures.Paste
p.Cut
'打开一个新的邮件项
Dim outlookApp作为Outlook.Application
设置outlookApp = CreateObject（Outlook.Application）
Dim outMail As Outlook .MailItem
设置outMail = outlookApp.CreateItem（olMailItem）
'获取其Word编辑器
outMail.Display
Dim wordDoc As Word.Document
设置wordDoc = outMail.GetInspector.WordEditor
'粘贴图片
wordDoc.Range.Paste
正如
I'm creating an Outlook email from Excel (Office 2013). I want to paste a range of cells (C3:S52) into the email as a picture.
Below is the code I have so far. Where am I going wrong?
Sub Button193_Click()
'
' Button193_Click Macro
'
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C3:S52").Select
Selection.Copy
End Sub
Sub CreateMail()
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Dim rngAttach As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With ActiveSheet
Set rngTo = .Range("E55")
Set rngSubject = .Range("E56")
Set rngBody = .Range("E57")
End With
With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing
Set rngAttach = Nothing
End Sub
Sub Button235_Click()
'
' Button235_Click Macro
'
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1:M27").Select
Selection.Copy
End Sub
Sub RunThemAll()
Application.Run "Button193_Click"
Application.Run "CreateMail"
End Sub
Here's a worked example, tested in Office 2010:
'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy
'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)
'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor
'To paste as picture
wordDoc.Range.PasteAndFormat wdChartPicture
'To paste as a table
'wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
Result:
In the code above I used early binding to have access to autocomplete; to use this code you need to set references to the Microsoft Outlook and Microsoft Word object libraries: Tools > References... > set checkmarks like this:
Alternatively, you can forget about the references and use late binding, declaring all the Outlook and Word objects As Object instead of As Outlook.Application and As Word.Document etc.
Apparently you're having trouble implementing the above; the range pastes as a table rather than a picture in your email message. I have no explanation for why that would happen.
An alternative is then to paste as an image in Excel, and then cut and paste that image into your e-mail:
'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy
'Paste as picture in sheet and cut immediately
Dim p As Picture
Set p = ActiveSheet.Pictures.Paste
p.Cut
'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)
'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor
'Paste picture
wordDoc.Range.Paste
As pointed out by WizzleWuzzle, there is also the option of using PasteSpecial instead of PasteAndFormat or Paste...
wordDoc.Range.PasteSpecial , , , , wdPasteBitmap
... but for some reason, the resulting image doesn't render as well. See how the lower table is kind of blurry:
这篇关于将Excel范围作为图片粘贴到电子邮件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
