Call a string (from a text box on a form) for use in a VBA function

[dianekvg]

I don't know VBA. I currently have an Access project that uses the EmailDatabaseObject macro to paste the content of a text box from a form to the body of an Outlook message. The problem is that there is a character limit that is limiting functionality. I am making good progress on a workaround using the following code, which is meant to copy a string to the user's clipboard:


(I am new to this forum and it will not allow me to add the link. The code I am referencing is from Microsoft's support site. The page is called acc2000-how-to-send-information-to-the-clipboard. Maybe I can add it in a followup comment? Or someone else can post it? I will post the content below.)


Can someone help me adjust this code to call the string that I am wanting to be copied to the clipboard? Here is the location of the string that I need to have copied: Forms("Edit_Send_Email_Text").EMAIL_BODY


Thanks in advance!



Here is the code from the above referenced link that I am using to write this function:


_____________________



To copy information to the Clipboard, follow these steps:

NOTE: You may have some Microsoft Windows API functions defined in an existing Microsoft Access library; therefore, your declarations may be duplicates. If you receive a duplicate procedure name error message, remove or comment out the declarations statement in your code.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Start Microsoft Access and open any database or project.
Create a module and type or paste the following lines in the Declarations section:Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096


Type or paste the following procedure:Function ClipBoard_SetData(MyString As String)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long

' Allocate moveable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)

' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere2
End If

' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Function
End If

' Clear the Clipboard.
X = EmptyClipboard()

' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If

End Function



To test this function, type the following line in the Immediate window, and then press ENTER.

? ClipBoard_SetData("To Clipboard")

Press CTRL+V (the shortcut for Paste) and note that "To Clipboard" is pasted into the Immediate window from the Clipboard.

[OBP]

Can I ask why you want to paste it to the clipboard, rather than directly to outlook?

[dianekvg]

Can I ask why you want to paste it to the clipboard, rather than directly to outlook?



It currently uses the EmailDatabaseObject macro to paste the content of a text box from a form to the body of an Outlook message. The problem is that there is a character limit that is limiting functionality.

[OBP]

Yes but where is the limit, on the form or in the email body.
If it is on the form switch to a Memo field.

[dianekvg]

Yes but where is the limit, on the form or in the email body.
If it is on the form switch to a Memo field.


The form and field limits are set correctly. There is a limit with the number of characters the SendObject method will handle.

[OBP]

How will using the clipboard help with the SendObject limit?
You could overcome that by saving the data as an attachment to send with the email.
How many characters are you trying to send?

[dianekvg]

How will using the clipboard help with the SendObject limit?
You could overcome that by saving the data as an attachment to send with the email.
How many characters are you trying to send?


Sending the data as an attachment will not work. The data is a very long string and it must be sent in the body of an email; it is thousands of characters. Copying it to a clipboard helps because there is already a button on a form that opens a new message to the desired recipient. So, the user will just have to hit the button to open the message an then Ctrl+V to paste the string to the body - a fairly simple operation to get around the fact that we can't automate with the SendEmailObject.

[dianekvg]

Someone from another forum helped me solve this! :)
Thanks so much!

[dianekvg]

If anyone looks at this thread for future help, this is the code that I was needing help with:


Option Compare Database
Option Explicit


Private Sub Command6_GotFocus()


On Error GoTo ErrHandle:


Dim strTest As String
strTest = Me.EMAIL_BODY


' ClipBoard_SetData (Me.EMAIL_BODY)
ClipBoard_SetData (strTest)


ErrHandle_Exit:
Exit Sub
ErrHandle:
MsgBox Error$
Resume ErrHandle_Exit


End Sub

[OBP]

I am not sure what the problem is as the string can be up to 64K characters and it sounds like it is the transfer of data from Access to the email itself, which will not be fixed by copy & paste.
But Access has the following copy command

DoCmd.RunCommand acCmdCopy

You need to set the focus on the field to be copied prior to using the copy command.

ps you can also use the acCmdPaste function as well.

[dianekvg]

I am not sure what the problem is as the string can be up to 64K characters and it sounds like it is the transfer of data from Access to the email itself, which will not be fixed by copy & paste.
But Access has the following copy command

DoCmd.RunCommand acCmdCopy

You need to set the focus on the field to be copied prior to using the copy command.

ps you can also use the acCmdPaste function as well.




Thanks for the suggestion. The code that I posted already solved my problem and the copy/paste is working beautifully! Thanks