粘贴特定的excel范围在展望 [英] Paste specific excel range in outlook
问题描述
我正在处理一个我想要自动化的excel报告,但是,单元格的范围没有被粘贴到Outlook中。
I am working on an excel report that I want to automate, however, the range of the cells are not being pasted in Outlook.
这是我的代码:
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = Selection.SpecialCells(xlCellTypeVisible)
Set rng = Sheets("Sheet1").RangeToHtml("D4:D12").SpecialCells(xlCellTypeVisible, xlTextValues)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("Sheet2").Range("C1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangeToHtml.rng
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
我没有收到任何错误,只是它不会在Outlook中粘贴范围。谢谢。
I am not getting any error, it's just that it does not paste range in outlook. Thank you.
推荐答案
首先, RangeToHTML
。脚本调用它像一个方法,但它不是。由MVP Ron de Bruin 功能 >。巧合的是,这些链接指向您发布的脚本的确切来源,之前几行已经修改了。
First off, RangeToHTML
. The script calls it like a method, but it isn't. It's a popular function by MVP Ron de Bruin. Coincidentally, that links points to the exact source of the script you posted, before those few lines got b̶u̶t̶c̶h̶e̶r̶e̶d̶ modified.
启用 Range.SpecialCells 的。该方法在一个范围内运行,只返回符合给定条件的那些单元格。在您的情况下,您似乎只对可见文本单元格感兴趣。重要的是,它运行在范围而不是HTML文本上。
On with Range.SpecialCells. This method operates on a range and returns only those cells that match the given criteria. In your case, you seem to be only interested in the visible text cells. Importantly, it operates on a Range, not on HTML text.
为了完整起见,我将在下面发布一个工作版本的脚本。我一定会建议忽略它,并重新审视Ron Bruin的优秀作品。
For completeness sake, I'll post a working version of the script below. I'd certainly advise to disregard it and revisit the excellent original by Ron the Bruin.
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Sheets("Sheet1").Range("D4:D12").SpecialCells(xlCellTypeVisible)
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ThisWorkbook.Sheets("Sheet2").Range("C1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
' By Ron de Bruin.
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 = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
这篇关于粘贴特定的excel范围在展望的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!