Paste clipboard contents using VBA

Last reviewed on December 30, 2013   —  20 comments

I need a macro that adds a specific text, plus the content of my clipboard, at the end of the subject of all messages I have selected. How do I get the content of the clipboard automatically, without first pasting it into an Input Box?

Although Outlook VBA doesn't include a paste from clipboard function directly, you can use the MSForms dataobject to transfer the clipboard contents to a string which is then called from VBA.

You can also use Word's 'Keep Source Formatting' to paste formatted text into an Item Body. Code sample is at Paste formatted text using VBA

Add code similar to this to your macro:

 Dim DataObj As MSForms.DataObject
 Set DataObj = New MSForms.DataObject

strPaste = DataObj.GetText(1)

The finished code will look something like the following. Note, you will need to have a reference to the Forms library in Tools, References.
Add the forms library as a reference
If you receive a "User-defined type not defined" you are missing the reference to Microsoft Forms 2.0 Object Library. If its not listed, add C:\Windows\System32\FM20.dll or C:\Windows\FM20.dll as a reference.

Sub AddtoSubject()
 Dim ex As Explorer
 Dim mail As MailItem
 Set ex = Application.ActiveExplorer
 Dim strPaste  As Variant
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject

strPaste = DataObj.GetText(1)

If strPaste = False Then Exit Sub
If strPaste = "" Then Exit Sub

 For Each mail In ex.Selection
    mail.Subject = mail.Subject & " my text " & strPaste
 Next mail
Set DataObj  = Nothing
End Sub

Copy to Clipboard

What about going in the other direction: copying text to the clipboard? Use PutInClipboard to capture the text.

Sub CapturetoClipbaord()
Dim oMail As MailItem
DataObj As MSForms.DataObject

    Set oMail = ActiveExplorer().Selection.Item(1)
    Set DataObj = New MSForms.DataObject
    DataObj.SetText oMail.Body
End Sub

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

20 responses to “Paste clipboard contents using VBA”

  1. Al Delgado

    How do I add the reference to Microsoft Forms 2.0 Object Library, for C:\Windows\System32\FM20.dll

  2. Michael Glotzkowski

    how can I modify your script so that it adds it into the Body of a Task instead of modifying the email subject?
    I know how to copy the Outlook id of an email but when I paste it into my taks it will not create a Hyperlink as it does with your script!

  3. Joe

    Diane, I am trying to paste the formatted text from the clipboard into the body of an outlook 2010 email, but it keeps dropping all of the formatting nad I only get the text. Is there a way to keep the formatting from the clipboard and put it in the .Body of an email?

  4. Andreas

    I got the message: Compile error: Statement invalid outside Type block

  5. Syhn John

    Dear Diane,
    I am new to VBA & macros, so need your support.
    I do a repetitive work daily by updating values in excel sheet table cells & then copy paste that table to the body of a fresh mail & sending it to a list of fixed recipients. I have tried to automate all this above with a macro code as below.

    Sub mail2()
    ' mail2 Macro

    'This will create the email
    Dim myOutlookApp As Outlook.Application
    Set myOutlookApp = New Outlook.Application
    Set myEmail = myOutlookApp.CreateItem(olMailItem)
    myEmail.To = ""
    myEmail.Subject = "testmail4"
    myEmail.Body = GetFromClipboard --
    MsgBox ("The email has been sent")

    End Sub

    But failed to find that email received in inbox has no contents. Why the data from clipboard is not available in the mail ?

    Kindly suggest a solution to simply make my copied contents to be available (with formating) in the mail body.

    Thanks in advance.

  6. Syhn John

    Hi Diane

    It works about sending a mail ! Gr8 & thanks a lot.
    But the data copied from excel to the mail body is not in same format as that of original.
    All the cell colouring, text formating (like fields with bold/italics etc), table borders etc properties are lost.

    Could you kindly help me out with that ?
    Thanks again.

  7. Syhn John

    Success !
    You are great.
    Thanks a lot.

  8. Shashank Darisi

    Hi Diane,

    I am trying to paste some data from an Excel Sheet into an outlok 2010 mail item as the contents. The data that I am copying has formatting like colors,table etc and I wish to retain the formatting in the mail content. Please help me on how I can achieve this. Using clipboard.GetText(1) pastes the data without the formatting :(

  9. Ben Burrell

    Hi Diane,

    I'm trying to edit the copy code to work with Excel. I'm trying to copy information from a website and bring it into the clipboard to eventually be pasted into an excel spreadsheet. Any ideas? Thank you!

  10. Page Menyatsoe

    Thanks Diane, I was worried about the formatting but I see on the comments you got it sorted. If you don't mind can you kindly update the blog instead to that other readers can get to it straight away. Thanks for this information, this cannot be found on a single book. I appreciate your time and effort to educate us.


Leave a Reply

If the Post Coment button disappears, press your Tab key.