[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

email merge from large Word document

Posted on 2014-03-06
7
Medium Priority
?
494 Views
Last Modified: 2014-03-07
Is it possible to perform an email merge from the attached sample document?

Each individual's transcript contains a name and email address.
testdoc.docx
0
Comment
Question by:roosterup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:aboo_s
ID: 39909260
email merge means what exactly?
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39909268
Not directly, you need a macro to go through the documents and extract email addresses, names  etc first then use that list to generate your merge.
0
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39909347
@roosterup, do you mean to send the email using mail merge.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:roosterup
ID: 39909865
I mean send the individuals their transcript pages to the correct email address.  

What I have is a huge word doc with many individual transcripts contained within.  I would like to email each person their transcript automatically. So extract the email address from each person's transcript header and email that person their transcript.

There probably isn't a good way to do this.  I am limited by the output of the software I am using.  There is a PDF option, but it seems to mangle the data when a person's transcript goes over a page.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39910002
A macro to read the document, identify the table and extract email, name and address is certainly possible but outside the time I allot to EE :)

 So I cribbed this from stackoverflow (google VBA extract data from a table) and will have play with it later if someone hasn't leapt into the fray in the meantime - I think it needs extending to read more than the first table

Sub Sample()
    Dim wrdTbl As Table
    Dim RowCount As Long, ColCount As Long, i As Long, j As Long

    '~~> Excel Objects
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object

    '~~> Set your table
    Set wrdTbl = Selection.Tables(1)

    '~~> Get the word table Row and Column Counts
    ColCount = wrdTbl.Columns.Count
    RowCount = wrdTbl.Rows.Count

    '~~> Create a new Excel Applicaiton
    Set oXLApp = CreateObject("Excel.Application")

    '~~> Hide Excel
    oXLApp.Visible = False

    '~~> Open the relevant Excel file
    Set oXLwb = oXLApp.Workbooks.Open("C:\Sample.xls")
    '~~> Work with Sheet1. Change as applicable
    Set oXLws = oXLwb.Sheets(1)

    '~~> Loop through each row of the table
    For i = 1 To RowCount
        '~~> Loop through each cell of the row
        For j = 1 To ColCount
            '~~> This gives you the cell contents
            Debug.Print wrdTbl.Cell(i, j).Range.Text

            '~~> Put your code here to export the values of the Word Table
            '~~> cell to Excel Cell. Use the .Range.Text to get the value
            '~~> of that table cell as shown above and then simply put that
            '~~> in the Excel Cell
            With oXLws
                '~~> EXAMPLE
                ' .Cells(1, 1).Value = wrdTbl.Cell(i, j).Range.Text
            End With
        Next
    Next

    '~~> Close and save Excel File
    oXLwb.Close savechanges:=True

    '~~> Cleanup (VERY IMPROTANT)
    Set oXLws = Nothing
    Set oXLwb = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing

    MsgBox "DONE"
End Sub

Open in new window


http://stackoverflow.com/questions/15789505/vba-pull-data-from-ms-word-table-into-ms-excel-worksheet-not-special-paste
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39910413
Ok that approach fails because whatever produced the output is not forming decent well behaved word tables. However - load the file into Word and Save As XML document then load that file into excel. you should be told Excel is creating a map for you and wind up with a huge table. if you scroll to column EK there should be a heading ns5:t which contains all the data we are interested in. I don't have time right now but will try and come up with a macro to extract the relevant information.

It looks like whatever didn't produce nice word tables was at least very consistent so this approach would seem to have a chance of working out
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 total points
ID: 39912622
Please try the attached, I created testdoc.xml from word (2010) by opening your sample and using 'Save as, filetype, XML. And saved it to my desktop.

In the excel sheet I created a macro that uses regular expressions to strip out the name and email addresses. You will need to load this into a module and change the File name parameter to point to your testcase.xml.

Its quite slow and I don't know if it will work with a very large document but it shows that the data can be extracted..
testdoc.xml
extractor.xls
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question