Solved

VBA for MS Word to clean up Document with formatting

Posted on 2014-09-27
5
79 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 500 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

623 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