Left Align embedded range in Email

Question

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

Answer 1

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.