Building Block to VBA String Variable

[Shred Dude]

Playing around with BuildingBlock Entries in a Template and hitting a pot hole here.

I've saved a couple entries to a Templates Building Block, Custom 1 Gallery under a new Category: "Email". These are nicely formatted paragraphs that I want to use as the Body in an Outlook email message.

First thing I hit was having trouble returning the BuildingBlockEntry to a string variable in VBA. All I could get was the first 255 characters of the entry with code like this:
strBody = ActiveDocument.AttachedTemplate.BuildingBlockEntries("Contract Email").value

So, I then resorted to embedding a BuildingBlock Gallery Content Control in the Template to which I could insert the desired BuildingBlockEntry. Then I would grab that CC's range into my vba string variable. Example:

'Retrieve the Boilerplate from the Template's Building Blocks
ActiveDocument.AttachedTemplate.BuildingBlockEntries("Contract Email").Insert where:=ActiveDocument.SelectContentControlsByTag("email").Item(1).Range, RichText:=True
strBody = ActiveDocument.SelectContentControlsByTag("email").Item(1).Range.Text
ActiveDocument.SelectContentControlsByTag("email").Item(1).Range.Delete

This succeeded in populating my string variable with the full length of buildingblock entry, which I then put into an email message. However, some of the formatting gets lost in the email's body. In particular, some hyperlinks appear as plain text in the email body, whereas when inserted in the document they appear nicely as blue underlined text as you'd expect.

I've tried different bodyformat settings in the outlook message to no avail.

Am I losing the formatting when I extract it from the content control as range.text?

Is there a way to return the full entry to a string variable without having tot first insert it into a document?

[Frosty]

Have you looked at using range.FormattedText?

I think how that translates will be determined by the destination... there are significant differences between html emails and rich text emails... and I think there might be additional snags if outlook is using Word as the email editor, but it's been a long time since I've done any of this.

[Shred Dude]

Thanks for the suggestions. I didn't know about .Formatted Text. Gave it a try but got the same results. I had already played with the settings on the OL mail message's bodyformat and nothing worked; setting it to RichText didn't preserve the formatting of my Word Building Block entry and it included my attachment in the body as an Icon instead of up on the attachments line. Not what I wanted.

I fell back to my Excel experience and adapted Ron DeBruin's RangeToHTML method, and it worked for me. All formatting is preserved in the resulting email I create.

Seems like a lot of work. Am I missing an easier way? My scenario is I have two or three boilerplate messages saved as BuildingBlock entries in the Template. Depending on circumstances found in the document, one of those boilerplate entries will be used by a routine that packages all but the first page of the document into a PDF and attaches it to an email. This is all launched by a Ribbon button. One click, your email is ready to go.... Any ideas on making this more efficient most welcome!

Still curious about not being able to directly assign the complete contents of the the BuildingBlock Entry to a string variable. Having to insert it in the document first seems like an unnecessary step.

Here's how I got around it so far.


'Trying something else...
'try to use ron DeBruin's RangetoHTML concept from here
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim rng As Word.Range
Dim RangetoHTML As Variant

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

ActiveDocument.AttachedTemplate.BuildingBlockEntries("Contract Email").Insert where:=ActiveDocument.SelectContentControlsByTag("email").Item(1).Range, RichText:=True
Set rng = ActiveDocument.SelectContentControlsByTag("email").Item(1).Range
rng.Copy
Application.Documents.Add
ActiveDocument.Range.Paste
ActiveDocument.SaveAs TempFile, wdFormatHTML
ActiveDocument.Close

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Delete the htm file
Kill TempFile

Set ts = Nothing
Set fso = Nothing

'and then in the part where I build the mail message...
with myMsg
.To = getCCValue(ActiveDocument, "conEmail")
.Subject = "Contract: " & getCCValue(ActiveDocument, "panel")
.Attachments.Add pdfName
.HTMLBody = RangetoHTML 'strBody
.BillingInformation = getCCValue(ActiveDocument, "bizName")
.Display
'.Send
end with