reverse mail merge name address city state zip

We're using a new CRM system and when we're downloading our created letters into Word so that we can print them out to be mailed, the new system doesn't do envelopes or labels. They assume the sales people are going to hand address all the envelopes.

My question is this;
Can the customer information be extracted from the Word documents (letters) and be merged onto envelopes or labels?

At the upper left of each letter is:
Name
Address
City, State, Zip

Other than copy n paste each letter information one at a time onto an envelope, does anyone have an easier method of doing this?

I've done the obvious mail merge from a database or list to envelopes or labels but not a reverse type merge and was wondering if it was possible or if anyone has ever tried it.

Our old system did the usually mail merge; after printing the letters you had the option to create the envelopes from the same list of names the letters were created from. This new system doesn't do that for what ever reason. The only data we control is a date range of sales or service, then the letters are automatically generated.

Any advise is appreciated.


http://www.gmayor.com/EnvelopesAndLabels.htm  may help print individual envelopes, but it should be possible to produce a macro to batch print envelopes from a folder full of letters. The difficult bit, when working without sight of the letter, is to determine what constitutes an address. To establish that, open a letter and run the following macro:

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

Sub Macro1()
Dim i As Long
Dim strAddress As String
    strAddress = vbNullString
    For i = 1 To 10
        strAddress = strAddress & i & vbTab & _
                     ActiveDocument.Paragraphs(i).Range.Text & vbCr
    Next i
    MsgBox strAddress
End Sub

It will display the first ten paragraphs of the letter, each next to a number. Note which numbers represent the start and end lines of the address. Then use the numbers in the following macro where indicated.

Create an envelope template (you can use the #10 samples at http://www.gmayor.com/Zips/Envelope.zip as a start point). If you create your own from scratch http://www.gmayor.com/changing_envelope_layout.htm  then ensure that the address location is marked with a bookmark called Address (the samples have this). The samples are in DOT format. You can save them as DOTX if you wish, though they will work as DOT format. Either way ensure that the name and path is entered where shown in bold.

Put the letters in a folder and run the macro. Select that folder at the prompt and it will print an envelope for each letter. If some of the letters use a different format, the results may be not what you expect. :) How successful this approach will be will depend on the accuracy of the typist who created them and that all the addresses have the same number of lines, which might work in the USA, but maybe not elsewhere..

Sub BatchPrintEnvelopes()
Dim strFilename As String
Dim strPath As String
Dim oDoc As Document
Dim fDialog As FileDialog
Dim oRng As Range
Dim oAddr As Range
Dim oEnvelope As Document
Const strEnvelope As String = "D:\Word 2010 Templates\Envelope #10.dotx"
Const iStart As Long = 1
Const iEnd As Long = 4
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = "Select folder containing the letters to process and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            MsgBox "Cancelled By User", , _
                   "List Folder Contents"
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1) & "\"
    End With
    strFilename = Dir$(strPath & "*.docx")
    While Len(strFilename) <> 0
        WordBasic.DisableAutoMacros 1
        Set oDoc = Documents.Open(strPath & strFilename)
        Set oRng = oDoc.Range
        oRng.Start = oDoc.Paragraphs(iStart).Range.Start
        oRng.End = oDoc.Paragraphs(iEnd).Range.Start
        Set oEnvelope = Documents.Add(strEnvelope)
        Set oAddr = oEnvelope.Bookmarks("Address").Range
        oAddr.Text = oRng.Text
        oEnvelope.PrintOut
        oEnvelope.Close wdDoNotSaveChanges
        oDoc.Close SaveChanges:=wdDoNotSaveChanges
        strFilename = Dir$()
        WordBasic.DisableAutoMacros 0
    Wend
    Set oEnvelope = Nothing
    Set oRng = Nothing
    Set oDoc = Nothing
End Sub



Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 8, 2023 Views 683 Applies to: