mailmerge event

[caroline]

Hi

I have a template with a table and a merge this template with my data. If
there is a value for a field in a cell the border is visible and the shading
is grey. If there is no value the border and shading are not visible.
I have some trouble to run my macro on the mailmerge event. I would like to
run the macro when i use the "mail merge" button in my tool bar.

I am not technical.... could you tell me what is wrong in my macro?

Thank you

Sub caro()
'
' test Macro
' Macro créée le 24/11/2007 par caro

With ActiveDocument.MailMerge

Dim intCount As Integer
Dim limCount As Integer

With ActiveDocument.MailMerge.DataSource

.ActiveRecord = wdLastRecord
intCount = .ActiveRecord

'Set the active record equal to the first record in the data source
.ActiveRecord = wdFirstRecord
limCount = .ActiveRecord

Do While limCount <= intCount

'Set the condition that will change the border and shading of a
specific cell
If .DataFields("CodeRel").Value = 1 Then

Set mycell = ActiveDocument.Tables(1).Cell(Row:=4,
Column:=6)
mycell.Range.Text = OrigSeqNum
mycell.Shading.BackgroundPatternColor = wdColorGray20
mycell.Borders.Enable = True

End If

Set mycell = ActiveDocument.Tables(1).Cell(Row:=4,
Column:=6)
mycell.Range.Text = ""
mycell.Shading.BackgroundPatternColor = wdColorWhite
mycell.Borders.Enable = False

'Move the record to the next record in the data source

.ActiveRecord = wdNextRecord
limCount = limCount + 1

Loop

End With

End With

End Sub

 

[Doug Robbins - Word MVP]

Hi Caroline,

Using Mail Merge Events is a bit more complicated than that. Take a look at
the addin that you can download from

See the "Individual Merge Letters" item on fellow MVP Graham Mayor's website
at:

http://www.gmayor.com/individual_merge_letters.htm

In addition, as I would assume that you do not want this (the cell
shading/border) to happen every time you create a merge if you were to
incorporate the code into a Mail Merge Event, you would have to be asking
the user if they wanted it to happen.

As an alternative, I would execute the merge to a new document and then run
a macro over that document that checked for the contents of Cell(4, 6) in
the first table in each section of that document and if it contained the
value 1, then applied the formatting to that cell. The following code
should do that.

Dim i As Long, Doc As Document, crange As Range

Set Doc = ActiveDocument
For i = 1 To Doc.Sections.Count
Set crange = Doc.Sections(i).Range.Tables(1).Cell(4, 6).Range
If Val(crange.Text) = 1 Then
With crange
.Text = OrigSeqNum 'I don't know what OrigSeqNum is
.Cells(1).Shading.BackgroundPatternColor = wdColorGray20
.Cells(1).Borders.Enable = True
End With
Else
With crange
.Cells(1).Shading.BackgroundPatternColor = wdColorWhite
.Cells(1).Borders.Enable = False
End With
End If
Next i



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP