Paste Clipboard

K

Kathy Webster

I have text in my clipboard from an MSAccess table. How can I paste it into
the TO line of my email? My users could be using one of many email
programs, so I am getting to their email program using MSAccess's SendObject
command. The cursor is now in the user's email TO line, with the clipboard
full of email addresses. How do I simply paste the text?

TIA,
Kathy
 
K

Kathy Webster

Hi Allen,
I'm struggling but trying. I'm not sure how much to paste from that article.
I tried everything
from Function Clipboard2Text() through the very last EndFunction, but I'm
getting errors. I then preceeded the last EndFunction with your 3 lines of
code below.
Before I bother you with the errors, have I pasted the right stuff?
Kathy
 
A

Allen Browne

1. Click on the Modules tab of the Database window.

2. Click New.
Access opens a new module window.

3. Copy everything from:
Declare Function abOpenClipboard Lib "User32" Alias ...
to the last:
End Function
and paste it into the module.

4. Delete the 2 lines in italic, i.e.:
To copy to the clipboard:
and
To paste from the clipboard:

5. Choose Compile on the Debug menu (while in the code window.)
This ensures Access understands the code.

You can now use the Clipboard2Text() function in your code.
 
K

Kathy Webster

Allen,
Thank you so much for your step by step. You are so kind.

I am getting an error, and I'm sure it must be because of the order in which
I start your RunCode:
Run-time error '2295':
Unknown message recipients. The message was not sent.
The debugger shows this line of your code highlighted:
DoCmd.SendObject acSendNoObject, , , strTo

Here is my sequence:
1. Macro opens query which displays one column: the email address of each
recipient.
2. Macro then Runs Command: select all records
3. Macro then does a RunCode of your 3 lines of Clipboard2Text
4. Macro then closes query.

I know this is an incredibly clutzy way to do this, and I don't have
anything else this clumsy in my apps, but in this case, I've been unable to
get a cleaner solution, so I does what I cans. :)

Kathy
 
A

Allen Browne

Immediately above the SendObject line, add:
Debug.Print strTo

Run the code. When it fails, open the Immediate Window (Ctrl+G) and look at
what came out. Is it a valid email address?
 
K

Kathy Webster

Allen,
Well, I put the Debug line immediately above the SendObject line.
Now when I run the code, Outlook opens, and in the TO line of the email, I
see:
xxxx
(This string of x's is the last thing that was in my copy buffer BEFORE I
ran the code.)
The immediate window shows the string of x's also.

Keep in mind, what my macro does PRIOR to running your code is to select all
records of a query, where the query is a 1 column grid of several email
addresses. But that isn't even getting to the copy buffer.
 
A

Allen Browne

Sounds like you did not perform the last step:
You can now use the Clipboard2Text() function in your code.

Use RunCode in your macro.
 
K

Kathy Webster

Allen,

Yes, I did perform the last step. As I referenced in step #3 below, this is
my runcode stuff in my macro (RunCode EmailGroup()):

Public Function EmailGroup()
Dim strTo As String
strTo = Clipboard2Text()
Debug.Print strTo
DoCmd.SendObject acSendNoObject, , , strTo
End Function

Note I RENAMED the function in my RunCode statement, though.
I renamed it EmailGroup() (then put RunCode EmailGroup() in my macro.)
I did this because when I tried to keep it named Clipboard2Text(), I got an
error:
The expression contains an ambiguous name.
You may have 2 or more functions with the same name in different
modules.

Kathy
 
A

Allen Browne

Not sure I followed the rename bit. Presumably you retained the
Clipboard2Text() function, and then used it in your EmailGroup() function.
That's fine.

So, if the code is executing, and it does not trigger an error, and the
strTo does not have the right result, perhaps the clipboard does not contain
what you expect it to. You can check that by opening the Immediate Window
(Ctrl+G) and entering:
? Clipboard2Text()
 
K

Kathy Webster

We seem to be going around in circles here.
Maybe this will be easier if I recap:

1. I created a new module called ClipboardCopyPaste.
2. The contents of the module are your code from
http://allenbrowne.com/func-07b.html:

Option Compare Database
Declare Function abOpenClipboard Lib "User32" Alias...

up to and including:

...CB2T_Free:
If abGlobalFree(hMemory) <> APINULL Then
MsgBox "Unable to free global clipboard memory."
End If
End Function

3. This includes your function somewhere in the middle:
Function Text2Clipboard(szText As String)

4. I closed and saved that module.

5. I opened a preexisting module called Module1 and created this:

Public Function EmailGroup()
Dim strTo As String
strTo = Clipboard2Text()
Debug.Print strTo
DoCmd.SendObject acSendNoObject, , , strTo
End Function

6. I created a macro that does this:
OpenQuery qEmailAddresses
RunCommand SelectAllRecords
RunCode EmailGroup()

7. I restart my computer.

8. I run the macro.

8. I get runtime error 94: Invalid use of Null. strTo = Clipboard2Text()
is
highlighted. In the Ctrl+G window I type ? Clipboard2Text() and get "Null".

Helllp :)
 
P

Pieter Wijnen

Means nothing is in the Clipboard

Pieter

Kathy Webster said:
We seem to be going around in circles here.
Maybe this will be easier if I recap:

1. I created a new module called ClipboardCopyPaste.
2. The contents of the module are your code from
http://allenbrowne.com/func-07b.html:

Option Compare Database
Declare Function abOpenClipboard Lib "User32" Alias...

up to and including:

...CB2T_Free:
If abGlobalFree(hMemory) <> APINULL Then
MsgBox "Unable to free global clipboard memory."
End If
End Function

3. This includes your function somewhere in the middle:
Function Text2Clipboard(szText As String)

4. I closed and saved that module.

5. I opened a preexisting module called Module1 and created this:

Public Function EmailGroup()
Dim strTo As String
strTo = Clipboard2Text()
Debug.Print strTo
DoCmd.SendObject acSendNoObject, , , strTo
End Function

6. I created a macro that does this:
OpenQuery qEmailAddresses
RunCommand SelectAllRecords
RunCode EmailGroup()

7. I restart my computer.

8. I run the macro.

8. I get runtime error 94: Invalid use of Null. strTo = Clipboard2Text()
is
highlighted. In the Ctrl+G window I type ? Clipboard2Text() and get
"Null".

Helllp :)
 
K

Kathy Webster

Allen, hath you forsaken me?
:)
Kathy

Kathy Webster said:
We seem to be going around in circles here.
Maybe this will be easier if I recap:

1. I created a new module called ClipboardCopyPaste.
2. The contents of the module are your code from
http://allenbrowne.com/func-07b.html:

Option Compare Database
Declare Function abOpenClipboard Lib "User32" Alias...

up to and including:

...CB2T_Free:
If abGlobalFree(hMemory) <> APINULL Then
MsgBox "Unable to free global clipboard memory."
End If
End Function

3. This includes your function somewhere in the middle:
Function Text2Clipboard(szText As String)

4. I closed and saved that module.

5. I opened a preexisting module called Module1 and created this:

Public Function EmailGroup()
Dim strTo As String
strTo = Clipboard2Text()
Debug.Print strTo
DoCmd.SendObject acSendNoObject, , , strTo
End Function

6. I created a macro that does this:
OpenQuery qEmailAddresses
RunCommand SelectAllRecords
RunCode EmailGroup()

7. I restart my computer.

8. I run the macro.

8. I get runtime error 94: Invalid use of Null. strTo = Clipboard2Text()
is
highlighted. In the Ctrl+G window I type ? Clipboard2Text() and get
"Null".

Helllp :)
 
A

Allen Browne

Clipboard2Text() can return a Null.
A string variable cannot accept a Null.
That's why the line attempting to return the value from the function to the
string fails.

You can fix that error like this:
Public Function EmailGroup()
Dim VarTo As Variant
varTo = Clipboard2Text()
If IsNull(varTo) Then
Debug.Print "No text retrieved from clipboard"
Else
Debug.Print To
DoCmd.SendObject acSendNoObject, , , varTo
End Function

The function will return Null if there is no text in the clipboard, or if an
error occurs trying to retrive it. To test if the text you expect is in the
clipboard, open Notepad and paste. If the text is there, but is not being
returned by the function, you have a bug to fix. Add the line STOP to the
top of the procedure. Then when you run it, press F8 repeatedly to step
through the procedure, and see what's going on.
 
K

Kathy Webster

Hi Allen,
Glad you are back!
I have already tried what you just suggested:Nothing pastes. The clipboard is empty.
Kathy
 
A

Allen Browne

Kathy, I'm confused.

You started this thread with the words:
I have text in my clipboard

Apparently this is not the case.

Are you actually trying to go the other way, and put something in the
clipboard?
 
K

Kathy Webster

No. I thought your code was doing the "copy", but I guess it isn't. So now I
added the copy command to my macro. So let's start from this new test:

1. I restart my computer so that it starts with no text in the clipboard.
2. I run my macro, which opens a query, selects all records, then runs the
command COPY.
3. Then the macro continues by running the EmailGroup() code, which is:
Dim strTo As String
strTo = Clipboard2Text()
Debug.Print strTo
DoCmd.SendObject acSendNoObject, , , strTo
4. Then debug error comes up, invalid use of null. Debug window highlights:
strTo = Clipboard2Text()
6. Ctrl+G window shows nothing.
7. I open notepad, and paste. It shows nothing.
 
P

Pieter Wijnen

the function is
Access.DoCmd.RunCommand Access.acCmdCopy
which will copy the contents of any *control* having the focus

HtH

Pieter
 
A

Allen Browne

Alternatively, if you have the address in a string variable, you can use the
Text2Clipboard() function to put it in the clipboard.

But I still don't get why need the clipboard here. If you have the email
address in a control named (say) txtEmail, you can just use that control
name in SendObject, e.g.:
DoCmd.SendObject acSendNoObject,,,Me.txtEmail,,,"Hello", "Here 'tis.",
True

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Pieter Wijnen"
 
K

Kathy Webster

Will that work if the email address is in a query and there are 10 rows in
the query, and I need all 10 email addresses?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top