Selecting Text with VBA?

[User#13347]

Hey Everyone,

Over the past couple of weeks I have been making macro enabled templates in Word 2016. In my Templates are a series of Legacy Form Drop Downs that populate based on the criteria you enter. The Drop Down works fine, The form works fine. When I copy and paste my brain child into a new message in outlook 2016 all appears to paste well. Once you send it any text contained in a drop down field is stripped from the message. This is what I have tried....

*Changing the font/style of the text (trying o get outlook to recognize the characters)
*Clearing the formatting after pasting
* Pasting keeping text only ( which will copy text from the drop down but remove my tables)
*Highlighting etc...

I've tried doing those in Outlook and word

Now I'm wondering if there are tools in VBA I could use to create a macro that could find and replace the dropdownfields with the text the represent respectively. Does that make sense? I'm going to post a few screenshots along with uploading my templates. If I remember correctly I'll have to zip them. I am very open to suggestions considering I am brand new to VBA. I'll continue to research this on my own but all input is appreciated! If I need to clarify anything feel free to ask!

Kindly,
~B

TL;DR: When I paste my template from word to outlook it looks fine but after I send, anything in a legacy dropdown is stripped... I want that to not happen.

[User#13347]

I should mention. I'd like to see if the problem could be fixed in word instead of Outlook... you know so I wouldn't have to patch every end user

[gmayor]

I think this is one of those occasions to say that if I was going where you intend, I wouldn't start from here.

Mixing legacy form fields with content controls is never a very good idea and as you want text output to your e-mail message (which I assume is the reasoning behind the form) then, I would create a VBA userform - see http://www.gmayor.com/Userform.htm for the basics and you will need http://www.gmayor.com/Userform_ComboBox.html for the comboboxes (for your dropdown lists). By creating the fields you want in the userform, it becomes a much simpler prospect to write the values selected of entered into the form to bookmarked locations in the template, to document variables, directly to table cell ranges to an e-mail message body or wherever you require them.

[User#13347]

Most importantly, Thanks for taking time to reply gmayor.

Before I get ecstatic......I used "Case" & "select" previously to make my drop downs populate based off of previously selected information... Could I adapt that to the methods from your links? Also.... can I use a userform and combo box in conjunction with an email template? Maybe I miss interpreted your post... Am I in the right direction?

Holding my breath,
~B


P.S. I tried leaving a link but apparently I can't because I haven't posted enough. If you wanna see some of the code I used for dependencies to see where my mind was at you can check the first thread post in my profile....if that's even something you would want/need to do.
Again thanks For helping out

[User#13347]

Wow! Your website was full of resources. After some skimming and reading the 15 minute vba crash course I did in fact make a magic cleanup button with a Macro called Complete!

I'll be going through your website with a fine tooth comb as it seems to be a phenomenal resource! I see you have recommended Greg Maxey who helped me last time I got a little stuck!

Either way My project is saved and it's because you helped me get my brain organized! You'd do wonders in therapy lol

Cheers!
~Brandon


In case you are curious here is my slop of code that I used to convert the drop downs into plain text and then clean up my form. Any ideas on how I could be more efficient in my code or structure are welcome but again thanks for everything! I'll even leave a zip file of my final project for anyone else that may lurk across this thread....



Sub ToggleOFF()
'Unprotect the file
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="a"
End If
End Sub

Sub CleanUp()
'
' CleanUp Macro is for turning dropdowns into plain text
'
'
On Error GoTo lbl_Exit
ActiveDocument.FormFields("ddCatalog").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddProfile").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddDocumenttype").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddDocumenttype2").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddFacilitytype").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddFacilitytype2").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)
ActiveDocument.FormFields("ddInspectiontype").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)

ActiveDocument.FormFields("ddInspectiontype2").Select
Selection.Cut
Selection.PasteAndFormat (wdFormatPlainText)

lbl_Exit:
On Error GoTo 0

Exit Sub
End Sub



Sub PleaseClean()
'
' PleaseClean Macro cleans up the instruction line containing the MacroButton
'
'
Selection.MoveUp Unit:=wdLine, Count:=23
Selection.MoveLeft Unit:=wdCharacter, Count:=62
Selection.MoveUp Unit:=wdLine, Count:=14
Selection.MoveLeft Unit:=wdCharacter, Count:=6
Selection.MoveUp Unit:=wdLine, Count:=6
Selection.MoveLeft Unit:=wdCharacter, Count:=4
Selection.MoveDown Unit:=wdLine, Count:=12
Selection.MoveRight Unit:=wdCharacter, Count:=21
Selection.MoveRight Unit:=wdCharacter, Count:=44, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
End Sub



Sub Complete()
Call ToggleOFF
Call CleanUp
Call PleaseClean

End Sub

[gmayor]

Thanks for the kind comments about my web site. Greg has been a friend for many years and his site is recommended viewing. In particular take a look at his basic VBA primer.

I still wouldn't do the tempate this way, but your macros can certainly be improved, by avoiding all that shuffling around the document moviing Selections which is very inefficient, and by looping through the form fields. I have added a macro to deal with the content controls also e.g.

Option Explicit

Sub Complete()
    Call ToggleOFF
    Call CleanUp
    Call PleaseClean
    'Call FixCC
lbl_Exit:
    Exit Sub
End Sub

Sub CleanUp()
Dim oFF As FormField
Dim orng As Range
    For Each oFF In ActiveDocument.FormFields
        Select Case oFF.Type
            Case Is = wdFieldFormCheckBox
                If oFF.CheckBox.Value = True Then
                    oFF.Range.Text = "True"
                Else
                    oFF.Range.Text = "False"
                End If
            Case Is = wdFieldFormDropDown
                If Not oFF.DropDown.Value = 0 Then
                    oFF.Range.Text = oFF.Result
                Else
                    oFF.Range.Text = ""
                End If
            Case Else
                oFF.Range.Text = oFF.Result
        End Select
    Next oFF
lbl_Exit:
    Set oFF = Nothing
    Exit Sub
err_Handler:
    MsgBox Err.Number & vbCr & Err.Description
    Err.Clear
    GoTo lbl_Exit
End Sub

Sub ToggleOFF()
Const strPassword As String = "a"
'Unprotect the file
    If ActiveDocument.ProtectionType <> wdNoProtection Then
        ActiveDocument.Unprotect Password:=strPassword
    End If
lbl_Exit:
    Exit Sub
    End Sub

Sub PleaseClean()
Dim orng As Range
    Set orng = ActiveDocument.Range
    With orng.Find
        Do While .Execute(FindText:="Oculus Info:")
            orng.End = orng.Paragraphs(1).Range.End - 1
            orng.MoveStartUntil Chr(58)    ':
            orng.Start = orng.Start + 1
            orng.Delete
            Exit Do
        Loop
    End With
lbl_Exit:
    Exit Sub
End Sub

Sub FixCC()
Dim oCC As ContentControl
Dim orng As Range
Dim strCC As String
    For Each oCC In ActiveDocument.ContentControls
        If Not oCC.Range.Text = oCC.PlaceholderText Then
            strCC = oCC.Range.Text
        Else
            strCC = ""
        End If
        Set orng = oCC.Range
        oCC.Delete
        orng.Text = strCC
    Next oCC
lbl_Exit:
    Set oCC = Nothing
    Exit Sub
End Sub