Solved

email merge from large Word document

Posted on 2014-03-06
7
467 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
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…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

861 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