Search code examples
vbaoutlookcontentcontrolline-spacing

Convert Content Control value to String using VBA

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?

Solution

  • 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