Automatically copying VBA to mail merged document

[lukestkd]

Hello all,
I have a set of code that colours cells based on their values (MMY/MMI/MMN), to show students whether they have completed a task or not. The code is stored in the mail merge document itself, but when I run the merge and the new 'letters' are created the code has to be manually copied into the VBA window. Is there a way of automatically inserting the VBA into the mail merged letters as some non-techhies will be using this system!


Many thanks


Luke

Sub HighlightTargetsMMN()
Dim Rng As Range, i As Long, TargetList
TargetList = Array("MMN") ' put list of terms to find here
For i = 0 To UBound(TargetList)
  Set Rng = ActiveDocument.Range
  With Rng
    With .Find
      .Text = TargetList(i)
      .Format = True
      .MatchCase = True
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Forward = True
      .Wrap = wdFindStop
      .Execute
    End With
    Do While .Find.Found
      .HighlightColorIndex = wdRed
      With .Font
        .Bold = True
        .ColorIndex = wdRed
        .Name = "TW Cen MT"
        .Size = 14
      End With
      If .Information(wdWithInTable) = True Then
        .Cells(1).Shading.BackgroundPatternColorIndex = wdRed
      End If
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub
Sub HighlightTargetsMMI()
Dim Rng As Range, i As Long, TargetList
TargetList = Array("MMI") ' put list of terms to find here
For i = 0 To UBound(TargetList)
  Set Rng = ActiveDocument.Range
  With Rng
    With .Find
      .Text = TargetList(i)
      .Format = True
      .MatchCase = True
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Forward = True
      .Wrap = wdFindStop
      .Execute
    End With
    Do While .Find.Found
      .HighlightColorIndex = wdYellow
      With .Font
        .Bold = True
        .ColorIndex = wdYellow
        .Name = "TW Cen MT"
        .Size = 14
      End With
      If .Information(wdWithInTable) = True Then
        .Cells(1).Shading.BackgroundPatternColorIndex = wdYellow
      End If
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub
Sub HighlightTargetsMMY()
Dim Rng As Range, i As Long, TargetList
TargetList = Array("MMY") ' put list of terms to find here
For i = 0 To UBound(TargetList)
  Set Rng = ActiveDocument.Range
  With Rng
    With .Find
      .Text = TargetList(i)
      .Format = True
      .MatchCase = True
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Forward = True
      .Wrap = wdFindStop
      .Execute
    End With
    Do While .Find.Found
      .HighlightColorIndex = wdBrightGreen
      With .Font
        .Bold = True
        .ColorIndex = wdGreen
        .Name = "TW Cen MT"
        .Size = 14
      End With
      If .Information(wdWithInTable) = True Then
        .Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen
      End If
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub


Sub fullmacros()
HighlightTargetsMMN
HighlightTargetsMMY
HighlightTargetsMMI
End Sub

cross posted on excel forum (unable to post link)

[macropod]

I gather you want to change some formatting in the mailmerge output, but I wonder why you're not handling that via field coding as part of the merge itself. That way, everything would be handled in one go and you wouldn't need the macros.

Your macros are also strange on their own account - I can see no reason for having single-element arrays and loops to process them. The whole lot could be handled with a single macro using either one of the existing loops or simply a sequence of F/R expressions.

[lukestkd]

Hi Paul,
The macros are quite hashed with my limited knowledge of VBA. The macros above colour the cells based on one of three specific sets of text (MMY, MMI, MMN) and then colour the text the same to essentially hide it - creating a traffic light system that is clear for students. Is there a way to get this code into the letters created when the document is merged? Thank you for your prompt reply. Luke

[macropod]

All you should need is three nested fields, in the relevant cell(s), coded as:

{IF{MERGEFIELD MM}= "MMY" "→↵
→" \* Charformat}{IF{MERGEFIELD MM}= "MMI" "→↵
→" \* Charformat}{IF{MERGEFIELD MM}= "MMN" "→↵
→" \* Charformat}

where the:
• → is an actual tab character
• ↵ is a manual line break
• each I in IF is highlighted with the relevant highlight colour
• MM is the data field name
and each cell to be shaded has:
• margins set to 0 all round
• paragraph formatting with a single right-aligned tab-stop set to at least the cell width


Note: The field brace pairs (i.e. '{ }') for the above demo field codes are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

[lukestkd]

Hi Paul,
Apologies for the delay in replying to you - I have been away on spring vacation.

I have tried to 'debunk' your last piece of code - but am really unsure how to structure it in vba/what else I need to add with it. Would you be able to give some further advice? I really am a VBA novice!

Many thanks

Luke

[macropod]

There is NO VBA in what I posted!!!! It's field coding. Just follow the instructions - they're clear enough...

As for:

I have tried to 'debunk' your last piece of code

I'm not sure how I'm supposed to take that.

[lukestkd]

Hi Paul,
I meant understand by debunk.

I'll give it a go and report back to you.

Cheers

Luke

[macropod]

Cross-posted at: http://www.excelforum.com/word-progr...d-letters.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

IMHO it's extremely rude of you to go cross-posting elsewhere after you've already been given an answer here!

[lukestkd]

I'll post this twice as is the fashion set. Difference of opinion. I am struggling to understand your solution and your last post advised 'its easy', which I am not finding it. Any other solutions welcome.