Paste clipboard contents using VBA

Last reviewed on December 30, 2013   —  22 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
 DataObj.GetFromClipboard

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
DataObj.GetFromClipboard

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
    mail.Save
 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
    DataObj.PutInClipboard
End Sub

About Diane Poremsky

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 Outlook forums by Slipstick.com.

22 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

    1. Diane Poremsky

      In the VBA Editor, go to Tools, Reference - if Microsoft Forms 2.0 Object Library is not listed, browse for it. (I added a screenshot of the Tools, References dialog to the page.)

  2. Michael Glotzkowski

    Hello,
    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!

    1. Diane Poremsky

      You'd use objtask.body = strPaste & vbcrlf & objtask.body (assuming the object you are working with is objTask).

      I haven't tried using word commends to insert it - but that is another possibility to insert it at the top of the task body.

    2. Diane Poremsky

      Actually, if you are using the code at create-task-outlookcom-local-tasks-folder, i did try different things and the body doesn't hyperlink if you are syncing with outlook.com. If you are using it with other accounts/data files, word commands should work.

      The basics of the word method is here

  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?

    1. Diane Poremsky

      Try using .htmlbody instead if .body. If that doesn't work, what version of outlook?

  4. Andreas

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

    1. Diane Poremsky

      I'm not sure why you'd get that with out seeing the entire code sample. See http://msdn.microsoft.com/en-us/library/office/gg251561(v=office.15).aspx for an explanation of the error message.

  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
    '

    '
    Range("A1:E10").Select
    Selection.Copy
    'This will create the email
    Dim myOutlookApp As Outlook.Application
    Set myOutlookApp = New Outlook.Application
    Set myEmail = myOutlookApp.CreateItem(olMailItem)
    myEmail.To = "xyz@1234.com"
    myEmail.Subject = "testmail4"
    myEmail.Body = GetFromClipboard --
    myEmail.send
    ActiveWorkbook.Save
    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.

    1. Diane Poremsky

      you need to use the msforms.dataobject - dim it, set it then get the text. This uses late binding so you don't have to set a reference to the msforms data object.

      Sub mail2()
      '
      ' mail2 Macro
      '

      '
      ActiveSheet.Range("A1:E10").Select
      Selection.Copy
      'This will create the email

      Dim myOutlookApp As Outlook.Application
      Dim myEmail As Outlook.MailItem

      Set myOutlookApp = New Outlook.Application
      Set myEmail = myOutlookApp.CreateItem(olMailItem)
      myEmail.To = "xyz@1234.com"
      myEmail.Subject = "testmail4"

      Dim DataObj As Object
      Set DataObj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
      DataObj.GetFromClipboard

      myEmail.Body = DataObj.GetText(1)
      myEmail.Display

      Set DataObj = Nothing

      End Sub

  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.

    1. Diane Poremsky

      Try using myEmail.HTMLBody instead of myEmail.Body. if that doesn't work, we can merge the macro with the one at http://www.slipstick.com/developer/code-samples/paste-formatted-text-vba/

    2. Diane Poremsky

      Ok, so changing the body doesn't work - this does - you need to set a reference to the word object model to use it.

      Sub mail2()
      ActiveSheet.Range("A1:E10").Select
      Selection.Copy
      'This will create the email

      Dim myOutlookApp As Outlook.Application
      Dim myEmail As Outlook.MailItem
      Dim objDoc As Object, wdRn As Object
      Dim objItem As Object

      Set myOutlookApp = New Outlook.Application
      Set myEmail = myOutlookApp.CreateItem(olMailItem)
      myEmail.To = "xyz@1234.com"
      myEmail.Subject = "testmail4"
      myEmail.Display

      Set objItem = myEmail ' Application.ActiveInspector.currentItem
      Set objDoc = objItem.GetInspector.WordEditor
      Set wdRn = objDoc.Range
      wdRn.Paste

      Set objItem = Nothing
      Set myOutlookApp = Nothing

      End Sub

  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 :(

    1. Diane Poremsky
  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!

    1. Diane Poremsky

      Do you need formatted text or just plain text, retaining any tablet like format?

  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.

    Thanks

  11. William

    Hello! Thank you for your help.

    How would you just copy text to the clipboard in Outlook? Currently I have this macro (with just example text here) on a button on the QAT:

    Sub Info()
    MsgBox "info1" & vbCrLf & _
    "info2"
    End Sub

    I _can_ copy from the message box but I get unwanted lines and extra text besides the text I do need, which is just the "Info1" and "Info2" parts:

    ---------------------------
    Microsoft Outlook
    ---------------------------
    info1 info2
    ---------------------------
    OK
    ---------------------------

    Can you recommend code instead to copy the 2 pieces of text above to the clipboard using vb in Outlook 2010?

    Thank you!

Leave a Reply

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

This site uses XenWord.