none
Help with Macro to format message but exclude URL RRS feed

  • Question

  • Hi,

    I'm new here and fairly new to writing in VBA, I am trying to write a macro to format my e-mail message text to a company standard font size/colour but I want this to exclude any hyperlink that may be in the message body. The usual links are either our e-mail address or website.

    So far this is the only way I thought to do it, and it does work but only if a link is in the message body. If I use this over text with no link then it formats the whole message body to blue/underlined..

    Any ideas?

    Thanks in advance


    Public Sub FormatText()

     

    'Refer to Word Object Library'

      Dim Ins As Outlook.Inspector

      Dim Document As Word.Document

      Dim Word As Word.Application

      Dim Selection As Word.Selection

     

      Set Ins = Application.ActiveInspector

      Set Document = Ins.WordEditor

      Set Word = Document.Application

      Set Selection = Word.Selection

     

    'Change all text to standard format for e-mail'

       Selection.Font.Name = "Calibri"

        Selection.Font.Size = 11

        Selection.Font.Color = wdColorBlack

       

    'If our e-mail address is formatted, change back to standard hyperlink format

     

      With Selection.Find

            .Text = "EMAILADDRESS"

            .Replacement.Text = ""

            .Forward = True

            .Wrap = wdFindContinue

            .Format = False

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

        Selection.Find.Execute

        Selection.Font.Color = wdColorBlue

        Selection.Font.UnderlineColor = wdColorAutomatic

        Selection.Font.Underline = wdUnderlineSingle

       

    'Repeat the same if website is included'

     

      With Selection.Find

            .Text = "WEBSITE"

            .Replacement.Text = ""

            .Forward = True

            .Wrap = wdFindContinue

            .Format = False

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

        Selection.Find.Execute

        Selection.Font.Color = wdColorBlue

        Selection.Font.UnderlineColor = wdColorAutomatic

        Selection.Font.Underline = wdUnderlineSingle

     

     ' Select all text and copy to clipboard'

     

       Selection.WholeStory

       Selection.Copy

      

    End Sub

    Tuesday, October 8, 2013 7:55 PM

Answers

  • Hi ErgoSW,
    Since you want to restore the styles of all links, I recommend you to loop all links in current document for a workaround.

    Here is a sample for your reference:

    Public Sub FormatText()
      Dim Ins As Outlook.Inspector
      Dim Document As Word.Document
      Dim Word As Word.Application
      Dim Selection As Word.Selection
      Dim i As Integer
      Set Ins = Application.ActiveInspector
      Set Document = Ins.WordEditor
      Set Word = Document.Application
      Set Selection = Word.Selection
      Selection.WholeStory
      Selection.Font.Name = "Calibri"
      Selection.Font.Size = 11
      Selection.Font.Color = wdColorBlack
    
      For i = 1 To Selection.Hyperlinks.Count
            Selection.Hyperlinks(i).Range.Font.Color = wdColorBlue
            Selection.Hyperlinks(i).Range.Font.UnderlineColor = wdColorAutomatic
            Selection.Hyperlinks(i).Range.Font.Underline = wdUnderlineSingle
      Next i
      Selection.WholeStory
      Selection.Copy
    End Sub

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 11, 2013 4:56 AM
    Moderator