Formatting text in vba

Question

How to fromat text in vba. I have to format Outlook.MeetingItem.Body to plain text. This object contains text with quotes and new lines, I wont to handle somehow this text.

For example write something like
sudocode:

if meetingitem contains quotes or newlines change quotes to single quotes and new lines to symbol(\n)

At the moment my MeetingItem.Body looks like (Example):

Hi every one
If you looking for "Dance", "Bus" or "Car"
go this way:

otherwise go:

I will replase newlines after "one", "Car", "way", "emptyline", "go" - with \n and double quotes with single quotes. And I don't know what my email using LF or CR LF(how to check that?).

Why? I will put this String after in to my JSON, maybe there is better way to do that but I've found only that. I can't put string with double quotes and new lines in to JSON.

Why? If an email has an Html body, the user will never see the text body. “change quotes to single quotes”. What sort of quotes do you want to change to single quotes? Do your emails use LF or CR LF for newline? Would `Replace(Outlook.MeetingItem.Body, vbCr & vbLf, "\n")` meet your requirement? You need to add a lot more information about your requirement before anyone can offer some detailed advice. — Tony Dallimore, May 16 '18 at 15:53

score 0 · Accepted Answer · answered May 16 '18 at 20:36

My answer to this question might help: How to copy Outlook mail message into excel using VBA or Macros. The main macro outputs selected properties of every email in Inbox to an Excel workbook so you can see what an email looks like to a VBA macro.

Even if I checked and found every email in my Inbox used CR LF as new line, I would not trust it. Some email packages will use LF and some will use CR LF as newline. Unless I found documentation that stated Outlook edited all text bodies so they had consistent newlines, I would not expect consistency. I would allow for newline being CR, LF or CR LF just to be safe.

Does this code do what you want:

Dim  JsonBody As String

JsonBody = Replace(MeetingItem.Body, """", "'")
JsonBody = Replace(JsonBody,vbCR & vbLF, "\n")
JsonBody = Replace(JsonBody,vbCR, "\n")
JsonBody = Replace(JsonBody,vbLF, "\n")

Formatting text in vba

1 Answers1