Results 1 to 15 of 15
  1. #1
    risman
    Guest

    Stripping email addresses from a distribution list

    I have a 58-page list of attendees at a conference, with names, addresses, phones, faxes, and in some cases, email addresses. There is not a consistent number of lines for each person. One page is attached. I want to be able to create a new file with just the name and email address as 2 separate fields. Is there a way to do that?

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Stripping email addresses from a distribution list

    Gutsy move posting real peoples addresses there Bob [img]/w3timages/icons/woof.gif[/img] I do think we needed to see it to believe it though.

    Try using a vertical selection to separate the left and right columns by holding the ALT key and dragging the complete left column across to the edge of the right column. Cut it and move to the bottom of the document and paste. Now get rid of the tabs with a global search and replace with nothing.

    Next you will need to strip out the other text which is possibly done with a series of search and replaces.
    <pre>Find Replace Wildcards Replace Format
    ^p ^013 False No Formatting
    E-mail*^013 True Bold
    ^013 ^p False No Formatting</pre>


    Now it gets tricky and you need code to find the Non bold items which contain more than a return and replace it with a tab. The following is a good start

    <pre>Sub FindDeleteNonBold()
    With Selection
    .HomeKey Unit:=wdStory
    With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .Font.Bold = False
    .MatchCase = False
    .MatchWildcards = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .Text = ""
    .Replacement.Text = ""
    Do
    .Execute Replace:=wdReplaceOne
    If .Found And Selection.Characters.count > 2 Then
    Selection.Range.Delete
    ' Selection.Delete Unit:=wdCharacter, count:=-1
    ' Selection.Delete Unit:=wdCharacter, count:=1
    Selection.TypeText Text:=vbTab
    Else
    End If
    Selection.Collapse Direction:=wdCollapseEnd

    Loop Until Selection.End = ActiveDocument.Content.End - 1
    End With
    End With
    End Sub</pre>


    Now a couple of clean ups for ^p^t and ^p^p should do it.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    Bob,

    See how this code goes

    Geoff

    <pre>Option Explicit

    Sub parse()
    Dim par As Paragraph
    Dim iPos As Long
    Dim strContact1 As String
    Dim strContact2 As String
    Dim strContacts() As String
    Dim j As Long
    Dim strPrev1 As String
    Dim strPrev2 As String
    Dim strTemp As String
    Dim strText As String
    Dim iCount As Long

    strPrev1 = ""
    For Each par In ActiveDocument.Paragraphs
    strText = par.Range.Text

    iPos = InStr(2, strText, vbTab)

    ' If tab position 1 is in the middle of the page, then there's nothing in column 1
    If par.TabStops(1).Position > 200 Then
    iPos = 1
    strPrev1 = ""
    End If

    ' If there's no tab in the line, we only half the first part of the line
    If iPos = 0 Then
    iPos = Len(strText)
    End If

    ' Work with the first part of the line (before the tab)
    ' If the previous line was blank, we have a contact name
    strTemp = Mid$(strText, 1, iPos - 1)
    If Mid$(strTemp, 1, 1) = vbTab Then
    strTemp = Mid$(strTemp, 2)
    End If
    If strPrev1 = "" And strTemp <> "" Then
    strContact1 = strTemp
    End If
    ' If there's an email, append it and output the contact details
    If InStr(strTemp, "@") > 0 Then
    strContact1 = strContact1 & vbTab & Replace(strTemp, vbTab, "")
    ReDim Preserve strContacts(iCount)
    strContacts(iCount) = strContact1
    iCount = iCount + 1
    Debug.Print strContact1
    End If
    strPrev1 = strTemp


    ' Work with the second part of the line (after the tab)
    ' If the previous line was blank, we have a contact name
    strTemp = Mid$(strText, iPos + 1)
    If Len(strTemp) > 2 Then
    If Mid$(strTemp, Len(strTemp)) = vbCr Then
    strTemp = Mid$(strTemp, 1, Len(strTemp) - 1)
    End If
    End If
    ' If Mid$(strTemp, 1, 1) = vbTab Then
    ' strTemp = Mid$(strText, 2)
    ' End If
    If strPrev2 = "" And strTemp <> "" Then
    strContact2 = strTemp
    End If
    ' If there's an email, append it and output the contact details
    If InStr(strTemp, "@") > 0 Then
    strContact2 = strContact2 & vbTab & strTemp
    ReDim Preserve strContacts(iCount)
    strContacts(iCount) = strContact2
    iCount = iCount + 1
    Debug.Print strContact2
    End If
    strPrev2 = strTemp

    Next
    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    risman
    Guest

    Re: Stripping email addresses from a distribution list

    I'm stuck at the very outset because the "columns" aren't really columns...they're set by tabs.

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Stripping email addresses from a distribution list

    Put your mouse/cursor at the start of the document hold down the ALT key and left-click-and-hold the mouse. Now whilst still holding down both the ALT and the left mouse button drag the selection to the left hand side of the right column on the last line of the document. Now cut that selection.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    By way of explanation

    This code does not appear to do anything. It sets the names up in an array- then doen't fdo anything with the array.

    If you change the "debug.print" to "msgbox" you may be able to see the results better.

    The array "strContacts" has all the names for whatever further processing is required- for instance, you might want to add this:

    Documents.Add
    For i = 0 To UBound(strContacts)
    ActiveDocument.Range.InsertAfter strContacts(i) & vbCrLf
    Next


    Which will put all the contacts into a new document
    next
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    Hi Karen:

    That was a brilliant technique. A technique that I can envision being used with other similar problems. No insult to the VBA gurus either.

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    Thanks, Phil... it (or similar) have saved me hours on more than one occasion.[img]/w3timages/icons/wink.gif[/img]
    Karen

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    Hi Bob -- The gurus have spoken. Now, for us plain and simple folks:

    I prefer the down & dirty rout unless it's something I frequently do and really need to "code" a macro. The following presumes that your document is formatted consistently (in that the right tabbed column is aligned at the same position, each address block is followed by 2 returns and that "E-Mail:" is always followed by two spaces).

    move right column to bottom of file, creating one column
    Karen

  10. #10
    risman
    Guest

    Re: Stripping email addresses from a distribution list

    Thanks very much Karen. That did the trick, although it wasn't quite as "clean" as I hoped it would be. I still had to do a lot of manual cleaning up, I presume because there were variable numbers of tabs between the 1st and 2nd columns. But the overall method worked fine.

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    [img]/w3timages/icons/grin.gif[/img]Glad it worked... although would have liked it to be cleaner by your account. Number of tabs shouldn't have affected it because it removed the tabs after moving the column down... but yes, variables would affect it.
    Karen

  12. #12
    risman
    Guest

    Re: Stripping email addresses from a distribution list

    This gets part of the way there--it gets rid of all the text between the name and the email address, but it joins everything together. See the 2 files attached. The first (testpg.doc) is a test page after putting in one column and bolding the email address. The 2nd (testpg after macro.doc) is what it looks like after running the macro.

  13. #13
    risman
    Guest

    Re: Stripping email addresses from a distribution list

    Guess you can't attach 2 files to a post. Here's the 2nd one.

  14. #14
    risman
    Guest

    Re: Stripping email addresses from a distribution list

    Geoff--I've tried every variation of this macro that I could think of, but nothing works. I get "End If without block If" messages or other errors. Where does the addition fit in (I've substituted iCount for i)? Could you try it with the test page I sent?

  15. #15
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping email addresses from a distribution list

    Bob,

    Here's the original page, with the macro attached. It works OK for me in W97- creating a new document with the names & email addresses
    Attached Files Attached Files
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •