Microsoft Excel Webinar

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:
    VB:
    strBody = ActiveDocument.AttachedTemplate.BuildingBlockEntries("Contract Email").value 
    
    
    Formatting tags added by mark007
    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:

    VB:
     '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 
    
    
    Formatting tags added by mark007
    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.

    VB:
     '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 
    
    
    Formatting tags added by mark007

Posting Permissions

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