VBA split string and add to HTML body with formatting

[wasntme]

Hi all,

I am trying to separate string from a cell based on character present and add it after to HTML body.

Sample text (all in one cell):
[TABLE="width: 407"]
<tbody>[TR]
[TD="class: xl63, width: 407, align: left"]

Registration form - signed by the legal representative;
Signature specimen;
Power of Attorney - signed by the legal representative;
Trade Register Excerpt;
Certificate of Incorporation;
VAT Certificate;
Proof for taxable activity;
ID of the legal representatives

Need to separate it by ";" and add it to HTML body for better look, like this:

[TABLE="width: 407"]
<tbody>[TR]
[TD="align: left"]Registration form - signed by the legal representative[/TD]
[/TR]
[TR]
[TD="align: left"]Signature specimen[/TD]
[/TR]
[TR]
[TD="align: left"]Power of Attorney - signed by the legal representative[/TD]
[/TR]
[TR]
[TD="align: left"]Trade Register Excerpt[/TD]
[/TR]
[TR]
[TD="align: left"]Certificate of Incorporation[/TD]
[/TR]
[TR]
[TD="align: left"]VAT Certificate[/TD]
[/TR]
[TR]
[TD="align: left"]Proof for taxable activity[/TD]
[/TR]
[TR]
[TD="align: left"]ID of the legal representatives[/TD]
[/TR]
</tbody>[/TABLE]

Think this is somewhat common problem, but can not find a working solution. Any help greatly appreciated.

Regards,[/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

You say it will be going into an html format. In that case, this will do it for you (change B2 to wherever your text is):

strttext = Range("B2").Text
EndText = Replace(strttext, ";", "<br><br>")

 

[wasntme]

You say it will be going into an html format. In that case, this will do it for you (change B2 to wherever your text is):

strttext = Range("B2").Text
EndText = Replace(strttext, ";", "

")

Hi, i am not sure if i was clear enough or i just don't understand how to apply your solution.

Let me explain a bit the logical process:
1. forms = Application.Vlookup that returns cell with multiple items and lines separated by ";"
2. i want to add those items to a generated outlook message, but in HTML body, so the text can be formatted neatly

Code i am using for the email body is:

        .to = KAMemail
        .subject = client & ", " & ctry
        .HTMLbody = "" & "Hello " & KAM & ", 
" & _
        "
 To proceed with the registration of " & client & " in " & ctry & ", we will need the following documents: 
" & _
        "
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp - " & forms & ". " & " (please double check)
 " & _
        "
 Please request the needed information from client so we can continue with the registration process. 
" & _
        "
 Kind regards," & .HTMLbody

Number of items (separated by ";") differs in each string returned by vlookup.

Regards,

 

[jmacleary]

Sorry. I forgot that this forum can misinterpret < and > characters. To do what you want, replace the variable forms in your output with Replace(forms,";"," < br > < br > "). Remove the blanks around the < and > symbols to get what you need. I'm not surprised my answer looked weird. This will convert the single; into 2 html line breaks.

 

[wasntme]

Sorry. I forgot that this forum can misinterpret < and > characters. To do what you want, replace the variable forms in your output with Replace(forms,";"," < br > < br > "). Remove the blanks around the < and > symbols to get what you need. I'm not surprised my answer looked weird. This will convert the single; into 2 html line breaks.

Oh, thank you!

Have to play around my other code to sort the formatting, but i think this is exactly what i need!

Many thanks!

 

[Peter_SSs]

Sorry. I forgot that this forum can misinterpret < and > characters.

FYI: You can overcome that by in the Reply Window click Go Advanced, scroll down and there is an option to turn off HTML for your post.

 

[jmacleary]

Thanks for the info. Peter. Now all I have to do is remember it!