Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA for MS Word to clean up Document with formatting

Posted on 2014-09-27
5
Medium Priority
?
93 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
  • 2
3 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

877 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