Excel Hints

Results 1 to 3 of 3

Thread: Building Block to VBA String Variable

  1. #1

    Building Block to VBA String Variable

    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:
    [VBA]strBody = ActiveDocument.AttachedTemplate.BuildingBlockEntries("Contract Email").value[/VBA]

    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:

    [VBA]'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[/VBA]

    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?

  2. #2
    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.

  3. #3
    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.

    [VBA]
    '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 xublishsource=", _
    "align=left xublishsource=")

    '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
    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •