Clipboard paste in macro

[Imbizile]

Im struggling to make a macro that adds a specific text, lets sat "CRM ", plus then content of my clipboard, at the end of the subject of all messages I have selected. This following works great, but is missing the clipboard paste, as the "pastedtext" should be the actual clipboard data.

Sub Renamemails()
Dim ex As Explorer
Dim mail As MailItem
Dim strTemp As String
Set ex = Application.ActiveExplorer

For Each mail In ex.Selection
strTemp = mail.Subject & " CRM " & "pastedtext"
mail.Subject = strTemp
mail.Save
Next mail

End Sub

It seems there is no simple code just to paste in Outlook, at least i havent been able to find it, so any help get the the actual clipboard text into the subject would be highly appreciated

Thanks in advance

/Imbizile

 

[Diane Poremsky]

VBA doesn't support pasting from the clipboard. You need to use Win32 clipboard API's.

 

[Imbizile]

Prior to running this macro i will have made a CTRL+C on a number eg. 1658794533, and i thought it would be an easy job to get that into a string.

So there is no way to get a simple CTRL+V into a String variable in outlook?

 

[Imbizile]

I messed a bit more with this and figured I could do this through an Inputbox to get my clipholder number into a variant, by doing a CTRL+V when it pops up. I also added Exit Sub, in case I press escape or dont enter anything, so my code now looks like this:

Sub Rename()
Dim ex As Explorer
Dim mail As MailItem
Dim strTemp As String
Set ex = Application.ActiveExplorer
Dim strNumber As Variant
strNumber = InputBox("Paste number")

If strNumber = False Then Exit Sub
If strNumber = "" Then Exit Sub

For Each mail In ex.Selection
strTemp = mail.Subject & " CRM " & strNumber
mail.Subject = strTemp
mail.Save
Next mail

End Sub

This works as intended but it is still an unnecessary step, as I would prefer to get it into the variant, without the "pop-up, CTRL+V, press return" act. Any ideas to do this or is this procedure as "shaved" as possible in Outlook?

 

[Diane Poremsky]

Kewl. It was easier than I thought... and no Win API needed. UserForms support paste from clipboard, so you just need to call a userform object (no actual userform needed though).

As an FYI, using the win api *should* only be a few lines in a function but I couldn't find any samples online. It doesn't really matter because you can't beat 3 lines.

This is the macro I tested with -

Sub Rename()
Dim ex As Explorer
Dim mail As MailItem
Dim strTemp As String
Set ex = Application.ActiveExplorer
Dim strNumber As Variant

Dim DataObj As MSForms.DataObject

Set DataObj = New MSForms.DataObject

DataObj.GetFromClipboard

strNumber = DataObj.GetText(1)

MsgBox strNumber
'If strNumber = False Then Exit Sub

' If strNumber = "" Then Exit Sub
For Each mail In ex.Selection
strTemp = " CRM " & strNumber
mail.Subject = strTemp
mail.Save
Next mail

End Sub

 

[Imbizile]

Amazing, this was exactly what I was looking for. Edited the macro a bit and now it does what its supposed to, without pop-ups or anything

Oddly enough I got the "User-defined type not defined" error at first and read up on it and figured I had to make a reference to Microsoft Forms 2.0 Object Library, but it was not in my references, so had to add it manually from c:\Windows\SysWOW64\FM20.dll.

But as I said it works like a charm now and I really appreciate the input.

Thanks a bunch

 

[Diane Poremsky]

I didn't mention the forms because i thought it should be enabled by default. It was in my install... but maybe that was because i have a userform in my project.

 

[svigneaux]

Hi,

This thread was very helpful as I tried to find a solution to my problem - pasting from the clipboard into an Outlook message BUT taking the formatting of the text I'm pasting into. For example, I copied some heavily formatted text from a Word doc onto my clipboard. Now I want to paste that into the middle of a paragraphi I'd already written in an Outlook message. I want the pasted text to match the rest of the paragraph, not retain the formatting of the document I copied it from.

In Word I have this macro that does just what I want, and I assign it to Control + shift + V so I have a shortcut that does this.

Sub PasteUnformatted()

'

' PasteUnformatted Macro

'

'
Selection.PasteAndFormat (wdFormatPlainText)

End Sub​

I saw that your two macros each managed to paste from the clipboard into an Outlook message. I tweaked and tweaked but couldn't get either of yours to do what I need.

I tried figuring out a variant of the PasteAndFormat command from Word but no luck.

Would you have any counsel to offer? I'd be grateful. This "copy from Word and paste into Outlook" is something I do a lot and am forever needing to then highlight the pasted text and match the formatting of the paragraph into which I pasted it. My Word macro fixed it but I cannot make a working one for Outlook

Thanks,

S

 

[svigneaux]

I should have mentioned that one of Diane's posts on SlipStick solved another long-standing frustration of mine - a macro to format a selected block of text and a Quick Access toolbar button to fire it off. Very nice, a big time saver for me.

 

[Diane Poremsky]

See http://www.slipstick.com/developer/code-samples/paste-formatted-text-vba/ - swap out wdFormatOriginalFormatting for the plain text one.

 

[svigneaux]

Thank you for the very fast reply.

I had seen that one and tried modifying it. It does paste unformatted text, but it pastes it into a new Appointment object. I need to paste it into whatever email I am working on at the time, the one that has "focus" on my desktop.

I confess I'm over my head in modifying the code below, from the one you suggested, so it pastes into my email instead of creating a new Appointment object and pasting unformatted into it.

Sub PasteUnFormattedClipboard()

On Error Resume Next

Dim olCal: Set olCal = Application.CreateItem(1)

olCal.Subject = "Testing " & Now

olCal.Location = "Here"

olCal.Display

Dim objItem As Object

Dim objInsp As Outlook.Inspector

Dim objWord As Word.Application

Dim objDoc As Word.Document

Dim objSel As Word.Selection

Set objItem = olCal ' Application.ActiveInspector.currentItem

Set objInsp = objItem.GetInspector

Set objDoc = objInsp.WordEditor

Set objWord = objDoc.Application

Set objSel = objWord.Selection

objSel.PasteAndFormat (Word.WdRecoveryType.wdFormatPlainText)

Set objItem = Nothing

Set objInsp = Nothing

Set objDoc = Nothing

Set objWord = Nothing

Set objSel = Nothing

End Sub​

S

 

[Diane Poremsky]

i'm on my tablet and can't put them together, but you need to use the lines that dim objitem and below, and set the word objects. you don't need the lines at the top that create the outlook but you will need to reference the item you want to paste in - that is the set objitem = ocal line. going from memory, it would be set objitem = application.currentitem something. (the macro i have that formats selected text should reference the current item. )

 

[Diane Poremsky]

I was close - you need Set objItem = Application.ActiveInspector.currentItem

This page has screenshots of the references dialog too - you need to set a reference to word to use word vba.

http://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/

 

[Diane Poremsky]

Ah the horrors of a small tablet screen - on the big screen i now see the code you would use for current item is right in the line:

Set objItem = olCal ' Application.ActiveInspector.currentItem

Just need to delete the olCal ' part.

 

[svigneaux]

That's the guidance I needed, thank you. My completed macro follows. The key was setting the PasteAndFormat command to Word.wdFormatPlainText.

Sub Paste_Un_FormattedClipboard()
On Error Resume Next


Dim objItem As Object

Dim objInsp As Outlook.Inspector

Dim objWord As Word.Application

Dim objDoc As Word.Document

Dim objSel As Word.Selection

Set objItem = Application.ActiveInspector.CurrentItem

Set objInsp = objItem.GetInspector

Set objDoc = objInsp.WordEditor

Set objWord = objDoc.Application

Set objSel = objWord.Selection

objSel.PasteAndFormat (Word.wdFormatPlainText)

Set objItem = Nothing

Set objInsp = Nothing

Set objDoc = Nothing

Set objWord = Nothing

Set objSel = Nothing

End Sub

Thank you for the assistance. I’ve been trying to create these macros in Outlook 2010’e editor for ages, though only very intermittently. They will save me a lot of time. Thank you again.

S