- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我必须将两个选定的范围从两个不同的工作簿粘贴到邮件正文中。
第一个工作簿是宏所在的父工作簿。我们从那里选择一个范围,通过宏打开第二个工作簿并选择一个范围。
这两个范围都将粘贴到 Outlook 邮件正文中。
我尝试了以下宏以及 rangetohtml 函数。
Sub Mail_Selection_Range_Outlook_Body()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim rng As Range
Dim rng2 As Range
Dim OutApp As Object
Dim OutMail As Object
Set Parent_wkb = ThisWorkbook
cnt_row = Parent_wkb.Worksheets("1Summary - All").Cells(Rows.Count, 1).End(xlUp).Row
cnt_col = Parent_wkb.Worksheets("1Summary - All").Cells(3, Columns.Count).End(xlToLeft).Column
l1 = cnt_row + 7
last_box = cnt_col - 2
last_box_Ltr = Evaluate("substitute(address(1, " & last_box & ", 4), ""1"", """")")
r1 = "B2:" & last_box_Ltr & l1
hide1 = cnt_col - 10
hide1_cell = Evaluate("substitute(address(1, " & hide1 & ", 4), ""1"", """")")
hide2 = cnt_col - 11
hide2_cell = Evaluate("substitute(address(1, " & hide2 & ", 4), ""1"", """")")
Worksheets("1Summary - All").Columns(hide2_cell & ":" & hide1_cell).Select
Selection.EntireColumn.Hidden = True
Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = Worksheets("1Summary - All").Range(r1).SpecialCells(xlCellTypeVisible)
' You can also use a range with the following statement.
' Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
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 rng_dt = Worksheets("1Summary - All").Range("AGQ2")
dt = rng_dt.Cells(1, 1).Value
dt_formatted1 = Format(dt, "dd-MMMM-yyyy")
dt_formatted2 = Format(dt, "dd MMMM yyyy")
' folder = Mid(dt, 2, 4)
'open the global variable file to get the acu file name along with the file name to be attached
'Attach_xcl - an excel workbook to be attached (working fine)
'body_xcl - another excel workbook, from where I have to select the range
'both the workbook names along with their path are stored in this global variable file(working fine)
Set wkb1 = Workbooks.Open(Filename:="\\vfpnbrgspr0\LESR_Phase2_R_RPA\Sayan\Global_Variable_Singapore.xlsx")
Set rng_xcl1 = wkb1.Worksheets("Global_Variable").Range("B17")
Attach_xcl = rng_xcl1.Cells(1, 1).Value
Set rng_xcl2 = wkb1.Worksheets("Global_Variable").Range("B18")
Body_xcl = rng_xcl2.Cells(1, 1).Value
ActiveWorkbook.Close
'open the acu limit(Body_xcl) file for second table for mail body
Set wkb2 = Workbooks.Open(Filename:=Body_xcl)
Set rng2 = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng2 = wkb2.Worksheets("Biz Breakdown").Range("body").SpecialCells(xlCellTypeVisible)
' You can also use a range with the following statement.
' Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng2 Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
wkb2.Close
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
wkb2.Close
'------------------------------PREPARE THE MAIL TO BE SENT---------------------------------------------'
Dim StrBody1 As String
' Build the string that you want to add.
StrBody1 = "Dear All," & "<br><br>" & _
"This is confidential." & "<br><br><br>"
Dim StrBody2 As String
' Build the string that you want to add.
StrBody2 = " " & "<br><br><br><br>" & _
"Thanks," & "<br>" & _
"user" & "<br>"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
' .To = "xxxx"
.To = "xxxx"
.CC = "xxxx"
.BCC = "xxxx"
.Subject = "This is confidential" & dt_formatted2
'THE LINE BELOW IS NOT WORKING, IF I WORK WITH 'rng' ITS WORKING FINE, WHEN I ADD THE SECOND 'rng2' THE BODY OF THE MAIL IS BLANK
.HTMLBody = StrBody1 & RangetoHTML(rng) & " " & RangetoHTML(rng2) & StrBody2
'.HTMLBody = RangetoHTML(rng2) & StrBody2
.Attachments.Add (Attach_xcl)
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
.Send
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Worksheets("1Summary - All").Columns(hide2_cell & ":" & hide2_cell).Select
Selection.EntireColumn.Hidden = False
Worksheets("1Summary - All").Columns(hide1_cell & ":" & hide1_cell).Select
Selection.EntireColumn.Hidden = False
End Sub
Function RangetoHTML(rng As Range)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
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 workbook to receive the data.
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 an .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 the RangetoHTML subroutine.
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.
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
最佳答案
您遇到的问题来自您关闭 wkb2
的事实。转换前rng2
到 html。当您到达 RangetoHTML(rng2)
线,rng2
不再可用,如果 wkb2
已经关了。
所以你应该转换 rng2
在您关闭 wkb2
之前,将其转换为 html 并将其存储在变量中.
你可以这样做:
If rng2 Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
wkb2.Close
Exit Sub
Else
Dim HtmlRng2 as string
HtmlRng2 = RangeToHtml(rng2)
End If
.HTMLBody = StrBody1 & RangetoHTML(rng) & " " & RangetoHTML(rng2) & StrBody2
.HTMLBody = StrBody1 & RangetoHTML(rng) & " " & HtmlRng2 & StrBody2
wkb2
直到您填写了您的电子邮件
htmlbody
与范围。
关于excel - 将两个不同工作簿中的两个范围粘贴到 Outlook 邮件正文中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57881972/
我在休息服务中有以下方法: @POST @Path("/create") @ResponseStatus(HttpStatus.CREATED) @Consumes(M
这个问题不太可能对 future 的访客有帮助;它只与一个小的地理区域、一个特定的时刻或一个非常狭窄的情况相关,通常不适用于互联网的全局受众。如需帮助使这个问题更广泛地适用,visit the hel
我有这样的弹出框: Speelland And here's some amazing content. It's very engaging. Right? Meer
我正在开发一个 firefox 插件,我正在收听这样的 http 响应: var observerService = Components.classes["@mozilla.org/observer
我正在使用 jqtouch 制作一个移动网站。我还在网站中实现了图库图像 slider ,但是当图库放在我需要的位置时(在 之间,图像不会显示。 修补了几个小时后,删除了 display: none
为了在 iPad 上的 Safari 上显示视差效果,我采用了以下 CSS 规则: body:after { content: ""; position: fixed; top
我想在通过 excel VBA 创建的电子邮件正文中插入一个链接。链接每天都在变化,所以我把它的值放在单元格 B4 中。但是,我找不到正确的方法来发送带有该链接的电子邮件。 这是我正在使用的代码: P
我正在尝试使用具有非常大主体的 Postman 执行 POST 请求。只有一个 JSON 字段非常大,我想知道是否可以从 Postman 的文件中加载该字段? { "field1": {
这个问题是针对 SoapUI 5.2.1 社区版的: 我有一个包含变量的 JSON 主体的 POST 请求。 我总是能够通过单击“原始”选项卡以查看请求进行或将发送到服务器来验证这些参数是否采用正确的
我有这个按钮,单击该按钮会打开 Outlook,其中包含我提供的详细信息。我还有一个 TEXTAREA,其中包含某些文本。我正在寻找一种方法让此文本出现在我的 Outlook 正文中。这可以做到吗?请
我知道错误消息是不言自明的,我们无法多次读取消息正文。这里我使用AOP(面向方面编程)来进行审计日志。 [AuditServiceMethod(AttributePriority = 0)] [F
我在 grails 3.3.3 中编写自定义验证器(命令)时遇到了一些问题。具体来说,我正在尝试验证其正文由项目列表组成的 POST 请求。这就是我所拥有的... 命令: class VoteComm
这个问题在这里已经有了答案: json.Marshal(struct) returns "{}" (3 个回答) JSON and dealing with unexported fields (3
我想清理很多邮件的 HTML 正文,它们有点脏(取自 Gmail 发送的电子邮件):有很多嵌套 ,不需要的字体更改等我想清理它并只保留 , , , , , 仅此而已(可能还有 或一些 ,
我正在使用 Accordion 功能在我的模块中添加端口详细信息。我只想在水平方向上显示正文内容。请看下面的 fiddle 。 html, body { background-color:#e
我的 HTML 正文中有这个: loaded y&EACUTE;t. 使用 JavaScript 我有这个: $( document ).ready(function() { document.bod
我对图表有很大的疑问。我试图在谷歌图表中显示一些 json 值,但我总是会出错。从 JSON 正文中,我只需要图表上个月的“全部购买”和“日期”。我见过的所有例子,他们已经有了一个静态的自定义 Jso
我的应用程序的功能之一涉及用户填写三个单独的文本字段(预订名称、客人和日期),然后使用文本编辑器通过短信发送这些字段中的文本。我无法将这些 View 中的文本放入正文中。这是我的代码: - (IBAc
我正在开发一个 HTA,它应该对 onunload 事件进行一些最终修改。该事件似乎没有被触发。 该事件是否仍受支持?是否有 IE 事件可以知道页面何时关闭? 我检查了一下(JavaScript bo
我正在尝试将以下图像添加为网站内容的背景: http://webbos.co/vibration/wp-content/themes/vibration-child-theme/images/back
我是一名优秀的程序员,十分优秀!