Letter in Word Address in Excel

Definitely.

Use Mail merge.
http://www.theofficeexperts.com/word.htm#MailMerge

I hope it will work for you.

Best of luck.

Create an address label on a separate sheet in your address database and use Vlookup to populate it. Then use a macro in Word to open the Database and copy the label and paste into your Word document.

Attached is an example I downloaded fro John Walkenbach's site. This example does it all from Excel, including the letter body. It might not still be available but here's a link to his site
http://j-walk.com/ss/excel/usertips/index.htm

OK Reading we agree mail merge is the answer, maybe not.

Down side of Mail Merge and is my only one of contention is this:

Say you merge 50 letters in one go you get one big file in one document with you 50 letters, all fine and dandy. But 6 months later some guys shouts at you saying you sent X letter on Y date, sounds like second data base required here>>>>

What if each letter was saving as exact copy [your use this copy to print – so it must be spot on] and then you can search all the thousands of letter and bingo there it is?

SOUNDS good?

This is a project I’m developing that will do as I say and even zip them, everything is in Excel, but some time until I can release this as a usable item. If you’re a VBA guru ill gladly post the code I have – its crude and ugly at present but will do as you want and works, just a total mess of codes.

Also can handle many different letters, not just one, so you can run and walk off, letter 1 or 2 or 3 or 4 or 5 as many as you like, all set from a trigger.

Let me know

Jack in the UK

Hi, Jack...there is code out there somewhere on the net that will take mail merged letters and break them down into separate files. I'll see if I can't find it---thought you might be interested...

If Wistow only needs to grab one address at a time, writing one letter at a time, then I don't recommend mailmerge. But, if Wistow does need to write all the letters basically at one time, but they all say something different, beginning with a mail merge to put in all of the SAME information, i.e., name, address, opening paragraph, signature...All that can be done with a mail merge and the individual letters "tweaked".

There are also ways to have mail merge provide different text depending on fields in the data source. I'm afraid I'm not very good at it, tho, but the MVPs have a bit of info: http://www.mvps.org/word/FAQs/MailMerge/MMergeIfFields.htm

Also, Jack: You wouldn't need "another" database. I suggest opening the data source and adding a column called XYZ Letter, and inserting the date sent. That way, if you add records later, you can tell it you want to merge only those records where the XYZ letter column is blank (because you've sent XYZ letter to everyone who has a date in that column...).

I know you are not crazy about mail merge, Jack. And I sometimes wonder if it is because you don't understand its capabilities?

:bouncing:

No dislike of Word, really as you know, its all about levels of usage, sure we all know Work has the cup here, but Excel can do jus as good, require a bucket of VBA thou, I just prefer the end results, in my opinion.

I find Jack can do more maybe as you say lack of skills and knowledge in the world field of M$ Word I might be quick top point out myself, believe it or not you are possibly my best helper in this aspect. I try to learn your tips in Word and then when m happy I transpose these to Excel which enhances my Excel knowledge and skills – so you help / posts are two fold kind of good I feel.

OK explain a little more

From a TOC [table of contents] the data can be in any arrangement, using headers of cause. And the data base I prefer to use two, one being data and one referred as merge as I call it, which houses all the data needed to merge / produce letter, this is a safety precaution Justin case, you know what I’m like, careful.

I just prefer lots of small sheets to a mass Word merge, which is no option. I’m interested in you information’s re code to split this, but will this work on two pages, and print OK on headed and continuation papers? Would be interesting not only to say interesting.

I have choice of letters [templates] that are then filled, saved and printed, just as a word document merge will do.

I found unique names were best also, but your have to wait for that secret just now, took some solving. As did the zip of these documents, unique will change instantly so Jack had some magic here:

Here is am example of my codes, not all ill post that as an attachment so all can try.

I just wanted a fully Excel solution to a common issue, and I can even save as M$ Word documents, which for compression reasons is best, but as yet I do say to these green cross icons.

Jack – this is an old code some 3 years back so you can see the methods I learnt from and how things have moved on and progressed.

Code Example [not complete]

Dim lwb_Ledger, lwb_Letter As Workbook
Dim ls_Letter1, ls_Letter2, ls_Letter3, ls_Letter4, ls_Letter5 As String
Dim lu_UserNo As String
Dim ld_DocsName As String
Dim li_NumSheets, li_NumRows, li_LetterCol, li_NumLetter As Integer
Dim lb_LastRow As Boolean
BRIXTON= Format$(Now, "d mmmm yyyy")
Snooser = Format$(Now + 14, "d mmm yyyy")
NumberFormat = "~## ## ##~"
UC = Format$(Now, "hh mm ss")

lb_LastRow = False
li_NumRows = 5 'Is always the default value
li_LetterCol = 28 'Need to check that this is in the correct insertion point
'A=1 B=2 C=3 D=4 ect
li_searchrow = 1 'JACK Sets search row
li_resultrow = 29 'JACK Sets result row

Set lwb_Ledger = ActiveWorkbook ' Sets number of arguments ie number diff letters
ls_Letter1 = "REM1"
ls_Letter2 = "REM1Y"
ls_Letter3 = "REM2"
ls_Letter4 = "REM2Y"
ls_Letter5 = "HRS24"

‘ Jack can be as many as you like matching template you have made

'JACK NO. WSheets reqd in new book ie.. 1 not 6
li_NumSheets = Application.SheetsInNewWorkbook

'JACK Put sheets = 1
With Application
.SheetsInNewWorkbook = 1 'Can put any number here ie 1,2,3,4,5,6,7,8, etc
End With

Mailmerge would work well if you used a lookup command for your customer list and then used IF statements inside your Word document.

You can also customize your letter by using If/and/else statements to bring in groups of paragraphs based on a specific requirement.

Good luck, Terry

Just as an update on my earlier comments Excel V’s Word

OK I have successfully merged 1680 pages of Word Mail Merge in 45 seconds, these are letters advisory style so you get the idea. Data source is Excel and all the same no frills. Saves in one file 7mb

Funny the page merge counter stops at 999 and then report *** and stay that way ?? But still !!!!

Also I have found that the count is low but if you wiggle the mouse over the counter it changes and speeds up. ???

Left alone merge was 2 minutes, wiggle the mouse method 45 seconds !!!

OK the same in Excel via my VBA program I have written. Difference is each is saved as separate document and just as in a flash Jack each is printed and zipped on the fly.

Time check 18 and minute ?????

The Excel version in more flexible for my needs and use, and I don’t have more that 18 and hour to do for a task so its not stress.

Interesting, soon ill post up my utility when I have prefaced the zipping as on Win Xp it get stropped with source and destination location of original excel file and re locating the Zip !!!

I myself prefer using Excel for something like this. If the letter is basically a standard form letter with some customization for certain fields, there is no need to save a copy of the letter to each addressee -- just save the standard letter and a database of the customization, which will include the Customer ID and date sent among other things.

Now as far as addressing the letter is considered, I would use the INDEX/MATCH functions to pull the information for each Customer ID and format the address as required.

So whether I am sending out 50 form letters (with necessary customization) or thousands, all I need to do is save 1 copy of the form letter and the customization database.

Later, if there is need to check what was sent to which customer, it can be instaneously brought up on screen for viewing, for emailing or for printing.

I just wanted to share some of my thoughts on this topic.