Remove Space After String

J

Jaredbuoy

New Member
Joined
Oct 25, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Please can you anyone kindly assist, I am looking to remove the last space after my string and just before my signaure.

VBA Code: 
Sub Order_Follow()

Dim EApp As Object, EItem As Object, signature As Variant
Set EApp = CreateObject("Outlook.Application")

Set EItem = EApp.CreateItem(0)

Dim RList As Range
Set RList = Range("A2:A" & cells(Rows.Count, "A").End(xlUp).Row)
Dim R As Range

For Each R In RList
    Set EItem = EApp.CreateItem(0)
        With EItem
        .Display
    End With
    signature = EItem.HTMLBody
    With EItem
        .to = R.Offset(0, 9)
        .SentOnBehalfOfName = "[EMAIL]john@doe.com[/EMAIL]"
        .Subject = "Order - " & R.Offset(0, 0)
        .HTMLBody = "<BODY>Hi " & Split(R.Offset(0, 8), " ")(0) & "<br> <br>Hope all is well," & "<br> <br>I see you have an order - " & R.Offset(0, 0) & " Are you still interested in it, or can we kindly close the order? </BODY>" & signature
        .Display
      
    End With
Next R

Set EApp = Nothing
Set EItem = Nothing

End Sub

Kindly ignore the dummy text but I get this annoying double space after order and above my signature "kind regards". I don't want to remove the kind regards as I use it for general email enquiries.


1669209643738.png


Really appreciate all feedback.
 
Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this code :

VBA Code: 
Sub Order_Follow()
Dim EApp As Object, EItem As Object, signature As String
Set EApp = CreateObject("Outlook.Application")
Set EItem = EApp.CreateItem(0)
Dim RList As Range
Set RList = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Dim R As Range
For Each R In RList
Set EItem = EApp.CreateItem(0)
With EItem
.Display
End With
signature = Replace(EItem.HTMLBody, "<p ><o:p>&nbsp;</o:p></p>", "")
With EItem
.to = R.Offset(0, 9)
.SentOnBehalfOfName = "john@doe.com"
.Subject = "Order - " & R.Offset(0, 0)
.HTMLBody = "<BODY>Hi " & Split(R.Offset(0, 8), " ")(0) & "<br> <br>Hope all is well," & "<br> <br>I see you have an order - " & R.Offset(0, 0) & " Are you still interested in it, or can we kindly close the order? </BODY>" & signature
.Display
End With
Next R
Set EApp = Nothing
Set EItem = Nothing
End Sub
 
Upvote 0
Flaiban said:
Try this code :

VBA Code: 
Sub Order_Follow()
Dim EApp As Object, EItem As Object, signature As String
Set EApp = CreateObject("Outlook.Application")
Set EItem = EApp.CreateItem(0)
Dim RList As Range
Set RList = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Dim R As Range
For Each R In RList
Set EItem = EApp.CreateItem(0)
With EItem
.Display
End With
signature = Replace(EItem.HTMLBody, "<p ><o:p>&nbsp;</o:p></p>", "")
With EItem
.to = R.Offset(0, 9)
.SentOnBehalfOfName = "john@doe.com"
.Subject = "Order - " & R.Offset(0, 0)
.HTMLBody = "<BODY>Hi " & Split(R.Offset(0, 8), " ")(0) & "<br> <br>Hope all is well," & "<br> <br>I see you have an order - " & R.Offset(0, 0) & " Are you still interested in it, or can we kindly close the order? </BODY>" & signature
.Display
End With
Next R
Set EApp = Nothing
Set EItem = Nothing
End Sub
Click to expand...
I tried the above and it didnt work, I also did this -
Code: 
signature = Replace(EItem.HTMLBody, "<o:p>&nbsp;</o:p>", "")
and it completely removed the breaks. There is no middle haha.
 
Upvote 0
Jaredbuoy said:
I tried the above and it didnt work, I also did this -
Code: 
signature = Replace(EItem.HTMLBody, "<o:p>&nbsp;</o:p>", "")
and it completely removed the breaks. There is no middle haha.
Click to expand...
Let's try with function :
VBA Code: 
Function RemoveBlankStuff(ByVal text as string) as string
    text = text.Replace("<P></P>","") 'Remove any empty paragraphs
    text = text.Replace("<BR>","")    'Remove any line breaks
    Return text;
End Function

signature = RemoveBlankStuff(EItem.HTMLBody)
 
Upvote 0
Flaiban said:
Let's try with function :
VBA Code: 
Function RemoveBlankStuff(ByVal text as string) as string
    text = text.Replace("<P></P>","") 'Remove any empty paragraphs
    text = text.Replace("<BR>","")    'Remove any line breaks
    Return text;
End Function

signature = RemoveBlankStuff(EItem.HTMLBody)
Click to expand...
Sorry man, cant wrap my head about this -

1669239122764.png
 
Upvote 0
Flaiban said:
Let's try with function :
VBA Code: 
Function RemoveBlankStuff(ByVal text as string) as string
    text = text.Replace("<P></P>","") 'Remove any empty paragraphs
    text = text.Replace("<BR>","")    'Remove any line breaks
    Return text;
End Function

signature = RemoveBlankStuff(EItem.HTMLBody)
Click to expand...
I tried the below and it solved the issue although not best practice -

Code: 
Sub Order_Follow()
Dim EApp As Object, EItem As Object, signature As String
Set EApp = CreateObject("Outlook.Application")
Set EItem = EApp.CreateItem(0)
Dim RList As Range
Set RList = Range("A2:A" & cells(Rows.Count, "A").End(xlUp).Row)
Dim R As Range
For Each R In RList
Set EItem = EApp.CreateItem(0)
With EItem
.Display
End With
signature = Replace(EItem.HTMLBody, "<p class=MsoNormal><o:p>&nbsp;</o:p></p>", "")
With EItem
.to = R.Offset(0, 9)
.SentOnBehalfOfName = "john@doe.com"
.Subject = "Order - " & R.Offset(0, 0)
.HTMLBody = "<BODY>Hi " & Split(R.Offset(0, 8), " ")(0) & "<br> <br>Hope all is well," & "<br> <br>I see you have an order - " & R.Offset(0, 0) & " Are you still interested in it, or can we kindly close the order?</BODY> <br>" & signature
.Display
End With
Next R
Set EApp = Nothing
Set EItem = Nothing
End Sub


I think you did send the class with, but it never copied when you added the code, also didn't add for me when I added it to mrexcel - I added a <br> after the </body> which created an additional space after removing the space with the replace function
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

R
  • Question
Multiple recipients with individualized attachments
Replies
1
Views
295
mumps
mumps
F
  • Question
VBA Email formatting
Replies
4
Views
488
floggingmolly
F
F
  • Question
VBA to create emails creating blank emails if only 1 row of data
Replies
1
Views
322
Micron
M
V
  • Question
VBA help on each loop if only 1 row it only show 1 if many show many email
Replies
1
Views
316
VBAEXCELNew
V
V
  • Question
Send email together based on organisation on my excel list
Replies
1
Views
322
Fluff
Fluff

Forum statistics

Threads
1,214,982
Messages
6,122,580
Members
449,089
Latest member
Motoracer88

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