VBA split string and add to HTML body with formatting

wasntme

New Member
Joined
Feb 1, 2019
Messages
37
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):
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:

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

<tbody>
</tbody>

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

Regards,

<tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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):
Code:
strttext = Range("B2").Text
EndText = Replace(strttext, ";", "<br><br>")
 
Upvote 0
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):
Code:
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:
Rich (BB code):
        .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,
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
Thanks for the info. Peter. Now all I have to do is remember it!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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