Solved

email merge from large Word document

Posted on 2014-03-06
7
465 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

813 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

12 Experts available now in Live!

Get 1:1 Help Now