Letter in Word Address in Excel

  • We have a customer database in excel appprox 3000 names and addresses they all have a unique customer number in column "A". We write a individual letter to about 50 of these people each week when they contact us. At the moment we write the letter, swap to the spread sheet, copy and paste the data into word. I have tried to automate this using VBA but without any success.


    I can check if running and start excel if needed from word


    In excel I can search for the ID number, select and copy the relevant text


    Paste and format the text in word


    But I cannot link the 3 bits together, sometimes it works but run the code a second time and nothing happens.


    Most of my code is bits I have found on the net and adaped, I would like to start again Can anyone help me to get started


    Thanks
    Graham

  • We only send letters to the 50 or so who contacted us
    I use the unique number to select the address
    Ther is no logic pattern to allow us to use mailmerge as far as I can see


    We type the letter unique for each customer, get the address from excel (cut and paste) into word, print the letter and then print the envelope


    Every step is unique.


    Thanks Graham

  • 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

  • Thanks for the replies


    Dreamboat "If Wistow only needs to grab one address at a time, writing one letter at a time, then I don't recommend mailmerge" Yes on some days I only need to produce 1 letter and on another day 2 or 10


    At the moment this is a manual task and I want to automate part of it


    steps
    1) write a totally unique letter in word
    2) swap to excel
    3) Search for the customer code
    4) select the cells containing the address from the row
    5) copy address
    6) swap back to word
    7) paste data in the correct place
    8) Convert table to text
    9) format text
    10) Check and print letter


    I think that a mailmerge option would not work and is over complex for what I need to do.


    I tried using the macro recorder to get the basics and then added the extra code onto it but it only works sometimes


    Thanks Graham

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!