Tag: mail mergeMultiple Letter Mail Merge in Word 2007August 07, 2009 I recently assisted someone with a mail-merge in Microsoft Word 2007 for a letter whose content depended on a person's interests. I started with three different components (all in the same directory):
In the Word document containing the top of the letter, I setup a mail-merge. The source data was the Excel spreadsheet. I inserted the necessary merge fields for the top of the letter. I also inserted the area of interest field surrounded by four equal signs ( ====<< Area_Of_Interest >>==== In the same document I wrote a macro to do the following:
Sub InsertLetter()
Dim dept
Dim aRange
Dim done
Dim filename
'Execute mail merge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
'Search entire document for special tags
done = False
Selection.SetRange ActiveDocument.Range.Start, ActiveDocument.Range.End
'Continue searching document until no more tags are found
Do While (Not done)
'Execute search
With Selection.Find
.Text = "====*===="
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
'If tag is found, get value of tag
If (Selection.Find.Found) Then
dept = ActiveWindow.ActivePane.Selection
dept = Left(dept, Len(dept) - 4)
dept = Right(dept, Len(dept) - 4)
filename = dept + ".doc"
'Verify document to insert (replacing tag) exists and insert
If (Dir(filename) <> "") Then
ActiveWindow.ActivePane.Selection = ""
Set aRange = ActiveWindow.ActivePane.Selection
aRange.Start = aRange.End
aRange.InsertFile filename:=filename
'Display and put error message in document if file does not exist
Else
MsgBox "No letter found for department: " + dept, vbExclamation
ActiveWindow.ActivePane.Selection = "No letter found for department: " _
+ dept
End If
'Update search range to search the rest of the document
Selection.SetRange Selection.End, ActiveDocument.Range.End
Else
done = True
End If
Loop
End Sub
Categories: Development Tags: excel, mail merge, word |
SearchCategories
TagsR acm apt awk bash benchwork bibtex c compiz dd-wrt dia dvd eeebuntu eeepc eps excel fedora firefox flash freebsd gnome hostname iptables java kde latex mac os x mail merge mediawiki mercurial mrtg nvidia pdf phidgets printer putty pxe python route scenery sed snmp ssh structures subnet subversion sudo ubuntu vegetarian vim visio windows word wrt54gl x11 yum Years |