Solved

email merge from large Word document

Posted on 2014-03-06
7
455 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
Comment Utility
email merge means what exactly?
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
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
Comment Utility
@roosterup, do you mean to send the email using mail merge.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:roosterup
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now