VBA to construct a string, then add RTF formatting so can be Pasted into a memo field (2 Viewers)

bignose2

Registered User.
Local time
Today, 18:51
Joined
May 2, 2010
Messages
219
Hi,

Not sure would help anyway but don't want to use the Forms reference as not used elsewhere.

I have code that loops through records & creates a large string/text field e.g.
AllDatesX = "
Bob 1 June - 10 June : OK
Jack 15 July - 20 July : Not OK
Jim 20 Aug - 25 Aug : OK"
inc. CRLF etc.

ClipBoard_SetText (AllDatesX)

I then either Ctrl V or DoCmd.RunCommand acCmdPaste to put this inside a large memo/long rtf text field in the correct place. (Actually an email to send)

All works well & used for a long time but would really like "Not OK" to be in bold. Some times I manually do but hoped to be automated

I cannot work out a way to add this to a text string, adding html markups does not work, <strong> etc. etc.

Is there a way to do this?

I imagine perhaps set an unbound field (rtf) to the string, then copy paste that but think once did something like that years ago & all I can remember was not a good idea, can't remember why, or perhaps did not work so hopefully a cleaner way

Thanks I/A

not relevant but in case.
Code:
Function ClipBoard_SetText(strCopyString As String) As Boolean
  Dim hGlobalMemory As Long
  Dim lpGlobalMemory As Long
  Dim hClipMemory As Long

  ' Allocate moveable global memory.
  '-------------------------------------------
  hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 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, strCopyString)

  ' Unlock the memory and then copy to the clipboard
  If GlobalUnlock(hGlobalMemory) = 0 Then
    If OpenClipboard(0&) <> 0 Then
      Call EmptyClipboard
      hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
      ClipBoard_SetText = CBool(CloseClipboard)
    End If
  End If
End Function

Function ClipBoard_GetText() As String
  Dim hClipMemory As Long
  Dim lpClipMemory As Long
  Dim strCBText As String
  Dim retval As Long
  Dim lngSize As Long
  If OpenClipboard(0&) <> 0 Then
    ' Obtain the handle to the global memory
    ' block that is referencing the text.
    hClipMemory = GetClipboardData(CF_TEXT)
    If hClipMemory <> 0 Then
      ' Lock Clipboard memory so we can reference
      ' the actual data string.
      lpClipMemory = GlobalLock(hClipMemory)
      If lpClipMemory <> 0 Then
        lngSize = GlobalSize(lpClipMemory)
        strCBText = Space$(lngSize)
        retval = lstrcpy(strCBText, lpClipMemory)
        retval = GlobalUnlock(hClipMemory)
        ' Peel off the null terminating character.
        strCBText = Left(strCBText, InStr(1, strCBText, Chr$(0), 0) - 1)
      Else
        MsgBox "Could not lock memory to copy string from."
      End If
    End If
    Call CloseClipboard
  End If
  ClipBoard_GetText = strCBText
End Function

Function CopyOlePiccy(Piccy As Object)
  Dim hGlobalMemory As Long, lpGlobalMemory As Long
  Dim hClipMemory As Long, x As Long

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

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


  'Need to copy the object to the memory here

  lpGlobalMemory = lstrcpy(lpGlobalMemory, Piccy)

  ' 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
'*********  Code End   ************
 

June7

AWF VIP
Local time
Today, 10:51
Joined
Mar 9, 2014
Messages
5,423
<strong></strong> is not supported, use <b></b>

Building strings with HTML tags for email is common topic. However, normally code is directly building email, not saving into a text field in table.

I just did a test with HTML tagged string saved in a Short Text field and then called that field in code to build email. The bold emphasis was applied.
 

isladogs

MVP / VIP
Local time
Today, 18:51
Joined
Jan 14, 2017
Messages
18,186
<strong></strong> is not supported, use <b></b>

Not supported in what sense?

1652890973538.png


In addition, <strong> or <B> both work in HTML email sent from Access. For example:

Code:
   aSubject = "Test HTML Email message - no attachments"
    aHTMLBody = "This is a <FONT size=5><FONT color=#800000><strong>TEST message</strong><FONT size=3>.<FONT color=#000000>" & _
        " to check <I>HTML email</I> with no attachments from the <B><I>CDOEMailTester</B></I> application" & _
       " <P><B><U>Example Image:</B></U></P>" & strImage

1652891349385.png
 
Last edited:

bignose2

Registered User.
Local time
Today, 18:51
Joined
May 2, 2010
Messages
219
Hi,

thanks all for very fast responses.

I did try <br> , a bad example <strong> for access rtf.
Nothing seems to works in an string variable, using a form field will.

I am probably over complicating, I was trying to avoid using a me.field, constructing there, setting focus & using
DoCmd.RunCommand acCmdCopy
Prefer ClipBoard_SetText () but this does not keep the rtf stuff.

I seem remember advise not good to use acCmdCopy also, I have to use acCmdPaste though.

Will means a bit of programming, nothing huge but hoped a v.simple way round.
I was not keen on moving focus around too much as position in the main email field was retained & other bits a pieces I have going on.

Basically Alt D - added these date in a email, if I Press a button did something similar but not exact so form focus was important but sure all can be overcome.
 

isladogs

MVP / VIP
Local time
Today, 18:51
Joined
Jan 14, 2017
Messages
18,186
Sorry. Don't understand.
As you can see from my screenshots, both <strong> and <b> (NOT <br>) do work in a text string
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:51
Joined
Feb 19, 2013
Messages
16,553
don't see why you can't build your string, constructing as rich text as you go then simply assign the string to the html body of the email
 

June7

AWF VIP
Local time
Today, 10:51
Joined
Mar 9, 2014
Messages
5,423
Well, dealt with another thread where <strong> was attempted and it would not work. But just tested and find it does work. So no idea why it gave issue before nor why this OP is having difficulty with it.

@bignose2, the tag is <b></b>, not <br> if you want to bold text. I have no problem using tags in VBA building email or saved in text field. But why use programmatic copy/paste just to save a string into a field?
 

bignose2

Registered User.
Local time
Today, 18:51
Joined
May 2, 2010
Messages
219
Hi,
Weird, my login was rejected so slow to reply, OK now, same user & PW, anyway..

Sorry was lazy typing, I do use <b> for bold etc & <br> Crlf
It is not hard to re-code some stuff to use a text field on the form but ...

Code:
aHTMLBody = "This is a <FONT size=5><FONT color=#800000><strong>TEST message</strong><FONT size=3>.<FONT color=#000000>" & _
        " to check <I>HTML email</I> with no attachments from the <B><I>CDOEMailTester</B></I> application" & _
       " <P><B><U>Example Image:</B></U></P>" & strImage

ClipBoard_SetText (aHTMLBody)

Does not work, the pasted text included the "<FONT size=5" etc. & not the formatting.

Using
me!CopyPasteField = "this is a <FONT SIZE=5... etc
me!CopyPasteField.setfocus
acCmdCopy
then pasting does work fine.
I had some lostfocus & gotfocus procedures that were messed up having to setfocus on this field to copy & a few other issues moving focus caused but can be fixed but as I say would hoped to avoid some of the issues.

"But why use programmatic copy/paste just to save a string into a field?"

I have a large email, I type, add lists of dates using the auto copy paste, manually change, duplicate, highlight, insert notes
I don't think would be possible building a string like a mail merge.

Thanks anyway, will work on the field method.
 

Users who are viewing this thread

Top Bottom