none
PasteSpecial copies only 8 characters to HTMLemailbody. RRS feed

  • Question

  • Hi, 
    I'm creating a VBA code for copying an excelsheet range into an html body, I used the coding example of mr. R. de Bruin. It works perfectly however, the code (pastespecial part) is pasting only 8 characaters per cell. (there are cells consisting more than 15 characters) 

    How can I solve this one? All other pastespecial methods don't work either. 

    Sub CreateFromTemplate2()
    
        Dim myOlApp As Outlook.Application
        Dim MyItem As Outlook.MailItem
        Dim strlocation As String
        Dim StrBody As String
        Dim oxl As Object
         
    strlocation = "C:\Users\...\Desktop\..\Daily transports " & Date & ".xlsx"
    
    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItemFromTemplate("C:\Users\..\..\ transporten.oft")
    
    
     Set oxl = CreateObject("Excel.Application")
                     'Dim vValue As Range
            Dim oBook As Workbook
            Dim oSheet As Worksheet
            Dim rng As Range
     
            Set oBook = oxl.Workbooks.Open(strlocation)
            Set oSheet = oBook.Worksheets("Sheet1")
    
            'Set vValue = oSheet.Range("A1:G3").Value   'Get the value from cell A1
    
    Set rng = Nothing
    ' Only send the visible cells in the selection.
    
    Set rng = oSheet.Range("A:F").SpecialCells(xlCellTypeVisible)
    
        MyItem.HTMLBody = RangetoHTML(rng)
     
        MyItem.Subject = ("Daily transports " & Date)
     
        'MyItem.Body = Replace(MyItem.Body, "%table%", vValue)
    
     'Set oSheet = Nothing
     'oBook.Close
     'Set oBook = Nothing
     'oxl.Quit
     'Set oxl = Nothing
    
    MyItem.Display
    
    End Sub
    
    Function RangetoHTML(rng As Range)
    
        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
                 
            On Error Resume Next
            .DrawingObjects.Visible = False
            .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


    Monday, October 20, 2014 12:18 PM

Answers

  • Hi SAPTG,

    Base on your code, I made a test, I found that the reason of that issue is in the html file (Publish).

    For the hidden data, it will has this code below:

    <span style=”display:none”> XXX<span>

    So, this content has been hidden, that’s why it seems just copy part data.

    To deal with this issue, I suggest that you could adjust the columns’ width of TempWB.

    With TempWB.Sheets(1)
                  
            .cells(1).PasteSpecial Paste:=8
                 
            On Error Resume Next
            .DrawingObjects.Visible = False
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
        'adjust the width
        TempWB.Sheets(1).Columns.AutoFit

    If you don’t want to adjust the column width, you could change the style.

    RangetoHTML = Replace(RangetoHTML, "style='display:none'", "style='display:block'")

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 23, 2014 3:22 AM
    Moderator

All replies

  • Hello,

    I see the following line of code above:

    .cells(1).PasteSpecial Paste:=8
    

    It looks like you need to change the value as shown above.

    Monday, October 20, 2014 1:25 PM
  • Hi SAPTG,

    Base on your code, the XlPasteType is xlPasteColumnWidth (value is 8), for this type, it just paste the column width.

    Please use other type and try again.

    # Range.PasteSpecial Method (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff839476(v=office.15).aspx

    If you still have the issue, please share the project on the OneDrive. (include excel file)

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 21, 2014 8:34 AM
    Moderator
  • Hi Starain, 

    I'm not sure how to share the project with you. I'm using the same code as stated above, which is the whole project. The Excel file is always 6 columns, but varies in number of rows. I'm not able to share it due to safety regulations within my company, but the content looks like this: 

    Column 1 = text
    Column 2 = Dateformat dd/mm/yyyy
    Column 3 = Text
    Column 4 = text
    Column 5 = text
    Column 6 = Text

    Can you help me? everything is working perfectly execpt for this small thing. 

    Thanks! 

    UPDATE: I've been strugling with the pastespecial methods, when I step into the macro the HTML file which is generated by the macro with the method pasteAll is not filled. Which explains why the mailbody stays empty. 

    UPDATE: Apparantly, the macro does not automatic kills the clipboard and tempfile. this results in the previous mentioned problems. How to fix this?

    • Edited by SAPTG Tuesday, October 21, 2014 11:57 AM Update due to more insights
    Tuesday, October 21, 2014 9:14 AM
  • Hi, 

    This number 8 is representing the pasting type of Columnwidhts (which was I was advised to use on another forum). 

    Tuesday, October 21, 2014 9:16 AM
  • Hi SAPTG,

    Base on your code, I made a test, I found that the reason of that issue is in the html file (Publish).

    For the hidden data, it will has this code below:

    <span style=”display:none”> XXX<span>

    So, this content has been hidden, that’s why it seems just copy part data.

    To deal with this issue, I suggest that you could adjust the columns’ width of TempWB.

    With TempWB.Sheets(1)
                  
            .cells(1).PasteSpecial Paste:=8
                 
            On Error Resume Next
            .DrawingObjects.Visible = False
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
        'adjust the width
        TempWB.Sheets(1).Columns.AutoFit

    If you don’t want to adjust the column width, you could change the style.

    RangetoHTML = Replace(RangetoHTML, "style='display:none'", "style='display:block'")

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 23, 2014 3:22 AM
    Moderator