none
Macro to find and replace text when composing an email RRS feed

  • Question

  • As I'm composing an email in Outlook 2010 (an HTML email), I'd like to run a macro to execute a simple find and replace within the text of my email. Specifically, I send a lot of SharePoint 2007 links that contain %20 rather than a space in the displayed text of the hyperlink.

    Doing a manual find/replace of "%20" with " " does what I want (i.e., changes the displayed text but doesn't change the destination URL). So I just want a macro to automate this simple find/replace command. I tried various bits of code from other forum answers to no avail. I use Word macros a lot, and this is my first attempt with an Outlook macro.

    Thanks for your help.

    Thursday, July 10, 2014 4:54 PM

Answers

  • The Application.ActiveInspector.WordEditor object for your open email is a Word.Document object. You can use Word code after making that assignment:

    Dim oDoc As Word.Document

    Set oDoc = Application.ActiveInspector.WordEditor


    Ken Slovak MVP - Outlook

    Friday, July 11, 2014 2:46 PM
    Moderator

All replies

  • Hello Nicole,

    The Outlook object model provides three main ways for working with item bodies:

    1. The Body property. A raw text.

    2. The HTMLBody property. The body is represented by the html markup.

    2. The Word object model. The WordEditor property of the Inspector class returns and instance of the Word Document class which represents the body.

    You can read more about this in the Chapter 17: Working with Item Bodies article in MSDN. It describes all these properties in depth.

    Thursday, July 10, 2014 5:02 PM
  • Outlook doesn't have a macro recorder, so you have to write code for an Outlook macro.

    The macro could look something like this. ActiveInspector is the open window you'd use when composing an email:

    Public Sub DoSpaces

      Application.ActiveInspector.CurrentItem.Body = Replace(Application.ActiveInspector.CurrentItem.Body, "%20", " ")

    End Sub

    Take a look at http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/ for more information on running Outlook macros.


    Ken Slovak MVP - Outlook

    Thursday, July 10, 2014 6:38 PM
    Moderator
  • I tried this and something similar and both times get a Compile error on the Replace command:

    "Wrong number of arguments or invalid property assignment". Not sure why.

    Thursday, July 10, 2014 7:06 PM
  • What code did you try to run?
    Thursday, July 10, 2014 7:19 PM
  • Sub DoSpaces()
      Application.ActiveInspector.CurrentItem.body = Replace(Application.ActiveInspector.CurrentItem.body, "%20", " ")
    End Sub
    

    Thursday, July 10, 2014 7:21 PM
  • You put the code in the Outlook VBA project, opened an email, ran the code?

    I tested it just now and it worked here in Outlook 2010, in an HTML email.


    Ken Slovak MVP - Outlook

    Thursday, July 10, 2014 7:43 PM
    Moderator
  • Yup, that's what I did. Interesting...
    • Edited by Nicole_42 Thursday, July 10, 2014 7:50 PM
    Thursday, July 10, 2014 7:45 PM
  • Let's see what's going on. Try this:

    Sub DoSpaces()

      Dim s As String

      s = Application.ActiveInspector.CurrentItem.body

      Debug.Print s ' make sure Immediate window is displayed  

    s = Replace(s, "%20", " ")

     Debug.Print s 

    Application.ActiveInspector.CurrentItem.body = s

    End Sub


    Ken Slovak MVP - Outlook




    Thursday, July 10, 2014 7:50 PM
    Moderator
  • Should that do something? I ran it by pressing play in the VBA window and ran it from the email. No result both times.
    Thursday, July 10, 2014 7:53 PM
  • Does the body of your email contain any text?
    Thursday, July 10, 2014 7:57 PM
  • I am not sure, but the Body should start with a capital letter. Is this the case?
    Thursday, July 10, 2014 7:57 PM
  • Yes:

    stghtyjuiu
    
    https://projectweb.jasco.com/EditorialTeam/Writing%20Wiki/Links%20to%20Nicoles%20Stuff.aspx 
    
    

    Thursday, July 10, 2014 7:58 PM
  • Did you get the same error?

    What did you see in the Immediate window? The first Debug statement should have shown the original text in the body, the second the replaced text. Then the email should have been modified.


    Ken Slovak MVP - Outlook

    Thursday, July 10, 2014 7:59 PM
    Moderator
  • No. If I replace b with B, it reverts back to b when I move the cursor off the word.
    Thursday, July 10, 2014 7:59 PM
  • Nothing happened at all. No error, nothing.
    • Edited by Nicole_42 Thursday, July 10, 2014 8:01 PM
    Thursday, July 10, 2014 8:00 PM
  • The casing doesn't matter, it's case insensitive.

    Ken Slovak MVP - Outlook

    Thursday, July 10, 2014 8:37 PM
    Moderator
  • Did you place your cursor in the macro and press F5?

    Did you see anything in the Immediate window?

    Where did you place the code? Make sure it's in the ThisOutlookSession class module.


    Ken Slovak MVP - Outlook

    Thursday, July 10, 2014 8:39 PM
    Moderator
  • Did you have a chance to check out the Trust center settings in Outlook? Are you allowed to run macros in Outlook?
    Friday, July 11, 2014 9:20 AM
  • I moved the code from Modules > Module 1 to Microsoft Outlook Objects > ThisOutlookSession, and then ran it by pressing F5. Still nothing.

    And yes, my Trust Settings are set to allow all macros.

    Friday, July 11, 2014 12:45 PM
  • Try to set a breakpoint to the first line and run the code anew. Do you see the yellow arrow inside the red circle?

    Also I'd recommend reading the Getting Started with VBA in Outlook 2010 article in MSDN.

    Friday, July 11, 2014 1:07 PM
  • Hmm, now, the following code is giving me the same compile error ("Wrong number of arguments or invalid property assignment". on the Replace command:

    Sub DoSpaces()
     
        Dim s As String
       
        s = Application.ActiveInspector.CurrentItem.body
       
        Debug.Print s ' make sure Immediate window is displayed
       
        s = Replace(s, "%20", " ")
       
        Debug.Print s
       
        Application.ActiveInspector.CurrentItem.body = s
     
    End Sub

    And setting breakpoints doesn't help because it can't compile.

    But on the bright side, this code seems to magically work today:

    Sub DoSpaces()
      Application.ActiveInspector.CurrentItem.body = Replace(Application.ActiveInspector.CurrentItem.body, "%20", " ")

    End Sub

    But it truncates the hyperlink at the first space. The hyperlink becomes: https://projectweb.jasco.com/recorders/Shared
    Documents/AMARS OBH Family/Release Notes/00846 Process for Creating and
    Updating Release Notes.docx

    • Edited by Nicole_42 Friday, July 11, 2014 1:46 PM
    Friday, July 11, 2014 1:43 PM
  • This makes no sense. The Replace() function should work with either of the code examples. It also makes no sense that it's truncating the text and works today but didn't yesterday.

    If you look at Tools, References is VBA the first reference?

    What do you get if you try this:

    Sub TestReplace

    Dim s As String

    s = "this%20is%20a%20test"

    Debug.Print s

    s = Replace(s, "%20", " ", 1, -1, vbTextCompare)

    Debug.Print s

    End Sub

    What is shown in the Immediate window? Are there any errors?


    Ken Slovak MVP - Outlook

    Friday, July 11, 2014 2:10 PM
    Moderator
  • Tools > References shows "Visual Basic for Applications" as the first reference. The following is shown in the Immediate window and there are no errors:

    this%20is%20a%20test
    this is a test

    Friday, July 11, 2014 2:17 PM
  • Is there a way to run .Find on the message the way that it is run in Word? In Word, I would do:

    With Selection.Find
        .ClearFormatting
        .Replacement.ClearFormatting
                   
        .Text = "%20"
        .Replacement.Text = " "
        .Font.Hidden = False        ' Ignore hidden text
        .Forward = True
        .Wrap = bWrap
        .Format = False
        .MatchCase = bMatchCase
        .MatchWholeWord = bMatchWholeWord
        .MatchWildcards = bMatchWildcards
        .MatchSoundsLike = False
        .MatchAllWordForms = False
       
        Do While .Execute
            'Keep going until nothing found
            .Execute Replace:=wdReplaceAll
        Loop

    End With

    Can I change the "With Selection.Find" line to run .Find on the outlook message text?

    Friday, July 11, 2014 2:20 PM
  • OK. So that test worked correctly. It replaced all the '%20' instances with spaces.

    It should work just as well if you now change the line that set s to "this%20is%20a%20test" with this:

    s = Application.ActiveInspector.CurrentItem.Body

    assuming that you have an open email that has focus and has text in the body.

    See if that works.


    Ken Slovak MVP - Outlook

    Friday, July 11, 2014 2:43 PM
    Moderator
  • The Application.ActiveInspector.WordEditor object for your open email is a Word.Document object. You can use Word code after making that assignment:

    Dim oDoc As Word.Document

    Set oDoc = Application.ActiveInspector.WordEditor


    Ken Slovak MVP - Outlook

    Friday, July 11, 2014 2:46 PM
    Moderator