• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

VB Script in Outlook 2010


From my custom Outlook form, I have a script that pulls the data from a SQL field that was just a text field to a Bookmark field in a Word doc.  It's worked fine.  

Now the definition of that same field in SQL was changed so that it could contain formatting information.  When I pull the data now, it pulls all the formatting garbage with it.  How can I just pull in the text that I want to see?  It can be plain text, or with the formatting, I don't care.

My code goes like this:

          rst.Open "Select BEZ FROM BW_AUFTR_KTXT WHERE TEXT_ID = 25 and ID = '" & Trim(Item.UserProperties("JobNumber").Value) & "'", _
          oADOConn, adOpenKeyset, adCmdTable

objDoc.Bookmarks("Notes").Range.InsertBefore Trim(rst("BEZ"))

But instead of pulling in "asdf", it pulls over this:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Times New Roman;}}
{\colortbl ;\red0\green0\blue0;}
{\*\generator Msftedit;}\viewkind4\uc1\pard\tx640\tx1280\tx1920\tx2560\tx3200\tx3840\tx4480\tx5120\tx5760\tx6400\tx7040\tx7680\tx8320\tx8960\tx9600\tx10240\tx10880\tx11520\tx12160\tx12800\tx13440\tx14080\tx14720\tx15360\tx16000\tx16640\tx17280\tx17920\tx18560\tx19200\tx19840\tx20480\cf1\f0\fs20 asdf\par

Thanks for your help,
1 Solution
Chris BottomleyCommented:
What happens if you try the value property i.e.

objDoc.Bookmarks("Notes").Range.InsertBefore Trim(rst("BEZ").value)

Genius123Author Commented:
Nothing changed....  Maybe there is some function like this?...

e.g.   objDoc.Bookmarks("Notes").Range.InsertBefore FormatFromRichTextToPlainText(rst("BEZ")

You could use this to transform your Rtf in Plain text ( With reference to MS HTML Object library)

Function fRTF2Text(sRTF As Variant) As Variant
  Dim oDoc As HTMLDocument
If IsNull(sRTF) Then
    fRTF2Text = Null
  Set oTxt = New HTMLDocument
  oTxt.body.innerHTML = sRTF
  fRTF2Text = oTxt.body.innerText
End If
End Function

Open in new window

Robberbaron (robr)Commented:
@rgonzo.  just tested that out of interest and it does not work for richtext....

only methods i have seen use MSWord as the convertor. given you have a reference to oDoc already this may be of use... stackoverflow
Genius123Author Commented:
That worked, thanks.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now