Advice for newbies VBA required mail merges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Parisa - 16 May 2005 18:21 GMT

I've been doing a lot of mail merges that require special tweaking with
macros like taking a document and splitting & saving a document into separate
files with the added difficult of having the filename come from fields within
the document. I'm still working on that one.

Anyway, I'm not macro or VB proficient. So far I've borrowed code from this
newsgroup. Are there any sites or books that specialized in VBA mail merges
for newbies? I'm having a tough time understanding the code I'm borrow from
the experts. I need step by step instruction to begin with.
Thanks :)

Reply to this Message

Doug Robbins - 16 May 2005 19:45 GMT

See the article "What do I do with macros sent to me by other newsgroup
readers

to help me out?" at:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Signature

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

> I've been doing a lot of mail merges that require special tweaking with
> macros like taking a document and splitting & saving a document into
[quoted text clipped - 11 lines]
> the experts. I need step by step instruction to begin with.
> Thanks :)

Reply to this Message

Graham Mayor - 16 May 2005 21:36 GMT

See http://www.gmayor.com/installing_macro.htm

As for the code to split a merge using field information, there are a couple
of possibilities - one would be to insert an extra field (or combination of
fields to make a single 'word') that provides the (unique!) filename as the
first thing on the page of the merge document, then run the following
variation on Doug's splitter macro which uses that field or combination to
name the file then deletes it.

Sub SplitMergeLetter()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
'
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
sName = Selection
'set path below
sPath = "D:\My Documents\Test\Merge\"
Docname = sPath & sName & LTrim$(Str$(Counter))
ActiveDocument.Sections.First.Range.Cut
Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub

You'll find alternative splitting code on my web site.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I've been doing a lot of mail merges that require special tweaking
> with macros like taking a document and splitting & saving a document
[quoted text clipped - 7 lines]
> to begin with.
> Thanks :)

Reply to this Message

Parisa - 19 May 2005 01:39 GMT

I'm an idiot. I finally figure it out. Thank You . I changed the code
slightly. I removed the index at the end. I have a space at the end but I'm
trying figure out how to remove the space. I'll figure it out yet.

I recorded the following macro.

With ActiveDocument
.TrackRevisions = True
.PrintRevisions = True
.ShowRevisions = True
End With
End Sub

I tried to incorporate this code into the code below. I added it between
ActiveDocument.Sections.First.Range.Cut and Documents.Add.

ActiveDocument.Sections.First.Range.Cut
ActiveDocument.TrackRevisions = True
ActiveDocument.PrintRevisions = True
ActiveDocument.ShowRevisions = True
Documents.Add

It turned on track changes but it didn't create each new document. What am I
doing wrong?

Sub SplitMergeLetter()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
'
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
sName = Selection
'set path below
sPath = "C:\MERGELETTERS\"
Docname = sPath & sName
ActiveDocument.Sections.First.Range.Cut

Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub

> See http://www.gmayor.com/installing_macro.htm
>
[quoted text clipped - 51 lines]
> > to begin with.
> > Thanks :)

Reply to this Message

Parisa - 19 May 2005 21:28 GMT

I'm not sure if I should start a new thread or if this issue closed. Can
someone direct me please.

Thanks :0)

> I'm an idiot. I finally figure it out. Thank You . I changed the code
> slightly. I removed the index at the end. I have a space at the end but I'm
[quoted text clipped - 110 lines]
> > > to begin with.
> > > Thanks :)

Reply to this Message

Doug Robbins - 20 May 2005 12:21 GMT

Exactly what is it that you are trying to do? Where did the track changes
come into it?

Signature

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

> I'm not sure if I should start a new thread or if this issue closed. Can
> someone direct me please.
[quoted text clipped - 122 lines]
>> > > to begin with.
>> > > Thanks :)

Reply to this Message

Parisa - 20 May 2005 13:15 GMT

When the user opens the file(s) I want to have the track changes feature
already enabled. I realize most users know how to enable it but my "users"
(attorneys) don't have a clue. Believe me.
Since the below code works perfectly. I wanted add additional code to enable
the track changes (revisions) feature before the file is saved.
I was trying to add it myself but it just doesn't work. Please help.

Sub SplitMergeLetter()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
'
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
sName = Selection
'set path below
sPath = "C:\MERGELETTERS\"
Docname = sPath & sName
ActiveDocument.Sections.First.Range.Cut

Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub

> Exactly what is it that you are trying to do? Where did the track changes
> come into it?
[quoted text clipped - 125 lines]
> >> > > to begin with.
> >> > > Thanks :)

Reply to this Message

Doug Robbins - 21 May 2005 06:47 GMT

Try the following modification

Sub SplitMergeLetter()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
'
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
sName = Selection
'set path below
sPath = "C:\MERGELETTERS\"
Docname = sPath & sName
ActiveDocument.Sections.First.Range.Cut

Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
With ActiveDocument
.TrackRevisions = True
.PrintRevisions = True
.ShowRevisions = True
.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
End With
ActiveDocument ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub

As you can tell from the date, that is a really old macro and this is the
way that I would do the basis splitting of the document today:

' Macro created by Doug Robbins to save each letter created by a mailmerge
as a separate file.

Dim i As Long, Source as Document, Target as Document, Letter as Range
Set Source = ActiveDocument
For i = 1 to Source.Sections.Count
Set Letter = Source.Sections(i).Range
Letter.End=Letter.End-1
Set Target = Documents.Add
Target.Range=Letter
Target.SaveAs FileName:="Letter" & i
Target.Close
Next i

Signature

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

> When the user opens the file(s) I want to have the track changes feature
> already enabled. I realize most users know how to enable it but my "users"
[quoted text clipped - 183 lines]
>> >> > > to begin with.
>> >> > > Thanks :)

Reply to this Message

Parisa - 23 May 2005 20:32 GMT

I did try using the Sub SplitMergeLetter() with te track changes on but it
doesn't work. I get an error message. It says Compile error. Expected
function or variable.

It highlighted this statement " ActiveDocument ActiveWindow.Close"

> Try the following modification
>
[quoted text clipped - 241 lines]
> >> >> > > to begin with.
> >> >> > > Thanks :)

Reply to this Message

Doug Robbins - 24 May 2005 07:10 GMT

Seems like something got deleted, instead of

ActiveDocument ActiveWindow.Close

it should have been

ActiveDocument.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
ActiveWindow.Close

Signature

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

>I did try using the Sub SplitMergeLetter() with te track changes on but it
> doesn't work. I get an error message. It says Compile error. Expected
[quoted text clipped - 259 lines]
>> >> >> > > to begin with.
>> >> >> > > Thanks :)

Reply to this Message