#1
|
|||
|
|||
How to copy userform text and formfield contents to outlook?
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 |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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?
|
#4
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Graham,
Thank you for the help. That works perfectly for what I need!!!! |
#6
|
|||
|
|||
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? |
#7
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
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 |