How to copy userform text and formfield contents to outlook?

odin · #1 09-04-2014, 07:01 AM

I have a userform that has text and formfields mixed in. I would like to copy the text and the contents of the legacy formfields into an Outlook email message, while keeping the text formatting (bold, italic, underline, etc..). I did some research and found code that almost does what I need but it's pasting the formfield as a formfield and not as text.

Any help on how to modify the code to do this would be greatly appreciated.

Private Sub EmailData()
Dim OutApp As Object
Dim OutMail As Object
Dim OutInsp As Outlook.Inspector
Dim WdApp As Word.Application
Dim OutDoc As Word.Document
Dim WdSel As Word.Selection

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "EmailAddressHere"
.Subject = "SubjectHere"
.Display
End With

Set OutInsp = OutMail.GetInspector
Set OutDoc = OutInsp.WordEditor
Set WdApp = OutDoc.Application
Set WdSel = WdApp.Selection

ActiveDocument.Content.Copy
WdSel.PasteAndFormat Type:=wdFormatOriginalFormatting

Set WdSel = Nothing
Set OutInsp = Nothing

Set OutMail = Nothing
Set OutDoc = Nothing
Set WdApp = Nothing
Set OutApp = Nothing
End Sub

macropod · #2 09-04-2014, 03:13 PM

To do that, you'd need to unprotect the document and convert its formfields to their results (e.g. by ActiveDocument.Fields.Unlink) before copying. Even that will only work with textboxes and dropdowns: checkbox formfields have no text equivalent and you'd need to replace them on a case-by-case basis with Wingding equivalents.

PS: Your reference to a userform is misleading. Your document is a Word form, but there's no indication it contains a userform, which is a special object created in the VBA environment itself. If your document had one, your current code wouldn't copy it.

odin · #3 09-05-2014, 06:52 AM

Sorry about the subject being misleading. I'll give that a try as soon as I can. I don't have any check boxes on this particular word form. But if I do on another could I have the macro do a search for the check boxes and replace with them with the correct close equivalent of wingding based on the true or false result of the box being checked or unchecked?

gmayor · #4 09-05-2014, 07:21 AM

You won't have to unprotect the form if you process the copied data in the message as it will already be unprotected there e.g. as follows.
You can change the wingding characters to those you prefer.

Code:

Option Explicit

Private Sub EmailData()
Dim OutApp As Object
Dim OutMail As Object
Dim OutInsp As Object
Dim OutDoc As Word.Document
Dim oFld As Word.FormField
Dim oRng As Word.Range

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "EmailAddressHere"
        .Subject = "SubjectHere"
        .Display
    End With

    Set OutInsp = OutMail.GetInspector
    Set OutDoc = OutInsp.WordEditor

    ActiveDocument.Content.Copy
    OutDoc.Range.PasteAndFormat Type:=wdFormatOriginalFormatting
    For Each oFld In OutDoc.Range.FormFields
        Set oRng = oFld.Range
        If oFld.Type = wdFieldFormCheckBox Then
            If oFld.CheckBox.Value = True Then
                oRng.InsertSymbol _
                        Font:="Wingdings", _
                        CharacterNumber:=-3842, _
                        Unicode:=True
            Else
                oRng.InsertSymbol _
                        Font:="Wingdings", _
                        CharacterNumber:=-3985, _
                        Unicode:=True
            End If
        Else
            oRng.Text = oFld.Result
        End If
    Next oFld
    Set oFld = Nothing
    Set oRng = Nothing
    Set OutInsp = Nothing
    Set OutMail = Nothing
    Set OutDoc = Nothing
    Set OutApp = Nothing
End Sub

odin · #5 09-08-2014, 04:22 AM

Graham,

Thank you for the help. That works perfectly for what I need!!!!

odin · #6 09-08-2014, 10:14 AM

Graham,

It looks like I may have spoken a little too soon. The code work fine initialy but the next I went to run it, it didn't keep my signature in the email message. Do you know of a way to make sure that if there is a signature it keeps it in the message and the form contents gets pasted above it?

gmayor · #7 09-09-2014, 04:37 AM

If you want to retain the signature, you need to set a range to the start of the message body and paste into that range e.g.

Code:

Option Explicit


Private Sub EmailData()
Dim OutApp As Object
Dim OutMail As Object
Dim OutInsp As Object
Dim OutDoc As Word.Document
Dim oFld As Word.FormField
Dim oRng As Word.Range
Dim oStart As Range

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "EmailAddressHere"
        .Subject = "SubjectHere"
        .Display
    End With

    Set OutInsp = OutMail.GetInspector
    Set OutDoc = OutInsp.WordEditor

    ActiveDocument.Content.Copy
    Set oStart = OutDoc.Range(Start:=0, End:=0)
    oStart.PasteAndFormat Type:=wdFormatOriginalFormatting
    For Each oFld In OutDoc.Range.FormFields
        Set oRng = oFld.Range
        If oFld.Type = wdFieldFormCheckBox Then
            If oFld.CheckBox.Value = True Then
                oRng.InsertSymbol _
                        Font:="Wingdings", _
                        CharacterNumber:=-3842, _
                        Unicode:=True
            Else
                oRng.InsertSymbol _
                        Font:="Wingdings", _
                        CharacterNumber:=-3985, _
                        Unicode:=True
            End If
        Else
            oRng.Text = oFld.Result
        End If
    Next oFld
    Set oFld = Nothing
    Set oRng = Nothing
    Set oStart = Nothing
    Set OutInsp = Nothing
    Set OutMail = Nothing
    Set OutDoc = Nothing
    Set OutApp = Nothing
End Sub

odin · #8 09-09-2014, 11:56 AM

THANK YOU!!! That seems to have fixed it! I appreciate all of your help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formfield calculation	Joachim	Word	4	03-08-2013 01:56 AM
Copy all comments & cell contents (i.e. data) to word?	IanM	Excel	0	07-03-2010 11:14 PM
Copy the contents of a dcoument and paste it several times in a new document	Gerjanst	Word VBA	0	06-30-2010 12:51 PM
Checkbox on Userform result in Text in Word	Dolfie_twee	Word VBA	1	06-22-2010 07:54 AM
Auto populate formfield from dropdown menu	jmofstead	Word	0	02-26-2010 10:35 AM