none
Find and Insert Text all at once RRS feed

  • Question

  • Hi All,

    I am stuck up in a complex situation. I have written a brief description of my problem. Please help me out.


    Sample Email-During Composing an new email

    Hi,

    The following are the order numbers. Please process them
    ASA1234567
    ASA4567812
    ASA2345678

    After Applying my Macro-During Composing a new email

    Hi,
    The following are the order numbers.Please process them
    http://www.mymacro.com/ASA1234567/ordernumber
    http://www.mymacro.com/ASA4567812/ordernumber
    http://www.mymacro.com/ASA2345678/ordernumber

    I have modified the macro accordingly and have got the following result.

    *I am able to convert a single required text to the link but not all at once. I need to run the Macro each time to convert each order number to a link.

    My Question:
    How can I do this in a single run?
    Please help me out.
    I have attached my code here which does the job one by one.

    Sub ConvertToHyperlink()


        Dim myInspector As Outlook.Inspector
        Dim myObject As Object
        Dim myItem As Outlook.MailItem

        Dim myDoc As Word.Document
        Dim mySelection As Word.Selection
        Dim strItem As String
        Dim strLink As String
              
        Set myInspector = Application.ActiveInspector
        Set myObject = myInspector.CurrentItem

        'The active inspector is displaying a mail item.
        If myObject.MessageClass = "IPM.Note" And _
            myInspector.IsWordMail = True Then
            Set myItem = myInspector.CurrentItem
            'Grab the body of the message using a Word Document object.
            Set myDoc = myInspector.WordEditor
            'myDoc.Range.Find.ClearFormatting
            Set myDocRange = ActiveDocument.Content

            Set mySelection = myDoc.Application.Selection
            With ActiveDocument.Content.Find
                .ClearFormatting
                .Text = "ASA???????"

                 .Replacement.Text = ""
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchAllWordForms = False
                .MatchSoundsLike = False
                .MatchWildcards = True
            End With
        
                '.Execute Replace:=wdReplaceAll
                'mySelection.Find.Execute Replace

            If mySelection.Find.Execute = True Then
                strItem = mySelection.Text
              

                'Mail item is in compose mode in the inspector
                If myItem.Sent = False Then
                    mySelection.InsertBefore Text:="http://www.mymacro.com/"
                    mySelection.InsertAfter Text:="/ordernumber"
                End If
                Else
                MsgBox "There is no order number in this message."
           End If
        End If
    End Sub

     

    Thanks for your time

    Thursday, June 16, 2011 1:29 AM

Answers

  • You have some duplication there. The whole thing should look like this ...
     
    Sub Convert()

        Dim myInspector     As Outlook.Inspector
        Dim myObject        As Object
        Dim myItem          As Outlook.MailItem
        Dim myDoc           As Word.Document
       
        Set myInspector = Application.ActiveInspector
        Set myObject = myInspector.CurrentItem
       
        If myObject.MessageClass = "IPM.Note" And _
           myInspector.IsWordMail = True Then
          
            Set myItem = myInspector.CurrentItem
            Set myDoc = myInspector.WordEditor
           
            With myDoc.Range.Find
                ..Text = "ASA^#^#^#^#^#^#^#"
                While ..Execute
                    ..Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & ..Parent.Text & "/ordernumber"
                    ..Parent.Collapse wdCollapseEnd
                Wend
            End With
        End If
     
    End Sub
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by MacroOutluk Thursday, June 16, 2011 3:13 PM
    Thursday, June 16, 2011 2:43 PM

All replies

  • I haven't looked at the Outlook part of your code, but for the Word part, try replacing what you have after

            Set myDoc = myInspector.WordEditor

    with this:

    With myDoc.Content.Find

      .ClearFormatting

      .Text = "(ASA???????)"

      .Replacement.Text = "http://www.mymacro.com/\1/ordernumber"

      .Forward = True

      .Wrap = wdFindContinue

      .Format = False

      .MatchCase = False

      .MatchWholeWord = False

      .MatchAllWordForms = False

      .MatchSoundsLike = False

      .MatchWildcards = True

      If Not .Execute(Replace:=wdReplaceAll) Then

        MsgBox "There is no order number in this message."

     End If

    End With


    Peter Jamieson
    Thursday, June 16, 2011 7:09 AM
  • That will replace with text, not hyperlinks. Try this instead, also after setting "myDoc":
     
        With myDoc.Range.Find
            .Text = "ASA^#^#^#^#^#^#^#"
            While ..Execute
                ..Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & ..Parent.Text & "/ordernumber"
                ..Parent.Collapse wdCollapseEnd
            Wend
        End With

    Enjoy,
    Tony
    www.WordArticles.com
    Thursday, June 16, 2011 10:18 AM
  • Whoops! Too many periods. I don't know what happened there - yet another oddity of these forums, but, assuming this comes out correctly, "..Execute" should be just ".Execute", and "..Parent" (thrice) should be just ".Parent" - in other words, no double period anywhere.
     

    Enjoy,
    Tony
    www.WordArticles.com
    Thursday, June 16, 2011 10:25 AM
  • That will replace with text, not hyperlinks. Try this instead, also after setting "myDoc":
     
        With myDoc.Range.Find
            .Text = "ASA^#^#^#^#^#^#^#"
            While ..Execute
                ..Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & ..Parent.Text & "/ordernumber"
                ..Parent.Collapse wdCollapseEnd
            Wend
        End With

    Enjoy,
    Tony
    www.WordArticles.com

    Hi Tony and Peter ,

    Thanks for the reply.

    So in this case, to convert it as a hyperlink like ASA1234567, the modified code should be like ....

    I am not sure where to put your code.

    Sub Convert()
        Dim myInspector As Outlook.Inspector
        Dim myObject As Object
        Dim myItem As Outlook.MailItem
        Dim myDoc As Word.Document
        Dim mySelection As Word.Selection
        Dim strItem As String
        Dim strLink As String
             
        Set myInspector = Application.ActiveInspector
        Set myObject = myInspector.CurrentItem

        'The active inspector is displaying a mail item.
        If myObject.MessageClass = "IPM.Note" And _
           myInspector.IsWordMail = True Then
           Set myItem = myInspector.CurrentItem
           'Grab the body of the message using a Word Document object.
           Set myDoc = myInspector.WordEditor
           myDoc.Range.Find.ClearFormatting
           Set myDocRange = ActiveDocument.Content
           Set mySelection = myDoc.Application.Selection
           mySelection.Find.ClearFormatting
           mySelection.Find.Replacement.ClearFormatting
           With mySelection.Find
           .Text = "(ASA)([a-z])([a-z])([0-9])([0-9])([0-9])([0-9])([0-9])"
           .Replacement.Text = http://www.mymacro.com/\1\2\3\4\5\6\7\8/ordernumber
           .Forward = True
           .Wrap = wdFindAsk
           .Format = False
           .MatchCase = False
           .MatchWholeWord = False
           .MatchAllWordForms = False
           .MatchSoundsLike = False
           .MatchWildcards = True
            With myDoc.Range.Find
            .Text = "ASA^#^#^#^#^#^#^#"
            While .Execute
                .Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & .Parent.Text & "/ordernumber"
                .Parent.Collapse wdCollapseEnd
            Wend
            End With
           If Not .Execute(Replace:=wdReplaceAll) Then
               MsgBox "There is no order number in this message! "
           Else
               MsgBox "Macro Executed Successfully and replaced with respective hyperlinks"
           End If
           End With
           'mySelection.Find.Execute Replace:=wdReplaceAll
        End If
    End Sub

    Thursday, June 16, 2011 2:10 PM
  • You have some duplication there. The whole thing should look like this ...
     
    Sub Convert()

        Dim myInspector     As Outlook.Inspector
        Dim myObject        As Object
        Dim myItem          As Outlook.MailItem
        Dim myDoc           As Word.Document
       
        Set myInspector = Application.ActiveInspector
        Set myObject = myInspector.CurrentItem
       
        If myObject.MessageClass = "IPM.Note" And _
           myInspector.IsWordMail = True Then
          
            Set myItem = myInspector.CurrentItem
            Set myDoc = myInspector.WordEditor
           
            With myDoc.Range.Find
                ..Text = "ASA^#^#^#^#^#^#^#"
                While ..Execute
                    ..Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & ..Parent.Text & "/ordernumber"
                    ..Parent.Collapse wdCollapseEnd
                Wend
            End With
        End If
     
    End Sub
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by MacroOutluk Thursday, June 16, 2011 3:13 PM
    Thursday, June 16, 2011 2:43 PM
  • Hi Tony,

    Thanks ...One more question

    Text = "ASA^#^#^#^#^#^#^#"
    This searches for ASA followed by seven numbers.

    What should the text look like if I always have alphabets in last two places instead of numbers(For Ex:ASA12345df).

    I tried this one

    Text="ASA^#^#^#^#^#^?^?

    But this works also for numbers in the last two places. I should make it work only with the required format ASA23456jk.

    Thanks.

    Thursday, June 16, 2011 3:26 PM
  • ^? searches for any character. ^$ should search for a letter. If you start to get much more complex than this, you will need to use Wildcards proper.
     

    Enjoy,
    Tony
    www.WordArticles.com
    Thursday, June 16, 2011 3:32 PM
  • Thanks Tony!!
    Thursday, June 16, 2011 3:59 PM
  • Hi Tony,

    Now if I need to use this Macro as VB script to search for the text and replace with hyperlinks as it was done above but during an incoming email message i.e Replace specified text with hyperlinks and store it in inbox during incoming email itself. I am planning to apply a rule in outlook to run this script before they come and sit in the inbox.

    I have modified the code with the help from outlookcode.com

    Modified code is as follows:

    Sub Convert(MyMail As MailItem)

        Dim myInspector     As Outlook.Inspector
        Dim myObject        As Object
        Dim myItem          As Outlook.MailItem
        Dim myDoc           As Word.Document
        Dim strID As String
        Dim objMail As Outlook.MailItem


        strID = MyMail.EntryID
        Set objMail = Application.Session.GetItemFromID(strID)
       
        objMail.GetInspector = Application.ActiveInspector
        Set myInspector = Application.ActiveInspector
        Set myObject = myInspector.CurrentItem
          
        If myObject.MessageClass = "IPM.Note" And _
           myInspector.IsWordMail = True Then
             Set myItem = myInspector.CurrentItem
             Set myDoc = myInspector.WordEditor
             With myDoc.Range.Find
                .Text = "ASA^#^#^#^#^#^$^$"
                While .Execute
                    .Parent.Hyperlinks.Add .Parent, "http://www.mymacro.com/" & .Parent.Text & "/ordernumber"
                    .Parent.Collapse wdCollapseEnd
                Wend
            End With
         End If
        objMail.Save
        Set objMail = Nothing
    End Sub

    This does not work. Can you help me please?

    Friday, June 17, 2011 2:19 AM
  • I am not an Outlook expert but, instinctively, I wouldn't expect you to be able to modify an incoming e-mail. Also, run as part of a Rule, you will have neither an Inspector nor a WordEditor for the e-mail to be able to run this code. In general, in fact, using ActiveInspector is not reliable unless you are running the code manually and know which e-mail is in the active Inspector, but if running on an incoming e-mail you will need to create an inspector and load the e-mail into it.
     

    Enjoy,
    Tony
    www.WordArticles.com
    Friday, June 17, 2011 6:34 AM
  • Thanks Tony..

    Can anyone else suggest me a better idea ?

    Friday, June 17, 2011 10:57 AM