在电子邮件中左对齐嵌入的范围 [英] Left Align embedded range in Email
问题描述
我无法获得内嵌在电子邮件中的范围左对齐。我尝试过几件事，但嵌入的部分仍然在电子邮件中。这是我的代码，具有讽刺意味的是，在其他电子表格中工作得很好。我尝试添加HTML标签，更改功能，都无济于事。任何帮助，将不胜感激。这是在W7x64和Office 2010上。在这个报告中，我嵌入了一个数据透视表而不是一个常规范围。
谢谢。
选项显式
SalesSub Mail_RegionalRANGE（）
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
'On Error Resume Next
Set OutApp = CreateObject（outlook.application）
Set OutMail = OutApp.CreateItem（olMailItem）
使用OutMail
.SentOnBehalfOfName =SalesAnalytics @ company。我们
。显示
.Subject =销售报告
.To =mike.marshall@company.us
'.CC =
'.BCC =
'.Attachments.Add\\filesrv1\department shares\Sales Report\Sales Report.xlsx
.HTMLBody =< br> _
& 附件是销售报告，如有任何问题，请联系我。 _
& <峰; br><峰; br> 中_
& < p align = left> &安培; fncRangeToHtml（RegAEPctg，B2：P67）& < P> 中_
& .HTMLBody
。显示
'。发送
End With
Set OutApp = Nothing
Set OutMail = Nothing
End Sub
私有函数fncRangeToHtml（_
strWorksheetName As String，_
strRangeAddress As String）As String
Dim objFilesytem As Object，objTextstream As Object，objShape As Shape
Dim strFilename As String，strTempText As String
Dim blnRangeContainsShapes As Boolean
Set objTextstream = Nothing
Set objFilesytem = Nothing
'kill strFilename
strFilename = Environ $（temp）& \& _
格式（现在，dd-mm-yy_h-mm-ss）& .htm
ThisWorkbook.PublishObjects.Add（_
SourceType：= xlSourceRange，_
Filename：= strFilename，_
Sheet：= strWorksheetName，_
来源：= strRangeAddress，_
HtmlType：= xlHtmlStatic）.Publish True
Set objFilesytem = CreateObject（Scripting.FileSystemObject）
Set objTextstream = objFilesytem.GetFile （strFilename）.OpenAsTextStream（1，-2）
strTempText = objTextstream.ReadAll
objTextstream.Close
strTempText = Replace（strTempText，align = center x：publishsource =，align =如果不是相交（objShape.TopLeftCell，工作表（_
strWorksheetName）.Range（strRangeAddress） ））没有那么
blnRangeContainsShapes = True
退出对于
结束如果
下一个
如果blnRangeContainsShapes Then _
strTempText = fncConvertPictureToMail（strTempText，Wor ksheets（strWorksheetName））
fncRangeToHtml = strTempText
Set objTextstream = Nothing
Set objFilesytem = Nothing
Kill strFilename
End Function
Public Function fncConvertPictureToMail（strTempText As String，objWorksheet As Worksheet）As String
$ b $ Const Const HTM_START =< link rel = File -List href =
Const HTM_END =/filelist.xml
Dim strTemp As String
Dim lngPathLeft As Long
lngPathLeft = InStr（ 1，strTempText，HTM_START）
strTemp = Mid $（strTemp，lngPathLeft，InStr（lngPathLeft，strTempText，>） - lngPathLeft）
strTemp = Replace（strTemp，HTM_START& Chr $（34），）
strTemp = Replace（strTemp，HTM_END& Chr $（34），）
strTemp = strTemp& /
strTempText =替换（strTempText，strTemp，Environ $（temp）&\& strTemp）
fncConvertPictureToMail = strTempText
End Function
100％肯定，但这似乎是你的rng html功能的原因。
我有一个类似的问题，因为我使用的HTML的范围较大，所以我的解决方案是一种解决方法，通过重写您正在使用的函数。
不能评论代表缺乏代表，所以请把它与一粒盐。
我建议使用这个range_to_html函数，并打开工作簿扩大它/激活您的工作簿/工作表的呼叫：
功能RangetoHTML（rng作为范围）'将范围转换为HTML用于email-sendout '
'在Excel 2000，Excel 2002，Excel 2003，Excel 2007，Excel 2010，Outlook 2000，Outlook 2002，Outlook 2003，Outlook 2007和Outlook 2010中可用。
Dim FSO As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ $（temp）& /&格式（现在，dd-mm-yy h-mm-ss）& .htm
'复制范围并创建工作簿以接收数据。
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该表格转换为.htm文件。
With TempWB.PublishObjects.Add（_
SourceType：= xlSourceRange，_
filename：= TempFile，_
sheet：= TempWB.Sheets（1）.Name，_
来源：= TempWB.Sheets（1）.UsedRange.Address，_
HtmlType：= xlHtmlStatic）
.Publish（True）
End With
'将.htm文件中的所有数据读入RangetoHTML子例程。
Set FSO = CreateObject（Scripting.FileSystemObject）
Set ts = FSO.GetFile（TempFile）.OpenAsTextStream（1，-2）
RangetoHTML = ts.ReadAll
ts 。Closeose
RangetoHTML = Replace（RangetoHTML，align = center x：publishsource =，_
align = left x：publishsource =）
'Close TempWB。
TempWB.Close savechanges：= False
'删除htm文件。
Kill TempFile
Set ts = Nothing
Set FSO = Nothing
Set TempWB = Nothing
End Function
我已经将这个用于所有相关的范围，不管它选了什么，它总是符合一般的电子邮件格式。
来源： http：// www .mrexcel.com / forum / excel-questions / 485720-ron-de-bruin-rangetohtml.html
由于您直接从文件中读取html可能包含代码的allign命令。这里有一个复制，并重新发布哪些应该绕过这个问题。
我也不确定这段代码如何处理形状，所以它可能不适用于具有形状的范围。
另外如果我误解了，问题是单元格中的文本左对齐lemme知道，这将更容易识别和修复。
I'm having trouble getting a range that is embedded in an email to left align. I've tried several things but the embedded portion still centers in the email. Here is my code, which ironically, works just fine in other spreadsheets. I've tried adding HTML tags, changing the function(s), all to no avail. Any help would be appreciated. This is on W7x64 and Office 2010. In this report I am embedding a pivot table instead of a regular range.
Thanks.
Option Explicit
SalesSub Mail_RegionalRANGE()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
'On Error Resume Next
Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.SentOnBehalfOfName = "SalesAnalytics@company.us"
.Display
.Subject = "Sales Report"
.To = "mike.marshall@company.us"
'.CC =
'.BCC =
'.Attachments.Add "\\filesrv1\department shares\Sales Report\Sales Report.xlsx"
.HTMLBody = "<br>" _
& "Attached is the Sales Report. Please reach out to me with any questions." _
& "<br><br>" _
& "<p align=left>" & fncRangeToHtml("RegAEPctg", "B2:P67") & "<p>" _
& .HTMLBody
.Display
'.Send
End With
Set OutApp = Nothing
Set OutMail = Nothing
End Sub
Private Function fncRangeToHtml( _
strWorksheetName As String, _
strRangeAddress As String) As String
Dim objFilesytem As Object, objTextstream As Object, objShape As Shape
Dim strFilename As String, strTempText As String
Dim blnRangeContainsShapes As Boolean
Set objTextstream = Nothing
Set objFilesytem = Nothing
'Kill strFilename
strFilename = Environ$("temp") & "\" & _
Format(Now, "dd-mm-yy_h-mm-ss") & ".htm"
ThisWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=strFilename, _
Sheet:=strWorksheetName, _
Source:=strRangeAddress, _
HtmlType:=xlHtmlStatic).Publish True
Set objFilesytem = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFilesytem.GetFile(strFilename).OpenAsTextStream(1, -2)
strTempText = objTextstream.ReadAll
objTextstream.Close
strTempText = Replace(strTempText, "align=center x:publishsource=", "align=left x:publishsource=")
For Each objShape In Worksheets(strWorksheetName).Shapes
If Not Intersect(objShape.TopLeftCell, Worksheets( _
strWorksheetName).Range(strRangeAddress)) Is Nothing Then
blnRangeContainsShapes = True
Exit For
End If
Next
If blnRangeContainsShapes Then _
strTempText = fncConvertPictureToMail(strTempText, Worksheets(strWorksheetName))
fncRangeToHtml = strTempText
Set objTextstream = Nothing
Set objFilesytem = Nothing
Kill strFilename
End Function
Public Function fncConvertPictureToMail(strTempText As String, objWorksheet As Worksheet) As String
Const HTM_START = "<link rel=File-List href="
Const HTM_END = "/filelist.xml"
Dim strTemp As String
Dim lngPathLeft As Long
lngPathLeft = InStr(1, strTempText, HTM_START)
strTemp = Mid$(strTempText, lngPathLeft, InStr(lngPathLeft, strTempText, ">") - lngPathLeft)
strTemp = Replace(strTemp, HTM_START & Chr$(34), "")
strTemp = Replace(strTemp, HTM_END & Chr$(34), "")
strTemp = strTemp & "/"
strTempText = Replace(strTempText, strTemp, Environ$("temp") & "\" & strTemp)
fncConvertPictureToMail = strTempText
End Function
I'm not a 100% sure but this seems to be cause by your rng to html function. I had a similar problem with an older range to html I was using, so my solution is a kind of workaround, by rewriting the function you are using.
Can't comment for lack of rep, so please take it with a grain of salt.
I'd suggest using this range_to_html function and expanding it with a workbook open/activate call for your workbook/sheet:
Function RangetoHTML(rng As Range) ' converts a range into html for email-sendout'
' 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
I've used this for everything range related and no matter what it picked out it was always in line with the general email format. Source: http://www.mrexcel.com/forum/excel-questions/485720-ron-de-bruin-rangetohtml.html
Since you are reading directly from the file the html might contain the allign command for the code. This one here does copying, and republishing which should circumvent the issue. I'm also not sure how this code handles shapes, so it might not work for ranges with shapes.
Also if I misunderstood and the problem is that the text in the cells is left aligned lemme know, that would be easier to identify and fix.
这篇关于在电子邮件中左对齐嵌入的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
