Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA for MS Word to clean up Document with formatting

Posted on 2014-09-27
5
Medium Priority
?
87 Views
Last Modified: 2016-05-25
Request

I have to send about 15-20 emails that are copied from a MS Word 2010 Document – the documents are not formatted and would like to do the following via a simple macro that I can run.

Body Text – Arial 9 Black
Carriage Returns (paragraph mark) removed for Manual Line (Soft Returns)
Hyperlinks are in Arial 9 Blue
Following text is Arial 9 bold “+1 (212) 555-1212 phone”
Following text is Arial 8 grey color “insert signature line here of department”

Insert New Text at bottom of every document after one manual line “today’s date – sent by John Doe”

Any assistance would be much appreciated.
0
Comment
Question by:Code Web
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 40348418
This shows how to do most of the items that you are having difficulty with. They may not work exactly as you are aiming for, but with a bit of tweaking, you should get there.
Sub ReFormat()
    Dim hyp As Hyperlink
    With ActiveDocument
        With .Range
         'set font for document body
            With .Font
                .Name = "Arial"
                .Size = 9
                .Color = wdColorAutomatic 'default colout, usually black
            End With
        End With
        
        'set colour for hyperlinks
        For Each hyp In .Hyperlinks
            hyp.Range.Font.Color = wdColorBlue
        Next hyp
        
        'set font for particular text
        With .Range.Find
            .Text = "+1 (212) 555-1212 phone"
            With .Replacement.Font
                .Bold = True
                .Name = "Arial"
                .Size = 9
            End With
            .Execute Replace:=wdReplaceAll
        End With
        
        'set font for a different piece of text
         With .Range.Find
            .Text = "insert signature line here of department"
            With .Replacement.Font
                .Bold = True
                .Name = "Arial"
                .Color = wdColorGray25
            End With
            .Execute Replace:=wdReplaceAll
        End With
        
        'add footer text
        .Range.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = _
               vbCr & "today’s date – sent by John Doe"
    End With
End Sub

Open in new window

It isn't entirely clear what you want done with paragraphs. You can remove or display the pilcrow symbol (¶). with the Show/ Hide button of the Home tab. If that isn't what you were trying to do, could you explain further please. Posting a sample document might be helpful.
0
 

Author Comment

by:Code Web
ID: 40348511
Thanks - that was perfect.  Sorry was unable to explain before but when we send the emails we remove the         .Text = "^p" with      .Replacement.Text = "^l"  However at the end it creates another line for some weird reason.

Sub Gmail()
    Selection.WholeStory
    Selection.Find.Replacement.ClearFormatting
    Selection.Font.Size = 9
    Selection.Font.Name = "Arial"
    Selection.Font.Color = wdColorBlack
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = "^l"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40348544
You have two Find and Replace operations in that macro. This first appears to do nothing. The second will replace all paragraph marks with new-line characters, thus making all the text into one paragraph, which is terminated with a paragraph mark. This cannot be removed, since any text in a Word document must be part of a paragraph.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question