how to wrap text

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,231
Office Version
  1. 2010
Platform
  1. Windows
hi all,good morning. I hope you can help me please? I have the code, where it copies from spreadsheet and pastes into an email, but I would like to have a wrap text for when its transferred into the email, can you help please?


HTML: 
 With Sheets("Handover")
        Set rngDataToEmail = .Range("H4:R" & .Range("H" & Rows.Count).End(xlUp).Row)
    End With

this is the whole code

HTML: 
Private Sub CommandButton4_Click()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String
Dim rngDataToEmail As Range

    Set aOutlook = CreateObject("Outlook.Application")
    Set aEmail = aOutlook.CreateItem(0)
    With Sheets("Handover")
        Set rngDataToEmail = .Range("H4:R" & .Range("H" & Rows.Count).End(xlUp).Row)
    End With
    
    aEmail.HTMLBody = "<html>" & _
                    "<p>Hi Paul/Jayne, good afternoon, <p>In day today there was an additional " & Worksheets("Handover").Range("F6").Value & " jobs raised in the system by myself to try and fill the guys up- escalations/sites brought forward. (This does not include the raising of duplicate jobs where work was raised incorrectly or needed to be re-raised due to technical fault)." & Worksheets("Handover").Range("F8").Value & "</p>" & _
                    "<p><u><b>" & Me.TextBox4.Value & "</p></u></b>" & _
                    "<table border=""1"", cellpadding=""18"", style=background:""#a6bbde"" >" & _
                "<tr>" & _
                    "<th>NCMO Issue:</th>" & "<td>" & Worksheets("Handover").Range("C5").Value & "</td>" & "<th>Re-Raised:</th>" & "<td>" & Worksheets("Handover").Range("F5").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>SNR:</th>" & "<td>" & Worksheets("Handover").Range("C6").Value & "</td>" & "<th>Additional Jobs:</th>" & "<td>" & Worksheets("Handover").Range("F6").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>Replans:</th>" & _
                    "<td>" & Worksheets("Handover").Range("C7").Value & "</td>" & "<th>Rebinds:</th>" & "<td>" & Worksheets("Handover").Range("F7").Value & "</td>" & "<th>Outages:</th>" & "<td>" & Worksheets("Handover").Range("c8").Value & "</td>" & _
                    "</tr>" & _
                    "</table>" & _
                "" & _
                "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) & _
                "<p>Many Thanks</p>" & _
                "<p>New Connections Team</p>" & _
                "</body></html>"
    
    
    aEmail.Recipients.Add (Worksheets("Email Links").Range("B2").Value)
    aEmail.CC = (Worksheets("Email Links").Range("C2").Value)
    aEmail.BCC = ""
    aEmail.Subject = "" & Range("C1").Value & " " & Range("K1").Value
    aEmail.Display
    
       
End Sub
 
Sort by date Sort by votes
hi I have now changed it the following but not working.
HTML: 
aEmail.HTMLBody = WrapText
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi I have looked at the links thanks but I still can't work it out. Can you help please with more advise? I am still new to this and still learning. Thanks for your time
 
Upvote 0
Hi, I have changed the code to the below for wrap text, it works sort off but it doesn't wrap what I copy over, it wraps the empty cells below what has been pasted. can you help please?

Code: 
Private Sub CommandButton2_Click()
With Selection

    .wrapText = True
End With
Sheets("Handover").Range("H4:R" & Sheets("Handover").Range("H:R").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy Sheets("Issues").Range("A1")
    
End Function
 
Upvote 0
Hi just checked again this does work, but it does a wrap text on the "Handover" sheet but I don't want that, I want it to copy from handover into issues sheet then do the wrap text on the info in this Issues sheet. please can you advise?
 
Upvote 0
hi have also tried the code below with wraptext in the issues sheet but get an error now. please can you help
Code: 
Private Sub CommandButton2_Click()

Sheets("Handover").Range("H4:R" & Sheets("Handover").Range("H:R").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy Sheets("Issues").Range("A1").wrapText = True

 
End Sub
 
Last edited:
Upvote 0
hi I have amended the code now, it now pastes into the 'issues' sheet but it doesn't wraptext in the issues sheet. please help
Code: 
Private Sub CommandButton2_Click()


Sheets("Handover").Range("H4:R" & Sheets("Handover").Range("H:R").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy Sheets("Issues").Range("A1")
Sheets("Issues").Range("A1").wrapText = True
 
End Sub
 
Upvote 0
Hi can anyone help me with this please? I am trying to protect with what is copied over to the issues sheet. Really hope you can help me.
 
Upvote 0
Hi,

Just tested you macro at my end ... and it does wrap text as expected ...

If your destination sheet is protected ... you do have to unprotect it before running your macro ... and then re-protect it ...

Hope this will help
 
Upvote 0
Hi the sheet is unprotected. The issue sheet does wraptext but in the empty sell below the data I have copied over. It didn't wraptext the information pasted
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Email error on border code
Replies
5
Views
195
Patriot2879
P
P
  • Solved
Copy and cut from sheet 'Raised'
Replies
7
Views
237
Patriot2879
P
P
  • Solved
Compile error: method or data member not found
Replies
6
Views
268
Patriot2879
P
P
  • Question
Email Me. to label and text on two lines issues
Replies
1
Views
175
Micron
M
E
  • Question
My Table created by VBA
2 3
Replies
24
Views
751
Candyman8019
C

Forum statistics

Threads
1,215,465
Messages
6,124,982
Members
449,201
Latest member
Lunzwe73

Share this page

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