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 could also use use the Word object model to copy the message body to the clipboard.
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.
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.
Remember, 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 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
Use Word Object Model to Copy (and Paste)
Current versions of Outlook use Word as the email as the email editor and can use the Word object model library to do things not normally supported in Outlook.
This sample copies the body of the selected message to the clipboard. To paste, use
objSel.PasteAndFormat (wdFormatOriginalFormatting)
Don't forget to set a reference to Word's Object model in Tools, References.
Sub CopyMessage()
Dim objMail As Outlook.MailItem
Dim objInsp As Inspector
Dim objDoc As Word.Document
Dim objSel As Word.Selection
Set objMail = Application.ActiveExplorer.Selection.Item(1)
If Not objMail Is Nothing Then
If objMail.Class = olMail Then
Set objInsp = objMail.GetInspector
If objInsp.EditorType = olEditorWord Then
Set objDoc = objInsp.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection
With objSel
.WholeStory
.Copy
End With
End If
End If
End If
Set objMail = Nothing
End SubThis sample shows how to paste the copied content into a new message:
Dim objMail As Outlook.MailItem
Dim objInsp As Inspector
Dim objDoc As Word.Document
Dim objSel As Word.Selection
Set objMail = Application.CreateItem(olMailItem)
With objMail
.To = "Alias@domain.com"
.Subject = "This is the subject"
.Display
End With
Set objInsp = objMail.GetInspector
If objInsp.EditorType = olEditorWord Then
Set objDoc = objInsp.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection
objSel.PasteAndFormat (wdFormatOriginalFormatting)
End If
Set objMail = Nothing
End SubPaste format types are below. For more information see WdRecoveryType Enumeration (Word).
| Name | Description |
|---|---|
| wdChart | Pastes a Microsoft Office Excel chart as an embedded OLE object. |
| wdChartLinked | Pastes an Excel chart and links it to the original Excel spreadsheet. |
| wdChartPicture | Pastes an Excel chart as a picture. |
| wdFormatOriginalFormatting | Preserves original formatting of the pasted material. |
| wdFormatPlainText | Pastes as plain, unformatted text. |
| wdFormatSurroundingFormattingWithEmphasis | Matches the formatting of the pasted text to the formatting of surrounding text. |
| wdListCombineWithExistingList | Merges a pasted list with neighboring lists. |
| wdListContinueNumbering | Continues numbering of a pasted list from the list in the document. |
| wdListDontMerge | Not supported. |
| wdListRestartNumbering | Restarts numbering of a pasted list. |
| wdPasteDefault | Not supported. |
| wdSingleCellTable | Pastes a single cell table as a separate table. |
| wdSingleCellText | Pastes a single cell as text. |
| wdTableAppendTable | Merges pasted cells into an existing table by inserting the pasted rows between the selected rows. |
| wdTableInsertAsRows | Inserts a pasted table as rows between two rows in the target table. |
| wdTableOriginalFormatting | Pastes an appended table without merging table styles. |
| wdTableOverwriteCells | Pastes table cells and overwrites existing table cells. |
| wdUseDestinationStylesRecovery | Uses the styles that are in use in the destination document. |
Pierre-Henry Delecourt says
Hi. Thanks a lot for this useful tip. I however can't fidn the reference to Microsoft Form, nor the FM20.dll library.
I bought the Microsoft Office in September 2019. Is there a new name for this library ?
Many thanks in advance
Diane Poremsky says
You need to click Browse and paste the path in. C:\Windows\System32\FM20.dll
Said says
Hi Diane,
Is there a way to paste a picture from clipboard directly using VBA?
Thanks.
Diane Poremsky says
No, not in Outlook. You need to use the word object. (Could probably use Excel's object model too.)
Victor says
How can you save clipboard content to a folder in Word without using userform even if it is using the windows api
Diane Poremsky says
As far as I know, you need to use the msforms data object -
dfa says
Thank you for the info
Rudy says
This is so helpful. Thanks Admin and all the member of this page.
Lu(ky says
How can "Sub CopyMessage" be modified to copy text up to the first "From" in an email (copy text from the most recent email reply)?
Diane Poremsky says
You'd need to find it the select everything above it -
With objSel
.Find.ClearFormatting
With objSel.Find
.Text = "From: "
.Replacement.Text = ""
.Forward = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.Execute
End With
.MoveUp Unit:=wdLine, Count:=1
.HomeKey Unit:=wdLine, Extend:=wdExtend
.MoveUp Unit:=wdScreen, Count:=1, Extend:=wdExtend
' .WholeStory
.Copy
End With
Lu(ky says
I'm sorry, I'm having trouble implementing this new code into the older code. I appreciate the fast reply and help!
Diane Poremsky says
I would need to see the code to help.
Lu(ky says
Unfortunately the above code does not locate the text "From"... the Sub still copies the entire email body. Any suggestion?
Diane Poremsky says
correct, it only copies the body. you need to use oMail.sendername or oMail.senderaddress to get the frm information.
Lu(ky says
I got it to work! Thank you for your help!!
Adam says
Hello Diane, I'm have a need to pass the value of a highlighted text within an email body directly to a URL. so basically, I would double-click (select) on a specfic key word, click some button on my ribbon and have that run a macro to take the selected key word and insert it into a pre-defined URL. I already have the URL code working. I'm stuck trying to get the selected keyword (in the email message body) copied and passed in my code. I'm not trying to copy the entire message body. Any ideas? Thank you!
Diane Poremsky says
Try using just .copy, removing .wholestory.
With objSel
.Copy
End With
Jason Johnson says
Hi Diane,
I'm trying to paste a collection of Excel cells I've copied into the clipboard into an Outlook email's .body. I'm trying to paste using destination styles so as to preserve the table grid of the cells I'm copying from my spreadsheet. While I can dump the contents unformatted easily from the clipboard, I can't seem to get anything to work using the Word references for using destination styles. I've tried playing around with the code in this post but no joy. Any help would be greatly appreciated.
Diane Poremsky says
You definitely need to use the word code https://www.slipstick.com/developer/code-samples/paste-clipboard-contents-vba/#word
do you get any error messages?
This should work to paste -
If objInsp.EditorType = olEditorWord Then
Set objDoc = objInsp.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection
objSel.PasteAndFormat (wdUseDestinationStylesRecovery)
End If
Valid Format types are here: https://msdn.microsoft.com/en-us/library/office/ff844915.aspx
Michele says
Hi Diane, I've used this code to past in the clipbord from many years ..
Dim x As New DataObject
x.SetText "Hi Diana"
x.PutInClipboard
now I've finally installed office2010 and windows10 but .. it doesn't work anymore! With CtrlV it paste just a pair of "?".
I've just checked the presence of Microsoft Forms 2.0 Object Library and ... its ok. Any suggest to find the solution?
AndyZ says
Hi Diane,
I've recently updated from Windows 7 32-bit and Office 2010 to Windows 10 64-bit and Office 2016. I am experiencing the same problem as the first commenter on this page (Michele, January 19, 2016).
The code:
MsgBox strContactDetails
DataObj.SetText strContactDetails
DataObj.PutInClipboard
works fine, except when it doesn't. When it works I get nice text in the MsgBox which then is copied into the clipboard where I can copy it into any other application. However intermittently it doesn't work - the text is displayed properly in the MsgBox but then goes into the clipboard as two question marks. Once the issue crops up it is remedied only by rebooting the computer.
This appears to be a known bug in Windows 64-bit builds. I found a page at https://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba which documents and reiterates the issue.
Have you seen this before? I only need the unformatted text and would be fine if all formatting was stripped out.
The full code is on your site at: https://www.slipstick.com/developer/code-samples/get-contact-details/
Thanks as always for your expertise and willingness to share.
AndyZ
Diane Poremsky says
I'll see if we can do it using word code instead. (I'm on vacation so it might be a few days.)
Diane Poremsky says
Interesting problems trump vacations. :) Try this snippet for the copy part - it's longer but worked in quickie test.
MsgBox strContactDetails
Dim objMail As Outlook.MailItem
Dim objInsp As Inspector
Dim objDoc As Word.Document
Dim objSel As Word.Selection
Set objMail = Application.CreateItem(olMailItem)
objMail.Body = strContactDetails
objMail.Display
Set objInsp = objMail.GetInspector
If objInsp.EditorType = olEditorWord Then
Set objDoc = objInsp.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection
With objSel
.WholeStory
.Copy
End With
End If
objMail.Close (olDiscard)
End With
Else
MsgBox "You need to select a Contact."
End If
AndyZ says
That is working wonderfully, Diane! I'll report back if I run into any problems but it has run a dozen times or so today without fail. Thank you so much! Enjoy your vacation!
Diane Poremsky says
As andyZ just pointed out, it's a bug in 64bit windows - it's not one I've experienced tho. The ?? replacement indicates an issue with font encoding.
Pasting into a new message body and copying using word code works, although is a heck of a lot longer than 3 lines.
Fulge says
I'm trying to extract the first two InLineShapes of a word document on an excel macro.
All the shapes are InLine Pictures, and I have tried to copy paste to excel to a chart then save, that works, but the quality of the picture gets greatly reduced, so I was wondering if I could just extract them directly from the word application object.
I'm not sure how can I use the Clipboard to store he picture then save it.
So far I have something like this:
i = 1
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
For Each aPict In ActiveDocument.InlineShapes
sName = orgDocuName & "_" & i & ".jpg"
aPict.Select
Selection.Copy 'Is this sending to clipboard automatically??
'Code to export/save the selected picture
DataObj.GetFromClipboard 'Goes ok
DataObject.GetData(1) 'Error, function doesn't exist
i = i + 1
Next aPict
So I really need help on how to save the content of the clipboard (given that the Select.copy is sending the image to the clipboard) to a .jpg file (or .bmp or .png)
Any ideas? suggestions?
Many thanks in advance
FloRyan says
Oh sorry that wasn't really accurate. Before I want to copy the content, I need to find the link in an unread email in my inbox and open it first.
Diane Poremsky says
That won't be so hard - you can use regex to locate it. I'll put a regex together.
Diane Poremsky says
This is a start - it opens a link in an email message -
Sub GetValueUsingRegEx()
Dim olMail As Outlook.MailItem
Dim Reg1 As RegExp
Dim M1 As MatchCollection
Dim M As Match
Dim strURL As String
Set olMail = Application.ActiveExplorer().Selection(1)
Set Reg1 = New RegExp
With Reg1
.Pattern = "(http([0-9a-z=\?:/\.&-])*)"
.Global = False
.IgnoreCase = True
End With
If Reg1.Test(olMail.Body) Then
Set M1 = Reg1.Execute(olMail.Body)
For Each M In M1
strURL = M.SubMatches(0)
Debug.Print strURL
Next
End If
Set Reg1 = Nothing
Dim oApp As Object
Set oApp = CreateObject("InternetExplorer.Application")
oApp.navigate (strURL)
oApp.Visible = True
'wait for page to load before passing the web URL
Do While oApp.Busy
DoEvents
Loop
End Sub
FloRyan says
Thank you, you are amazing. That works just fine!
Diane Poremsky says
I tried to insert it into a new message but i couldn't get it to work - the method i use for stationery needs to have the html saved locally.
FloRyan says
Is there a way to open all links in all unread emails in my inbox, because right now the macro is only opening the link of an open email? Thanks in advance!
Diane Poremsky says
it should be opening the first link in the selected message. We can loop and open all links (change .Global = True and move the lines that open IE after debug.print strurl line), but it has the potential to bring down the computer if you have a lot of links.
From there, it's just a matter of popping it in the macro at https://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/
FloRyan says
It worked out just fine, thanks again Diane!
Diane Poremsky says
Here's two macros - the first gets all urls in the message, the second one does all messages in a folder.
Open All Hyperlinks in an Outlook Email Message
FloRyan says
Hi Diane,
Thank you for this great website!
How do you copy the content of a website into the body of an email?
Best regards
Diane Poremsky says
If the message is hTML, it often works just to select all of the page then copy and paste... or use the File, Send page by email... or if its an html page on the hard drive, you can use file > insert as text. File> Insert can be automated via a macro - https://www.slipstick.com/outlook/email/create-new-message-using-html-file-stationery/
Using a macro is more difficult because you can't control the browser using VBA (so copy and paste is out) - the macro to insert an html file might work with a webpage - you just need to pass the page url to the macro.
FloRyan says
Unfortunately I couldn't figure out how to do it...
Diane Poremsky says
What part didn't work?
FloRyan says
It's hard to explain, but I'm kinda trying to copy a text on that webpage and that text is between 2 things, one would be a date and the other one would be a the word "next". Is there any vba code that can do that and which I can apply on every website?
Diane Poremsky says
In outlook email or a web page? In email, as long as the date format is the same in all messages, you can use regex to find it. The same method should work for web pages but you need to be able to automate the browser using VBA so that you can find the date. That is beyond the scope of this website so i can't say for sure if you can do it from outlook.
dmcmillo says
Hello Diane! Hopefully i have found someone that can help. I'm dealing with Access 2010. I have a form with 2 fields. Filed 1 is a text box and Filed 2 is a Bound Object Frame. My code below allows for the email to be called and information inserted into the subject and body. However my image i paste into the Bound object frame does not appear. Any ideas???
Private Sub Complete_Click()
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Dim sHTML As String
Dim sSubject As String
Dim sTo As String
Dim sCC As String
Dim sBody As String
sHTML = " Collation Team," & " " & "AutoRelease priorities have been changed to the below settings." & " " & "Reason" & " " & _
"New Settings" & " " & "" & " "
sSubject = "Collation AutoRelease Priority Change"
'sTo = ""
'sCC =""
Call SendEMail(sTo, sCC, sSubject, sHTML)
Diane Poremsky says
i'm guessing you are not embedding the image correctly into the message code.
H-curious says
Hi Diane,
as I said, I have a select case structure that identified the language used to compose the email and assign a signature based on the email format:
Em is the CurrentItem (that can be a new email, a reply or a forward)
MyLanguage is the LanguageID of the composed section of Em.
Select Case MyLanguage
Case 1036, 11276, 3084, 12300, 5132, 13324, 6156, 8204, 10252, 7180, 9228, 4108, 2060 'French
Select Case Em.BodyFormat
Case olFormatHTML
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_fr.htm"
Case olFormatRichText
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_fr.rtf"
Case olFormatPlain
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_fr.txt"
End Select
'This case about English language is redundant since we are using English as our default language (see Case Else)
Case 3081, 10249, 4105, 9925, 6153, 8201, 5129, 13321, 7177, 11273, 2057, 1033, 12297 'English
Select Case Em.BodyFormat
Case olFormatHTML
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_en.htm"
Case olFormatRichText
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_en.rtf"
Case olFormatPlain
MySignature = Environ("appdata") & "\Microsoft\Signatures\Signature_fr.txt"
End Select
End Select
I have tried following Ron de Bruin's advice and look extensively on the net and I cannot find anything that would enable me to insert the selected signature (that contains images).
MySIgnature is defined as a Variant.
The only solution that seems to work is copying and pasting the concerned file. But as I said, I might be missing something utterly simple.
Kind regards,
Hacène
H-curious says
Hi Diane,
Thank you for the suggestion.
My issue is that the files that are normally signatures, sometimes contain images.
The file is selected based on the email language and email formatting.
I tried various methods using .body or .htmlbody without success. I therefore opted for the simplest solution I could think off: open the file in Word copy it and paste it where I need it in the email before sending.
If you want, I could send you privately and confidentially the frm, frx and .bas so that you could see what I am trying to do.
Maybe I am missing something obvious and simple!
Kind regards,
Hacène
Diane Poremsky says
strBody = .HTMLbody should work (but i didn't test it). did you try using insertbefore?
H-curious says
Hello DIane,
I am writing an Outlook macro where I am copying formatted text from Word files at the end of an Email message.
I am encountering several issues with this.:
a) if Word was not opened, it refuses to close as it requests user input about the last copied item
b) I found a temporary solution to that problem by emptying the clipboard but, the macro works only once. After that, the text from Word is not pasted into Outlook.
c) if Word is opened, it doesn't close
Here is the sample code
Dim appWord As Object
Dim opWord As Boolean
On Error Resume Next
'Checks if Word is opened
Set appWord = GetObject(, "Word.Application")
opWord = False
'If not, opens Word
If appWord Is Nothing Then
Set appWord = CreateObject("Word.Application")
opWord = True
Err.Clear
End If
With appWord
.Activate
.Visible = True
.WindowState = wdWindowStateNormal
.Documents.Open MySignature
.Selection.WholeStory
Selection.Copy
End With
objSel.Collapse (wdCollapseEnd)
objSel.InsertParagraphAfter
objSel.PasteAndFormat wdFormatOriginalFormatting
'Close the opened document without saving
appWord.Close False
'If Word was not already opened, quit the application
If opWord = True Then
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
appWord.Quit
End If
Set appWord = Nothing
What am I missing?
Thank you in advance for your help.
H-curious says
Hello Diane,
Sorry for the disturbance.
I solved the issue. appWord was an instance of Word. I added a MyFile as Word.Document and modified the code to refer to MyFile.
The code is changed from the line With appWord as follows
Set MyFile = appWord.Documents.Open(MySignature)
With MyFile
.Activate
.Range.WholeStory
.Range.Copy
End With
Em.Display
objSel.Collapse (wdCollapseEnd)
objSel.InsertParagraphAfter
objSel.Paste
With MyFile
.Clipboard.Clear
.Close
End With
If opWord = True Then
appWord.Quit
End If
Thank you for you time.
Diane Poremsky says
thanks for the update.
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.
Diane Poremsky says
You'd normally use .body = strText & .body - i don't think you can use it with paste but you can do one of two thing.
before pasting, add the body to a string: strBody = .body
then add it back: .body = .body & strBody
the other option is to use . insertbefore instead of paste. A macro that uses insertbefore is here
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 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"
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
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!
Diane Poremsky says
Do you need formatted text or just plain text, retaining any tablet like format?
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 :(
Diane Poremsky says
I know the macro here pastes formatted text: https://www.slipstick.com/developer/code-samples/paste-formatted-text-vba/
Syhn John says
Success !
You are great.
Thanks a lot.
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
Try using myEmail.HTMLBody instead of myEmail.Body. if that doesn't work, we can merge the macro with the one at https://www.slipstick.com/developer/code-samples/paste-formatted-text-vba/
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
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
Andreas says
I got the message: Compile error: Statement invalid outside Type block
Diane Poremsky says
I'm not sure why you'd get that with out seeing the entire code sample. See https://msdn.microsoft.com/en-us/library/office/gg251561(v=office.15).aspx for an explanation of the error message.
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?
Diane Poremsky says
Try using .htmlbody instead if .body. If that doesn't work, what version of outlook?
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.
Diane Poremsky says
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
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.)