Last reviewed on December 30, 2013   —  23 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


Comments

  1. Al Delgado says

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

    • Diane Poremsky says

      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 says

    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!

    • Diane Poremsky says

      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.

  3. Joe says

    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. Syhn John says

    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.

    • Diane Poremsky says

      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

  5. Syhn John says

    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.

    • Diane Poremsky says

      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

  6. Shashank Darisi says

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

  7. Ben Burrell says

    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!

  8. Page Menyatsoe says

    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

  9. William says

    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!

    • Diane PoremskyDiane Poremsky says

      rather than using a msgbox, use the copy to clipboard code at the bottom of the page then replace this line
      DataObj.SetText oMail.Body
      with
      DataObj.SetText "info1" & vbCrLf & _
      "info2"

  10. Bhavin says

    "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"

    hi Diana,

    Thanks a lot for providing this piece of information. It is very helpfull. In using your above code of wordeditor pasting i am not able to enter anything using .body, as pasting replaces the body and using .body later removes pasting. Can you please provide some solution so that i can get some .body text first and then pasting and again some text.

Leave a Reply

Please post long or more complicated questions at OutlookForums by Slipstick.com.

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