letters to contain multiple(# varying) addresses

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

Hi,
I'm looking to automate the generation of several hundred letters from data
in an access database. The problem is there can be anywhere from 1 - 6 names
and addresses that the letter is addressed to. I can get one address on the
letter via my vba code from access, no problem. It's the multiple addresses I
cannot find a way to do.
Does anybody know if I can create a word table to insert a variable number
of name and address data into that will fill the table from left to right
then down for a 2 column table?
Frankly, this sounds impossible to me at this point.

TIA,
--
MorningStarFan

Doug Robbins - Word MVP · External Since: Jul 14, 2006 Posts: 2843

How is the data arranged in your database?

--
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

"MorningStarFan" wrote in message

> Hi,
> I'm looking to automate the generation of several hundred letters from
> data
> in an access database. The problem is there can be anywhere from 1 - 6
> names
> and addresses that the letter is addressed to. I can get one address on
> the
> letter via my vba code from access, no problem. It's the multiple
> addresses I
> cannot find a way to do.
> Does anybody know if I can create a word table to insert a variable number
> of name and address data into that will fill the table from left to right
> then down for a 2 column table?
> Frankly, this sounds impossible to me at this point.
>
> TIA,
> --
> MorningStarFan

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

The data is all in one table in separate fields as below:

ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
AddressLine5 AddressLine6 FundName

Can the table be created with VBA and automation that I described? One that
will work for 1-6 name and address combinations per letter?

TIA,
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> How is the data arranged in your database?
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > Hi,
> > I'm looking to automate the generation of several hundred letters from
> > data
> > in an access database. The problem is there can be anywhere from 1 - 6
> > names
> > and addresses that the letter is addressed to. I can get one address on
> > the
> > letter via my vba code from access, no problem. It's the multiple
> > addresses I
> > cannot find a way to do.
> > Does anybody know if I can create a word table to insert a variable number
> > of name and address data into that will fill the table from left to right
> > then down for a 2 column table?
> > Frankly, this sounds impossible to me at this point.
> >
> > TIA,
> > --
> > MorningStarFan
>
>
>

Doug Robbins - Word MVP · External Since: Jul 14, 2006 Posts: 2843

Not quite clear to me,

Does a field such as AddressLine1 contain the name AND address details of an
addressee? Or, are AddressLine1 through AddressLine6 parts of the one
address?

--
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

"MorningStarFan" wrote in message

> The data is all in one table in separate fields as below:
>
> ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> AddressLine5 AddressLine6 FundName
>
> Can the table be created with VBA and automation that I described? One
> that
> will work for 1-6 name and address combinations per letter?
>
> TIA,
> --
> MorningStarFan
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> How is the data arranged in your database?
>>
>> --
>> 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
>>
>> "MorningStarFan" wrote in
>> message
>>
>> > Hi,
>> > I'm looking to automate the generation of several hundred letters from
>> > data
>> > in an access database. The problem is there can be anywhere from 1 - 6
>> > names
>> > and addresses that the letter is addressed to. I can get one address on
>> > the
>> > letter via my vba code from access, no problem. It's the multiple
>> > addresses I
>> > cannot find a way to do.
>> > Does anybody know if I can create a word table to insert a variable
>> > number
>> > of name and address data into that will fill the table from left to
>> > right
>> > then down for a 2 column table?
>> > Frankly, this sounds impossible to me at this point.
>> >
>> > TIA,
>> > --
>> > MorningStarFan
>>
>>
>>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

AddressLine1 - 6 are all parts of the same address.
an example row from the table is a such:

0064694 Dr. Steve J. Adelf 7523 East 128th Street Tulsa OK 74143 Adelf
Scholarship for Engineering and Natural Sciences
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> Not quite clear to me,
>
> Does a field such as AddressLine1 contain the name AND address details of an
> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
> address?
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > The data is all in one table in separate fields as below:
> >
> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> > AddressLine5 AddressLine6 FundName
> >
> > Can the table be created with VBA and automation that I described? One
> > that
> > will work for 1-6 name and address combinations per letter?
> >
> > TIA,
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> How is the data arranged in your database?
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> > Hi,
> >> > I'm looking to automate the generation of several hundred letters from
> >> > data
> >> > in an access database. The problem is there can be anywhere from 1 - 6
> >> > names
> >> > and addresses that the letter is addressed to. I can get one address on
> >> > the
> >> > letter via my vba code from access, no problem. It's the multiple
> >> > addresses I
> >> > cannot find a way to do.
> >> > Does anybody know if I can create a word table to insert a variable
> >> > number
> >> > of name and address data into that will fill the table from left to
> >> > right
> >> > then down for a 2 column table?
> >> > Frankly, this sounds impossible to me at this point.
> >> >
> >> > TIA,
> >> > --
> >> > MorningStarFan
> >>
> >>
> >>
>
>
>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

fyi: my example only had data in AddressLine1 and 2 in this case. The data
varies as addresses vary. Some addresses have 6 lines ...but that is rare.
Most will only have two.
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> Not quite clear to me,
>
> Does a field such as AddressLine1 contain the name AND address details of an
> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
> address?
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > The data is all in one table in separate fields as below:
> >
> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> > AddressLine5 AddressLine6 FundName
> >
> > Can the table be created with VBA and automation that I described? One
> > that
> > will work for 1-6 name and address combinations per letter?
> >
> > TIA,
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> How is the data arranged in your database?
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> > Hi,
> >> > I'm looking to automate the generation of several hundred letters from
> >> > data
> >> > in an access database. The problem is there can be anywhere from 1 - 6
> >> > names
> >> > and addresses that the letter is addressed to. I can get one address on
> >> > the
> >> > letter via my vba code from access, no problem. It's the multiple
> >> > addresses I
> >> > cannot find a way to do.
> >> > Does anybody know if I can create a word table to insert a variable
> >> > number
> >> > of name and address data into that will fill the table from left to
> >> > right
> >> > then down for a 2 column table?
> >> > Frankly, this sounds impossible to me at this point.
> >> >
> >> > TIA,
> >> > --
> >> > MorningStarFan
> >>
> >>
> >>
>
>
>

Doug Robbins - Word MVP · External Since: Jul 14, 2006 Posts: 2843

OK, Show us the code that you have now. Better to advise you how to modify
that than start from scratch.

--
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

"MorningStarFan" wrote in message

> fyi: my example only had data in AddressLine1 and 2 in this case. The data
> varies as addresses vary. Some addresses have 6 lines ...but that is rare.
> Most will only have two.
> --
> MorningStarFan
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> Not quite clear to me,
>>
>> Does a field such as AddressLine1 contain the name AND address details of
>> an
>> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
>> address?
>>
>> --
>> 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
>>
>> "MorningStarFan" wrote in
>> message
>>
>> > The data is all in one table in separate fields as below:
>> >
>> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
>> > AddressLine5 AddressLine6 FundName
>> >
>> > Can the table be created with VBA and automation that I described? One
>> > that
>> > will work for 1-6 name and address combinations per letter?
>> >
>> > TIA,
>> > --
>> > MorningStarFan
>> >
>> >
>> > "Doug Robbins - Word MVP" wrote:
>> >
>> >> How is the data arranged in your database?
>> >>
>> >> --
>> >> 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
>> >>
>> >> "MorningStarFan" wrote in
>> >> message
>> >>
>> >> > Hi,
>> >> > I'm looking to automate the generation of several hundred letters
>> >> > from
>> >> > data
>> >> > in an access database. The problem is there can be anywhere from 1 -
>> >> > 6
>> >> > names
>> >> > and addresses that the letter is addressed to. I can get one address
>> >> > on
>> >> > the
>> >> > letter via my vba code from access, no problem. It's the multiple
>> >> > addresses I
>> >> > cannot find a way to do.
>> >> > Does anybody know if I can create a word table to insert a variable
>> >> > number
>> >> > of name and address data into that will fill the table from left to
>> >> > right
>> >> > then down for a 2 column table?
>> >> > Frankly, this sounds impossible to me at this point.
>> >> >
>> >> > TIA,
>> >> > --
>> >> > MorningStarFan
>> >>
>> >>
>> >>
>>
>>
>>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

I have tried several approaches to this. Below is the code that works to
produce the letters with only one addressee (donor) name and address.
************************************************************
Function MergeIt()
Dim objWord As Word.Document
Set objWord =
GetObject("W:\Stewardship_Automation\Stewardship_Automation_That_Works\Office
of Institutional Advancement_ENDS_TEMPLATE2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the test db.
objWord.MailMerge.OpenDataSource _
Name:="W:\Stewardship_Automation\Stewardship_Automation_That_Works" & _
"StewardshipENDSCleanupDatabase.mdb", _
LinkToSource:=True, _
Connection:="TABLE PMKENDS_2", _
SQLStatement:="SELECT * FROM [PMKENDS_2]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function
***********************************************************

I also am working on writing code that uses more vba to loop through my
recordset and get the multiple addressees but I don't know how to get the
multiples on the letter in the form of an address. My code will only spit out
the data in rows in my table with the data arranged like this

name addressline1 addressline2 ... city, state zip
If you want to see that code too, I'll have finish modifying it. It's only
partially done.
I know how it will work though as it's a copy of the code I used for the
recipient reports and I'm modifying it to work for the letters as well.
Hopefully, anyways!

TIA,
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> OK, Show us the code that you have now. Better to advise you how to modify
> that than start from scratch.
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > fyi: my example only had data in AddressLine1 and 2 in this case. The data
> > varies as addresses vary. Some addresses have 6 lines ...but that is rare.
> > Most will only have two.
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> Not quite clear to me,
> >>
> >> Does a field such as AddressLine1 contain the name AND address details of
> >> an
> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
> >> address?
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> > The data is all in one table in separate fields as below:
> >> >
> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> >> > AddressLine5 AddressLine6 FundName
> >> >
> >> > Can the table be created with VBA and automation that I described? One
> >> > that
> >> > will work for 1-6 name and address combinations per letter?
> >> >
> >> > TIA,
> >> > --
> >> > MorningStarFan
> >> >
> >> >
> >> > "Doug Robbins - Word MVP" wrote:
> >> >
> >> >> How is the data arranged in your database?
> >> >>
> >> >> --
> >> >> 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
> >> >>
> >> >> "MorningStarFan" wrote in
> >> >> message
> >> >>
> >> >> > Hi,
> >> >> > I'm looking to automate the generation of several hundred letters
> >> >> > from
> >> >> > data
> >> >> > in an access database. The problem is there can be anywhere from 1 -
> >> >> > 6
> >> >> > names
> >> >> > and addresses that the letter is addressed to. I can get one address
> >> >> > on
> >> >> > the
> >> >> > letter via my vba code from access, no problem. It's the multiple
> >> >> > addresses I
> >> >> > cannot find a way to do.
> >> >> > Does anybody know if I can create a word table to insert a variable
> >> >> > number
> >> >> > of name and address data into that will fill the table from left to
> >> >> > right
> >> >> > then down for a 2 column table?
> >> >> > Frankly, this sounds impossible to me at this point.
> >> >> >
> >> >> > TIA,
> >> >> > --
> >> >> > MorningStarFan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

The most pertinent part of the vba routine that I have written is below- the
part that creates the table... I won't need the heading so I'm removing it.
**********************************************************
Set rs = dbs.OpenRecordset("SELECT XPERSON_MAIL_WRAP, XPERSON_ADDRESS_2,
XPERSON_ADDRESS_3, XPERSON_ADDRESS_4, XPERSON_ADDRESS_5, XPERSON_ADDRESS_6,
CITY, STATE, ZIP, SRK_ID FROM PMKENDS_2 WHERE SRK_ID = '" & sSRK_ID & "'",
dbOpenDynaset)
Do While Not rs.EOF
irows = irows + 4
rs.MoveNext
Loop
irows = irows + 1
.ParagraphFormat.Alignment =
wdAlignParagraphCenter
.InlineShapes.AddPicture
FileName:="W:\Stewardship_Automation\New Picture.png", LinkToFile:=False, _
SaveWithDocument:=True
.TypeParagraph
.TypeParagraph
.Font.Size = 18
.ParagraphFormat.Alignment = 1
.Font.Bold = True
.Font.Italic = True
.Font.SmallCaps = True
.TypeText Text:="OFFICE OF INSTITUTIONAL
ADVANCEMENT"
.TypeParagraph
.TypeParagraph
.TypeParagraph
.Font.SmallCaps = False
.Font.Size = 14
.Font.Bold = True
.Font.Italic = False
.ParagraphFormat.Alignment =
wdAlignParagraphLeft
.TypeText Text:="2006-2007 RECIPIENT
INFORMATION"
.TypeParagraph
.TypeParagraph
.Font.Size = 9
sTemp = sName & vbTab & sADDRESS_2 & vbTab
& sADDRESS_3 & vbTab & sADDRESS_4 & vbTab & sADDRESS_5 & vbTab & sADDRESS_6 &
vbTab & sCITY & vbTab & sSTATE & vbTab & sZIP
'Insert a heading on the string
sTemp = "NAME" & vbTab & "ADDRESS" & vbTab &
vbTab & vbTab & vbTab & vbTab & "CITY" & vbTab & "STATE" & vbTab & "ZIP" &
vbCrLf & sTemp
'Insert the table text into the word document
.Font.Bold = True
Set oRange = .Sections(1).Range
With oRange
.MoveEnd Unit:=wdCharacter, Count:=-1
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
.InsertAfter "End of section"
End With
oRange.Text = sTemp
'Convert the text to a table and format the
table
oRange.ConvertToTable vbTab, , , ,
wdAutoFitFixed, , , , , , , , , , , wdWord9TableBehavior
'.MoveDown Unit:=wdLine, Count:=irows
With .Tables(1)
.Style = "Table Elegant"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True

End With

.MoveDown Unit:=wdLine, Count:=irows
.InsertBreak Type:=wdPageBreak
rs.Close
End With

***********************************************************
HTH,

--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> OK, Show us the code that you have now. Better to advise you how to modify
> that than start from scratch.
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > fyi: my example only had data in AddressLine1 and 2 in this case. The data
> > varies as addresses vary. Some addresses have 6 lines ...but that is rare.
> > Most will only have two.
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> Not quite clear to me,
> >>
> >> Does a field such as AddressLine1 contain the name AND address details of
> >> an
> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
> >> address?
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> > The data is all in one table in separate fields as below:
> >> >
> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> >> > AddressLine5 AddressLine6 FundName
> >> >
> >> > Can the table be created with VBA and automation that I described? One
> >> > that
> >> > will work for 1-6 name and address combinations per letter?
> >> >
> >> > TIA,
> >> > --
> >> > MorningStarFan
> >> >
> >> >
> >> > "Doug Robbins - Word MVP" wrote:
> >> >
> >> >> How is the data arranged in your database?
> >> >>
> >> >> --
> >> >> 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
> >> >>
> >> >> "MorningStarFan" wrote in
> >> >> message
> >> >>
> >> >> > Hi,
> >> >> > I'm looking to automate the generation of several hundred letters
> >> >> > from
> >> >> > data
> >> >> > in an access database. The problem is there can be anywhere from 1 -
> >> >> > 6
> >> >> > names
> >> >> > and addresses that the letter is addressed to. I can get one address
> >> >> > on
> >> >> > the
> >> >> > letter via my vba code from access, no problem. It's the multiple
> >> >> > addresses I
> >> >> > cannot find a way to do.
> >> >> > Does anybody know if I can create a word table to insert a variable
> >> >> > number
> >> >> > of name and address data into that will fill the table from left to
> >> >> > right
> >> >> > then down for a 2 column table?
> >> >> > Frankly, this sounds impossible to me at this point.
> >> >> >
> >> >> > TIA,
> >> >> > --
> >> >> > MorningStarFan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Doug Robbins - Word MVP · External Since: Jul 14, 2006 Posts: 2843

It now seems to me that this is just a mailmerge issue and that what you
really needing to be able to do is suppress blank lines that would otherwise
appear in the documents produced if there is no data in some of the fields.

Take a look in the mailmerge.fields newsgroup for posts concerning
suppressing blank lines in mailmerge.

--
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

"MorningStarFan" wrote in message

>I have tried several approaches to this. Below is the code that works to
> produce the letters with only one addressee (donor) name and address.
> ************************************************************
> Function MergeIt()
> Dim objWord As Word.Document
> Set objWord =
> GetObject("W:\Stewardship_Automation\Stewardship_Automation_That_Works\Office
> of Institutional Advancement_ENDS_TEMPLATE2.doc", "Word.Document")
> ' Make Word visible.
> objWord.Application.Visible = True
> ' Set the mail merge data source as the test db.
> objWord.MailMerge.OpenDataSource _
> Name:="W:\Stewardship_Automation\Stewardship_Automation_That_Works"
> & _
> "StewardshipENDSCleanupDatabase.mdb", _
> LinkToSource:=True, _
> Connection:="TABLE PMKENDS_2", _
> SQLStatement:="SELECT * FROM [PMKENDS_2]"
> ' Execute the mail merge.
> objWord.MailMerge.Execute
> End Function
> ***********************************************************
>
> I also am working on writing code that uses more vba to loop through my
> recordset and get the multiple addressees but I don't know how to get the
> multiples on the letter in the form of an address. My code will only spit
> out
> the data in rows in my table with the data arranged like this
>
> name addressline1 addressline2 ... city, state zip
> If you want to see that code too, I'll have finish modifying it. It's only
> partially done.
> I know how it will work though as it's a copy of the code I used for the
> recipient reports and I'm modifying it to work for the letters as well.
> Hopefully, anyways!
>
> TIA,
> --
> MorningStarFan
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> OK, Show us the code that you have now. Better to advise you how to
>> modify
>> that than start from scratch.
>>
>> --
>> 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
>>
>> "MorningStarFan" wrote in
>> message
>>
>> > fyi: my example only had data in AddressLine1 and 2 in this case. The
>> > data
>> > varies as addresses vary. Some addresses have 6 lines ...but that is
>> > rare.
>> > Most will only have two.
>> > --
>> > MorningStarFan
>> >
>> >
>> > "Doug Robbins - Word MVP" wrote:
>> >
>> >> Not quite clear to me,
>> >>
>> >> Does a field such as AddressLine1 contain the name AND address details
>> >> of
>> >> an
>> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
>> >> address?
>> >>
>> >> --
>> >> 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
>> >>
>> >> "MorningStarFan" wrote in
>> >> message
>> >>
>> >> > The data is all in one table in separate fields as below:
>> >> >
>> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
>> >> > AddressLine5 AddressLine6 FundName
>> >> >
>> >> > Can the table be created with VBA and automation that I described?
>> >> > One
>> >> > that
>> >> > will work for 1-6 name and address combinations per letter?
>> >> >
>> >> > TIA,
>> >> > --
>> >> > MorningStarFan
>> >> >
>> >> >
>> >> > "Doug Robbins - Word MVP" wrote:
>> >> >
>> >> >> How is the data arranged in your database?
>> >> >>
>> >> >> --
>> >> >> 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
>> >> >>
>> >> >> "MorningStarFan" wrote
>> >> >> in
>> >> >> message
>> >> >>
>> >> >> > Hi,
>> >> >> > I'm looking to automate the generation of several hundred letters
>> >> >> > from
>> >> >> > data
>> >> >> > in an access database. The problem is there can be anywhere from
>> >> >> > 1 -
>> >> >> > 6
>> >> >> > names
>> >> >> > and addresses that the letter is addressed to. I can get one
>> >> >> > address
>> >> >> > on
>> >> >> > the
>> >> >> > letter via my vba code from access, no problem. It's the multiple
>> >> >> > addresses I
>> >> >> > cannot find a way to do.
>> >> >> > Does anybody know if I can create a word table to insert a
>> >> >> > variable
>> >> >> > number
>> >> >> > of name and address data into that will fill the table from left
>> >> >> > to
>> >> >> > right
>> >> >> > then down for a 2 column table?
>> >> >> > Frankly, this sounds impossible to me at this point.
>> >> >> >
>> >> >> > TIA,
>> >> >> > --
>> >> >> > MorningStarFan
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

No, I need to be able to print multiple names and addresses (1-6) on a form
letter in two columns. The addresses are related to the SRK_ID and when
SRK_ID changes, I need to go to the next letter. It's not a simple mail
merge. I don't have any blank lines - just name and address lines.
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> It now seems to me that this is just a mailmerge issue and that what you
> really needing to be able to do is suppress blank lines that would otherwise
> appear in the documents produced if there is no data in some of the fields.
>
> Take a look in the mailmerge.fields newsgroup for posts concerning
> suppressing blank lines in mailmerge.
>
>
>
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> >I have tried several approaches to this. Below is the code that works to
> > produce the letters with only one addressee (donor) name and address.
> > ************************************************************
> > Function MergeIt()
> > Dim objWord As Word.Document
> > Set objWord =
> > GetObject("W:\Stewardship_Automation\Stewardship_Automation_That_Works\Office
> > of Institutional Advancement_ENDS_TEMPLATE2.doc", "Word.Document")
> > ' Make Word visible.
> > objWord.Application.Visible = True
> > ' Set the mail merge data source as the test db.
> > objWord.MailMerge.OpenDataSource _
> > Name:="W:\Stewardship_Automation\Stewardship_Automation_That_Works"
> > & _
> > "StewardshipENDSCleanupDatabase.mdb", _
> > LinkToSource:=True, _
> > Connection:="TABLE PMKENDS_2", _
> > SQLStatement:="SELECT * FROM [PMKENDS_2]"
> > ' Execute the mail merge.
> > objWord.MailMerge.Execute
> > End Function
> > ***********************************************************
> >
> > I also am working on writing code that uses more vba to loop through my
> > recordset and get the multiple addressees but I don't know how to get the
> > multiples on the letter in the form of an address. My code will only spit
> > out
> > the data in rows in my table with the data arranged like this
> >
> > name addressline1 addressline2 ... city, state zip
> > If you want to see that code too, I'll have finish modifying it. It's only
> > partially done.
> > I know how it will work though as it's a copy of the code I used for the
> > recipient reports and I'm modifying it to work for the letters as well.
> > Hopefully, anyways!
> >
> > TIA,
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> OK, Show us the code that you have now. Better to advise you how to
> >> modify
> >> that than start from scratch.
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> > fyi: my example only had data in AddressLine1 and 2 in this case. The
> >> > data
> >> > varies as addresses vary. Some addresses have 6 lines ...but that is
> >> > rare.
> >> > Most will only have two.
> >> > --
> >> > MorningStarFan
> >> >
> >> >
> >> > "Doug Robbins - Word MVP" wrote:
> >> >
> >> >> Not quite clear to me,
> >> >>
> >> >> Does a field such as AddressLine1 contain the name AND address details
> >> >> of
> >> >> an
> >> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the one
> >> >> address?
> >> >>
> >> >> --
> >> >> 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
> >> >>
> >> >> "MorningStarFan" wrote in
> >> >> message
> >> >>
> >> >> > The data is all in one table in separate fields as below:
> >> >> >
> >> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3 AddressLine4
> >> >> > AddressLine5 AddressLine6 FundName
> >> >> >
> >> >> > Can the table be created with VBA and automation that I described?
> >> >> > One
> >> >> > that
> >> >> > will work for 1-6 name and address combinations per letter?
> >> >> >
> >> >> > TIA,
> >> >> > --
> >> >> > MorningStarFan
> >> >> >
> >> >> >
> >> >> > "Doug Robbins - Word MVP" wrote:
> >> >> >
> >> >> >> How is the data arranged in your database?
> >> >> >>
> >> >> >> --
> >> >> >> 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
> >> >> >>
> >> >> >> "MorningStarFan" wrote
> >> >> >> in
> >> >> >> message
> >> >> >>
> >> >> >> > Hi,
> >> >> >> > I'm looking to automate the generation of several hundred letters
> >> >> >> > from
> >> >> >> > data
> >> >> >> > in an access database. The problem is there can be anywhere from
> >> >> >> > 1 -
> >> >> >> > 6
> >> >> >> > names
> >> >> >> > and addresses that the letter is addressed to. I can get one
> >> >> >> > address
> >> >> >> > on
> >> >> >> > the
> >> >> >> > letter via my vba code from access, no problem. It's the multiple
> >> >> >> > addresses I
> >> >> >> > cannot find a way to do.
> >> >> >> > Does anybody know if I can create a word table to insert a
> >> >> >> > variable
> >> >> >> > number
> >> >> >> > of name and address data into that will fill the table from left
> >> >> >> > to
> >> >> >> > right
> >> >> >> > then down for a 2 column table?
> >> >> >> > Frankly, this sounds impossible to me at this point.
> >> >> >> >
> >> >> >> > TIA,
> >> >> >> > --
> >> >> >> > MorningStarFan
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Doug Robbins - Word MVP · External Since: Jul 14, 2006 Posts: 2843

I would suggest that you use a Report in Access.

--
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

"MorningStarFan" wrote in message

> No, I need to be able to print multiple names and addresses (1-6) on a
> form
> letter in two columns. The addresses are related to the SRK_ID and when
> SRK_ID changes, I need to go to the next letter. It's not a simple mail
> merge. I don't have any blank lines - just name and address lines.
> --
> MorningStarFan
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> It now seems to me that this is just a mailmerge issue and that what you
>> really needing to be able to do is suppress blank lines that would
>> otherwise
>> appear in the documents produced if there is no data in some of the
>> fields.
>>
>> Take a look in the mailmerge.fields newsgroup for posts concerning
>> suppressing blank lines in mailmerge.
>>
>>
>>
>>
>> --
>> 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
>>
>> "MorningStarFan" wrote in
>> message
>>
>> >I have tried several approaches to this. Below is the code that works
>> >to
>> > produce the letters with only one addressee (donor) name and address.
>> > ************************************************************
>> > Function MergeIt()
>> > Dim objWord As Word.Document
>> > Set objWord =
>> > GetObject("W:\Stewardship_Automation\Stewardship_Automation_That_Works\Office
>> > of Institutional Advancement_ENDS_TEMPLATE2.doc", "Word.Document")
>> > ' Make Word visible.
>> > objWord.Application.Visible = True
>> > ' Set the mail merge data source as the test db.
>> > objWord.MailMerge.OpenDataSource _
>> >
>> > Name:="W:\Stewardship_Automation\Stewardship_Automation_That_Works"
>> > & _
>> > "StewardshipENDSCleanupDatabase.mdb", _
>> > LinkToSource:=True, _
>> > Connection:="TABLE PMKENDS_2", _
>> > SQLStatement:="SELECT * FROM [PMKENDS_2]"
>> > ' Execute the mail merge.
>> > objWord.MailMerge.Execute
>> > End Function
>> > ***********************************************************
>> >
>> > I also am working on writing code that uses more vba to loop through my
>> > recordset and get the multiple addressees but I don't know how to get
>> > the
>> > multiples on the letter in the form of an address. My code will only
>> > spit
>> > out
>> > the data in rows in my table with the data arranged like this
>> >
>> > name addressline1 addressline2 ... city, state zip
>> > If you want to see that code too, I'll have finish modifying it. It's
>> > only
>> > partially done.
>> > I know how it will work though as it's a copy of the code I used for
>> > the
>> > recipient reports and I'm modifying it to work for the letters as well.
>> > Hopefully, anyways!
>> >
>> > TIA,
>> > --
>> > MorningStarFan
>> >
>> >
>> > "Doug Robbins - Word MVP" wrote:
>> >
>> >> OK, Show us the code that you have now. Better to advise you how to
>> >> modify
>> >> that than start from scratch.
>> >>
>> >> --
>> >> 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
>> >>
>> >> "MorningStarFan" wrote in
>> >> message
>> >>
>> >> > fyi: my example only had data in AddressLine1 and 2 in this case.
>> >> > The
>> >> > data
>> >> > varies as addresses vary. Some addresses have 6 lines ...but that is
>> >> > rare.
>> >> > Most will only have two.
>> >> > --
>> >> > MorningStarFan
>> >> >
>> >> >
>> >> > "Doug Robbins - Word MVP" wrote:
>> >> >
>> >> >> Not quite clear to me,
>> >> >>
>> >> >> Does a field such as AddressLine1 contain the name AND address
>> >> >> details
>> >> >> of
>> >> >> an
>> >> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the
>> >> >> one
>> >> >> address?
>> >> >>
>> >> >> --
>> >> >> 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
>> >> >>
>> >> >> "MorningStarFan" wrote
>> >> >> in
>> >> >> message
>> >> >>
>> >> >> > The data is all in one table in separate fields as below:
>> >> >> >
>> >> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3
>> >> >> > AddressLine4
>> >> >> > AddressLine5 AddressLine6 FundName
>> >> >> >
>> >> >> > Can the table be created with VBA and automation that I
>> >> >> > described?
>> >> >> > One
>> >> >> > that
>> >> >> > will work for 1-6 name and address combinations per letter?
>> >> >> >
>> >> >> > TIA,
>> >> >> > --
>> >> >> > MorningStarFan
>> >> >> >
>> >> >> >
>> >> >> > "Doug Robbins - Word MVP" wrote:
>> >> >> >
>> >> >> >> How is the data arranged in your database?
>> >> >> >>
>> >> >> >> --
>> >> >> >> 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
>> >> >> >>
>> >> >> >> "MorningStarFan"
>> >> >> >> wrote
>> >> >> >> in
>> >> >> >> message
>> >> >> >>
>> >> >> >> > Hi,
>> >> >> >> > I'm looking to automate the generation of several hundred
>> >> >> >> > letters
>> >> >> >> > from
>> >> >> >> > data
>> >> >> >> > in an access database. The problem is there can be anywhere
>> >> >> >> > from
>> >> >> >> > 1 -
>> >> >> >> > 6
>> >> >> >> > names
>> >> >> >> > and addresses that the letter is addressed to. I can get one
>> >> >> >> > address
>> >> >> >> > on
>> >> >> >> > the
>> >> >> >> > letter via my vba code from access, no problem. It's the
>> >> >> >> > multiple
>> >> >> >> > addresses I
>> >> >> >> > cannot find a way to do.
>> >> >> >> > Does anybody know if I can create a word table to insert a
>> >> >> >> > variable
>> >> >> >> > number
>> >> >> >> > of name and address data into that will fill the table from
>> >> >> >> > left
>> >> >> >> > to
>> >> >> >> > right
>> >> >> >> > then down for a 2 column table?
>> >> >> >> > Frankly, this sounds impossible to me at this point.
>> >> >> >> >
>> >> >> >> > TIA,
>> >> >> >> > --
>> >> >> >> > MorningStarFan
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

MorningStarFan · External Since: Apr 11, 2007 Posts: 15

Thanks for the suggestion. However, our VP wants a word document because we
can control the fonts, font weight, and formatting more with word than
access. They usually reject our access reports as not professional quality.
--
MorningStarFan

"Doug Robbins - Word MVP" wrote:

> I would suggest that you use a Report in Access.
>
> --
> 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
>
> "MorningStarFan" wrote in message
>
> > No, I need to be able to print multiple names and addresses (1-6) on a
> > form
> > letter in two columns. The addresses are related to the SRK_ID and when
> > SRK_ID changes, I need to go to the next letter. It's not a simple mail
> > merge. I don't have any blank lines - just name and address lines.
> > --
> > MorningStarFan
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> It now seems to me that this is just a mailmerge issue and that what you
> >> really needing to be able to do is suppress blank lines that would
> >> otherwise
> >> appear in the documents produced if there is no data in some of the
> >> fields.
> >>
> >> Take a look in the mailmerge.fields newsgroup for posts concerning
> >> suppressing blank lines in mailmerge.
> >>
> >>
> >>
> >>
> >> --
> >> 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
> >>
> >> "MorningStarFan" wrote in
> >> message
> >>
> >> >I have tried several approaches to this. Below is the code that works
> >> >to
> >> > produce the letters with only one addressee (donor) name and address.
> >> > ************************************************************
> >> > Function MergeIt()
> >> > Dim objWord As Word.Document
> >> > Set objWord =
> >> > GetObject("W:\Stewardship_Automation\Stewardship_Automation_That_Works\Office
> >> > of Institutional Advancement_ENDS_TEMPLATE2.doc", "Word.Document")
> >> > ' Make Word visible.
> >> > objWord.Application.Visible = True
> >> > ' Set the mail merge data source as the test db.
> >> > objWord.MailMerge.OpenDataSource _
> >> >
> >> > Name:="W:\Stewardship_Automation\Stewardship_Automation_That_Works"
> >> > & _
> >> > "StewardshipENDSCleanupDatabase.mdb", _
> >> > LinkToSource:=True, _
> >> > Connection:="TABLE PMKENDS_2", _
> >> > SQLStatement:="SELECT * FROM [PMKENDS_2]"
> >> > ' Execute the mail merge.
> >> > objWord.MailMerge.Execute
> >> > End Function
> >> > ***********************************************************
> >> >
> >> > I also am working on writing code that uses more vba to loop through my
> >> > recordset and get the multiple addressees but I don't know how to get
> >> > the
> >> > multiples on the letter in the form of an address. My code will only
> >> > spit
> >> > out
> >> > the data in rows in my table with the data arranged like this
> >> >
> >> > name addressline1 addressline2 ... city, state zip
> >> > If you want to see that code too, I'll have finish modifying it. It's
> >> > only
> >> > partially done.
> >> > I know how it will work though as it's a copy of the code I used for
> >> > the
> >> > recipient reports and I'm modifying it to work for the letters as well.
> >> > Hopefully, anyways!
> >> >
> >> > TIA,
> >> > --
> >> > MorningStarFan
> >> >
> >> >
> >> > "Doug Robbins - Word MVP" wrote:
> >> >
> >> >> OK, Show us the code that you have now. Better to advise you how to
> >> >> modify
> >> >> that than start from scratch.
> >> >>
> >> >> --
> >> >> 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
> >> >>
> >> >> "MorningStarFan" wrote in
> >> >> message
> >> >>
> >> >> > fyi: my example only had data in AddressLine1 and 2 in this case.
> >> >> > The
> >> >> > data
> >> >> > varies as addresses vary. Some addresses have 6 lines ...but that is
> >> >> > rare.
> >> >> > Most will only have two.
> >> >> > --
> >> >> > MorningStarFan
> >> >> >
> >> >> >
> >> >> > "Doug Robbins - Word MVP" wrote:
> >> >> >
> >> >> >> Not quite clear to me,
> >> >> >>
> >> >> >> Does a field such as AddressLine1 contain the name AND address
> >> >> >> details
> >> >> >> of
> >> >> >> an
> >> >> >> addressee? Or, are AddressLine1 through AddressLine6 parts of the
> >> >> >> one
> >> >> >> address?
> >> >> >>
> >> >> >> --
> >> >> >> 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
> >> >> >>
> >> >> >> "MorningStarFan" wrote
> >> >> >> in
> >> >> >> message
> >> >> >>
> >> >> >> > The data is all in one table in separate fields as below:
> >> >> >> >
> >> >> >> > ID PreferredName AddressLine1 AddressLine2 AddressLine3
> >> >> >> > AddressLine4
> >> >> >> > AddressLine5 AddressLine6 FundName
> >> >> >> >
> >> >> >> > Can the table be created with VBA and automation that I
> >> >> >> > described?
> >> >> >> > One
> >> >> >> > that
> >> >> >> > will work for 1-6 name and address combinations per letter?
> >> >> >> >
> >> >> >> > TIA,
> >> >> >> > --
> >> >> >> > MorningStarFan
> >> >> >> >
> >> >> >> >
> >> >> >> > "Doug Robbins - Word MVP" wrote:
> >> >> >> >
> >> >> >> >> How is the data arranged in your database?
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> 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
> >> >> >> >>
> >> >> >> >> "MorningStarFan"
> >> >> >> >> wrote
> >> >> >> >> in
> >> >> >> >> message
> >> >> >> >>
> >> >> >> >> > Hi,
> >> >> >> >> > I'm looking to automate the generation of several hundred
> >> >> >> >> > letters
> >> >> >> >> > from
> >> >> >> >> > data
> >> >> >> >> > in an access database. The problem is there can be anywhere
> >> >> >> >> > from
> >> >> >> >> > 1 -
> >> >> >> >> > 6
> >> >> >> >> > names
> >> >> >> >> > and addresses that the letter is addressed to. I can get one
> >> >> >> >> > address
> >> >> >> >> > on
> >> >> >> >> > the
> >> >> >> >> > letter via my vba code from access, no problem. It's the
> >> >> >> >> > multiple
> >> >> >> >> > addresses I
> >> >> >> >> > cannot find a way to do.
> >> >> >> >> > Does anybody know if I can create a word table to insert a
> >> >> >> >> > variable
> >> >> >> >> > number
> >> >> >> >> > of name and address data into that will fill the table from
> >> >> >> >> > left
> >> >> >> >> > to
> >> >> >> >> > right
> >> >> >> >> > then down for a 2 column table?
> >> >> >> >> > Frankly, this sounds impossible to me at this point.
> >> >> >> >> >
> >> >> >> >> > TIA,
> >> >> >> >> > --
> >> >> >> >> > MorningStarFan
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>