VBA for Copy and Paste (with formatting) from Word 2010 to Outlook 2010

I have created a Word template that uses a userform - a date field, some list boxes, some text boxes, etc.  Then, when user is done, there are command buttons that call various vba subs - one is "InsertData" which does exactly what I want - which is to take the user input and place it in appropriate positions in a table (yes, I am intentionally using Word rather than Excel - truthfully I am taking a custom routine from Excel and trying to create it in Word, and it was much easier to set up in Excel!).  Then, I want to "EmailData" - where the entire text of the document is placed into the body of a new message window, while holding its table format, with To and Subject prefilled, but display only (user will need to select attachments before sending).  Then, the userform is unloaded, and the Word document will close without saving (or prompting to save).

Everything works great, except that the formatting is not retained.  I've tried various paste methods and range selection methods, and I can't find one that works.  (Note that if I Ctrl+V in the Outlook message window after the code has run (when I have the Range.Copy line included), it pastes exactly as I want it - is there any way to paste clipboard, or even push the keystrokes to the message window??)  Any help would be appreciated.  My "Email Data" code is below.

Private Sub EmailData()

    Dim OutApp As Object
    Dim OutMail As Object
       
    ActiveDocument.Range.Select
    'ActiveDocument.Range.Copy

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "EmailAddressHere"
        .Subject = "SubjectHere"
        .HTMLBody = ActiveDocument.Range.FormattedText
        .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Open in new window

Jill9871Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
You can't paste to HTMLBody from the clipboard. But you can use the PasteAndFormat method with the wdFormatOriginalFormatting parameter to paste into the freshly created mail, just as you have described. For this purpose you need to establish a Selection or Range object in the mail. The following code does that.

Option Explicit

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.Range.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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jill9871Author Commented:
I had to include ActiveDocument.Range.Select, but otherwise, that worked perfectly - thanks very much!
0
FaustulusCommented:
Jull9871,
Yes, of course. Sorry about that.
Actually, I had intended ActiveDocument.Content.Copy
I'm not a fan of getting VBA to select things for the purpose of manipulating them.
0
Jill9871Author Commented:
Agreed - I switched to ActiveDocument.Content.Copy.  Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.