Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

email merge from large Word document

Posted on 2014-03-06
7
Medium Priority
?
504 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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

564 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