如何在VBA中格式化Outlook.MeetingItem.Body文本适配JSON

I’ve dealt with this exact scenario before—getting Outlook meeting body text ready for JSON can be tricky because of unescaped quotes and inconsistent line endings. Let’s break this down into actionable steps:

Step 1: Detecting Line Endings in the Meeting Body #

Outlook might use CRLF (vbCrLf), LF (vbLf), or even CR (vbCr) as line endings depending on how the meeting was created. To check which one your MeetingItem uses, you can run this quick check:

Sub DetectLineEndings(meeting As Outlook.MeetingItem)
    Dim bodyText As String
    bodyText = meeting.Body
    
    If InStr(bodyText, vbCrLf) > 0 Then
        Debug.Print "Line endings are CRLF"
    ElseIf InStr(bodyText, vbLf) > 0 Then
        Debug.Print "Line endings are LF"
    ElseIf InStr(bodyText, vbCr) > 0 Then
        Debug.Print "Line endings are CR"
    Else
        Debug.Print "No line endings found"
    End If
End Sub

This will tell you exactly what line endings you’re working with, but our cleaning function will handle all cases automatically anyway.

Step 2: Cleaning the Text for JSON #

Your requirements are clear: replace double quotes with single quotes, and convert any line endings to \n (the JSON-friendly newline escape sequence). Here’s a robust function that does both:

Function CleanMeetingBodyForJSON(meeting As Outlook.MeetingItem) As String
    Dim cleanedText As String
    cleanedText = meeting.Body
    
    ' Replace double quotes with single quotes to avoid JSON syntax errors
    cleanedText = Replace(cleanedText, """", "'")
    
    ' Replace all line ending types with \n (handle CRLF first to avoid duplicates)
    cleanedText = Replace(cleanedText, vbCrLf, "\n")
    cleanedText = Replace(cleanedText, vbCr, "\n")
    cleanedText = Replace(cleanedText, vbLf, "\n")
    
    CleanMeetingBodyForJSON = cleanedText
End Function

We prioritize replacing CRLF first so we don’t end up with duplicate \n entries from splitting CRLF into two separate line endings.

Step 3: Using the Cleaned Text in JSON #

Once you have the cleaned string, you can safely insert it into your JSON structure. Here’s an example of how to build a valid JSON string:

Sub CreateJSONFromMeeting(meeting As Outlook.MeetingItem)
    Dim cleanedBody As String
    cleanedBody = CleanMeetingBodyForJSON(meeting)
    
    ' Construct the JSON string
    Dim jsonString As String
    jsonString = "{""meetingBody"": """ & cleanedBody & """}"
    
    ' Use the JSON string as needed (save to file, send via API, etc.)
    Debug.Print jsonString
End Sub

This will output a valid JSON string where all quotes are single quotes and newlines are properly escaped as \n.

Quick Notes #

• If you ever need to keep double quotes but escape them for JSON (instead of replacing with single quotes), swap the quote replacement line with cleanedText = Replace(cleanedText, """", "\""")—but your requirement specifies replacing with single quotes, so the above code follows that.
• The MeetingItem.Body property returns plain text by default. If you need to work with HTML-formatted content, use MeetingItem.HTMLBody instead, but that will require extra parsing to strip HTML tags first.

内容的提问来源于stack exchange，提问作者khashashin