Convert Content Control value to String using VBA

Question

I am developing a script to format an Outlook template and send it by clicking on a button. The main goal is to use Single Line Spacing and 0pt on After/Before Spacing, and to convert all the Content Controls (such as ComboBoxes) on my template to Text/String.

Sub FormatAndSend()
    Dim CurrentMessage As Outlook.MailItem

    If TypeName(Application.ActiveWindow) = "Inspector" Then
        Set CurrentMessage = Application.ActiveWindow.CurrentItem

        If CurrentMessage.To = "" Then
            If CurrentMessage.CC = "" Then
                If CurrentMessage.BCC = "" Then Exit Sub
            End If
        End If

        CurrentMessage.HTMLBody = Replace(CurrentMessage.HTMLBody, vbCr, vbCrLf)
        'Workaround to Remove Line Spacing (not working)
        '&
        'Convert Content Control selected values to String ...

        CurrentMessage.Send

        Set CurrentMessage = Nothing
    End If
End Sub

Is there an easier way to format the existing template? I have tried already several things, such as replacing with ASCII chars, HTML elements, keywords, ...

Also, is it possible to convert Content Control selected values to normal text?

Ok, so how are you planning to "convert" a combobox "to text"? Exactly what do you mean by that, and what code have you written that's doing that, and specifically where & how is it not working as it should? — Mathieu Guindon, Oct 10 '17 at 20:09
I mean, convert the combobox selected value, so when someone forward/reply the message, its contents cannot be changed — mafap, Oct 10 '17 at 20:21
I'm not familiar with the Outlook object model, but a quick glance at the `MailItem` class shows that there doesn't appear to be any `Controls` collection, so parsing the `HtmlBody` seems like your best bet. — Mathieu Guindon, Oct 10 '17 at 20:27

mafap · Accepted Answer · 2017-10-13T03:25:25.043

I managed to get my problem solved by using regex to remove the OOXML elements and by changing the CSS properties.

Dim regX As Object
Set regX = CreateObject("VBScript.RegExp")

regX.Global = True
regX.MultiLine = True
regX.IgnoreCase = False
regX.Pattern = "<\/?w:[A-z].*?>"

CurrentMessage.HTMLBody = regX.Replace(CurrentMessage.HTMLBody, "")
CurrentMessage.HTMLBody = Replace(CurrentMessage.HTMLBody, "<p class=MsoNormal>", _
                            & "<p class=MsoNormal style='margin:0;line-height:0;'>")
Set regX = Nothing

Convert Content Control value to String using VBA

1 Answers1