Word 2003, grabbing address and email data from a table

[DougP]

I created a directory in Word and all of the names are as follows
Canner, fathername and mothername PhHome: xxx-xxx-xxxx
child– grade Cell Father: xxx-xxx-xxxx
address street Cell Mother: xxx-xxx-xxxx
city, st zip
email blahblah@somemail.com


Clanna, fathername and mothername PhHome: xxx-xxx-xxxx
child– grade Cell Father: xxx-xxx-xxxx
address street Cell Mother: xxx-xxx-xxxx
city, st zip
email blahblah2@somemail.com


Clocks, fathername and mothername PhHome: xxx-xxx-xxxx
child– grade Cell Father: xxx-xxx-xxxx
address street Cell Mother: xxx-xxx-xxxx
city, st zip
email blahblah3@somemail.com
----------

I would like to create VBA code or something to get each persons info such as name, child, address, city st zip, 3ea phones and then send that to the email shown.

Is this possible? I want to have each person check their info. for accuracy. since we have so many it seems several are wrong when it gets printed. I really don't want to send everyone the entire directory in case some one does not wnat their phone number published etc.

TIA



DougP

 

[Andrzejek]

If that would be me, I would have a little Access data base with 1 table, with all of the information you have here. It would be very easy to do what you want. You can display it all in a simple report – it may look like you have it now in Word.

Even have it all in a simple Excel spreadsheet.

But to retrieve the right pieces of information from your Word document may be though. It may be depended on how you have your Word document formatted. Not that it is impossible… I know others here will be happy to help, but be prepared for some interesting VBA code to go down the document, pick the pieces, and validate.


Have fun.

---- Andy

 

[SkipVought]

Doug,

I created a directory in Word...

Why, why, why???

Get this ABSOLUTELY HORRIBLE TOTALLY UNUSEABLE structure into a PROPER TABLE. If you can get that done, your MailMerge is a piece of cake.

I'd paste the 'directory' into Excel and do ALL the coding there.

Unfortunately, your poested examples do not make it clear, what data would be in what column in Excel. Consequently, I could not comment further on a solution.

I would not proceed any other way than creating a proper table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DougP]

Well I would have put it in Access and it is actually.
But this is a School Calendar/Partent name/student directory, listing eveything during the entire school year and church.

The names are part of this booklet and are at the end.

I have been told to update the directory with new students and any changes to the above data. I am doing this in Word since this what I was given and this is the final document. Obviously this is a monumental task and I loathe it evey year.
Doing the entire calendar takes many many weeks and several people double checking eveything. I was thinkging it would be good to give each parent just their listing to double check this year. I did copy and paste this into a text file and wrote VBA to extract the data into Access and it works well. So I guess this it what I need to do?

DougP

 

[macropod]

Hi DougP,

Depending on how your Word 'directory' is structured, you may be able to do what you want via a mailmerge to email.

Cheers
Paul Edstein
[MS MVP - Word]

 

[fumei]

Depending on how your Word 'directory' is structured"

There is the rub.

Manipulation of DATA in Word can be done, but really, this is not what it is for. It is a word processor. It is a document builder.

If it is well structured, if it is well designed, like...ahem...a database, or an Excel spreadsheet....ahem...then this can be moderately easy to do. If it is not, well, then I can imagine it takes many weeks and many people.

 

[macropod]

Hi Gerry,

Manipulation of DATA in Word can be done, but really, this is not what it is for. It is a word processor. It is a document builder.

Using document tables as mailmerge datasources from which to build an output document is one of the things Word is designed to do. However, the strictures I referred to apply to any data source that Word might use. Similar issues arise even when Word isn't being used.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

@Paul

Using document [highlight]tables[/highlight] as mailmerge datasources...

This issue is not whether the source data is in Word or somewhere else. This issue is the the OP, DougP, did NOT create a TABLE in Word. Rather he created a DIRECTORY in Word, that is not in tabular structure.

My advice has been to put the data into a proper table, wherever makes sense. To me, as Excel centric as I am, Excel makes the most sense for the table.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi Skip,

The OP's directory may or may not be a table. I see nothing in the posts so far that makes a categorical statement either way.

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Correction: The thread heading suggests the OP is using a Word table.

In any event, Word has no feature named a directory.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

It is a common user pitfall, to cast DATA in a structure that the user wants to view the data, rather than a functional data structure.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

If that was my text in my word document:
[tt]
Canner, fathername and mothername PhHome: 555-xxx-xxxx
child– grade Cell Father: 555-xxx-xxxx
address street Cell Mother: 555-xxx-xxxx
city, st zip
email 555@somemail.com


Clanna, fathername and mothername PhHome: 666-xxx-xxxx
child– grade Cell Father: 666-xxx-xxxx
address street Cell Mother: 666-xxx-xxxx
city, st zip
email 6662@somemail.com


Clocks, fathername and mothername PhHome: 777-xxx-xxxx
child– grade Cell Father: 777-xxx-xxxx
address street Cell Mother: 777xxx-xxxx
city, st zip
email 777@somemail.com
[/tt]

You could use something like this to read this text and send an e-mails:

Option Explicit

Sub MyTest()
Dim p As Paragraph
Dim str() As String
Dim i As Integer
Dim strB As String

For Each p In ActiveDocument.Paragraphs
    str = Split(p.Range.Text, "")
    For i = LBound(str) To UBound(str)
        If InStr(str(i), "@") Then[green]
            'we have an e-mail address here[/green]
            Call SendEMail(Mid(str(i), 9), strB)
            strB = ""
        Else
            strB = strB & vbNewLine & str(i)
        End If
    Next i
Next p

End Sub

Private Sub SendEMail(strTo As String, strBody As String)

Debug.Print "To: " & strTo
Debug.Print "Body: " & strBody

End Sub

You get the outcome like this:
[tt]
To:  555@somemail.com          
Body:
  Canner, fathername and mothername    PhHome:       555-xxx-xxxx   
  child– grade                         Cell Father:  555-xxx-xxxx
  address street                       Cell Mother:  555-xxx-xxxx
  city, st zip

To:  6662@somemail.com          
Body:
  Clanna, fathername and mothername    PhHome:       666-xxx-xxxx   
  child– grade                         Cell Father:  666-xxx-xxxx
  address street                       Cell Mother:  666-xxx-xxxx
  city, st zip

To:  777@somemail.com      
Body:
  Clocks, fathername and mothername    PhHome:       777-xxx-xxxx   
  child– grade                         Cell Father:  777-xxx-xxxx
  address street                       Cell Mother:  777xxx-xxxx
  city, st zip
[/tt]
Just modify the Sub SendEMail to accually send an e-mail and you are done.


Have fun.

---- Andy

 

[SkipVought]

I have been told to update the directory with new students and any changes to the above data. I am doing this in Word since this what I was given and this is the final document. Obviously [highlight]this is a monumental task and I loathe it evey year[/highlight].

Just modify the Sub SendEMail to accually send an e-mail and you are done.

Done....for NOW! But what about the NEXT time and the time after THAT, and.......

Which is why I would NOT put a bandaid on it by coding a solution for THIS TASK.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Skip, I agree. See my very first reply to this post - and I stand by it

DougP may use this logic to transfer the data from his Word document into Excel, Access, or whatever. I just wanted to show some logic to deal with the information in Word. What he does with it - it's up to him....

Have fun.

---- Andy