How to Wrap text

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
Hi good morning, please can you help me I have the code below where I am copying date from a sheet into an email but I want it to wrap text as all information isn't shown when copied over. Please can you help?

Code:
Private Sub CommandButton8_Click()
Range("F8").Value = Range("F8").Value + 1
Worksheets("Handover").Range("H5:P5").Copy Worksheets("Chase").Range("A1")
Call Mail_Selection_Range_Outlook_Body
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
post also the code for transferring data to email.
What you've posted only copies something within excel.
Are you sure that WRAP-ing is the problem. I would guess you are not actually copying all the information you need.
 
Upvote 0
hi this is the other code below which is a module, it only copies over what you see on screen, hope you can help please
Code:
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("Chase").Range("A1:I1").SpecialCells(xlCellTypeVisible)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


With OutMail
    .To = ThisWorkbook.Sheets("Email Links").Range("A2").Value
    .CC = ""
    .BCC = ""
    .Subject = "Chaser please on this job as the MT has called"
    .htmlBody = RangetoHTML(rng)
    .Display
End With
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Upvote 0
I hope you can help me with this please? thank you for your time
 
Upvote 0
What t is some cells have a lot of information typed in to them and this doesn't copy all over into the email, would that be a wrap test? or some kind of formatting? hope you can help?
 
Upvote 0
So far it all looks OK, but there is more.
Now it is time to post the code for the function doing the job: RangetoHTML().
and maybe provide some sample data and somehow show what's missing.
Maybe a copy of HTML body with missing data.
 
Upvote 0
Hi, good morning. this is the RangetoHTML code. I was wondering if I do need t wrap text? or is there something else I can add in the code to make sure it copies and pastes everything across in the cells?
Code:
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"

    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

    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

    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=")

    TempWB.Close savechanges:=False

    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Upvote 0
The function turns out to work very well actually. The temp file and the result string RangetoHTML are identical (barring the alignment change).

Unfortunately at the moment I cannot really try how things are transferred to Outlook on any of my computers.
And I am still not clear what is missing from the e-mail body. Can you give some example? For me even wrapped lines looked ok in the HTML.

Try to set Body format to HMTL:
Code:
 [B].BodyFormat = olFormatHTML
[/B] .htmlBody = RangetoHTML(rng)
see if this makes any difference
Also keep the TEMP HTML file (comment the KILL line) and open it in a browser to see if it looks OK.
 
Upvote 0
Hi, Thank you I shall try that, your from Bulgaria? that is so nice I live in UK and I have a Bungalow in Bulgaria as well in Burgas, I love it there :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top