Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Help (97 SR2)

    I've got over 500 returned mails, so i'm hoping for an easy way to do this:

    I've got all 500 e-mails into one huge document, over 600 pages long.

    Ideally, it would identify Only the e-mail address, and copy it into another document. I felt the best way to do this would be to identify the "@" symbol, and use that as a reference, but i'm having some problems working it out...

    Has anyone out there done anything like this? Does anyone have any ideas on what I can to do make it happen?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    St. Louis, Missouri, USA
    Posts
    67
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I'm a total amateur, so this may not be helpful at all, but just in case . . .
    We use the following macro to select an entire paragraph and then apply a style whose name is 1.

    Sub Style1()
    ActiveWindow.ActivePane.View.Type = wdOutlineView
    Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
    If ActiveWindow.View.SplitSpecial = wdPaneNone Then
    ActiveWindow.ActivePane.View.Type = wdNormalView
    Else
    ActiveWindow.View.Type = wdNormalView
    End If
    Selection.Style = ActiveDocument.Styles("1")
    End Sub

    I can't find it in Help, but could you in a similar way expand the selection to one word, which presumably would be the whole address? Then your macro could find the @ sign, select the whole word, and copy it to your new document?

    Lin (knows just little enough to be dangerous)

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    Hrmph...

    There are several tools online to do it, but I think it would be very helpful if someone could identify a way of doing it here...

    One of the things that complicates things, is that not all e-mail addresses are user@domain.com, some are user.last@domain.com...

    I had hoped someone had scripted this out before, or had some insight on a sure fire way of doing this...

    I have what I need until the next 'corporate communication' goes out... (I heard through the grapevine, that's on Monday... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>)

    Thanks!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Macro Help (97 SR2)

    Hewre's something to strip out email addresses:<pre>Dim strText As String
    Dim strWord As String
    Dim iPos As Integer

    strText = Replace(ActiveDocument.Range.Text, vbCr, " ")
    strText = Replace(strText, vbLf, " ")

    iPos = InStr(strText, " ")
    Do Until iPos = 0
    strWord = Mid$(strText, 1, iPos - 1)
    If InStr(strWord, "@") > 0 Then
    Debug.Print strWord
    End If
    strText = Mid$(strText, iPos + 1)
    iPos = InStr(strText, " ")
    Loop</pre>


    For Office 97, which does not have a Replace function, you would additionally need this:<pre>Function Replace(Source As String, Searchfor As String, _
    ReplaceBy As String)

    Dim lngPos As String

    lngPos = InStr(LCase$(Source), LCase$(Searchfor))

    If lngPos = 0 Then
    Replace = Source
    Else
    Replace = Left(Source, lngPos - 1) & ReplaceBy & _
    Mid$(Source, lngPos + Len(Searchfor))
    End If
    End Function
    </pre>

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

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    Are you trying to parse the file to get the email addresses of the intended recipients?

    If so, I'd search for the To and Cc headers, extract those headers and work from there.

    If you are trying to do some other parsing, then you need to get the RFC that describes the standard format for email, and hope that your email software followed that RFC.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro Help (97 SR2)

    I think you would need to work with the document in somewhat smaller chunks, as a VBA string cannot exceed about 64K characters. I don't have a sense of how many pages that is, but I'm guessing it's less than 600.

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro Help (97 SR2)

    Let's start with two assumptions (I was an economics major):

    1. E-mail addresses contain an "@" and are bounded on both ends by spaces and/or carriage return/line feed pairs.
    2. All letter strings that meet criterion 1 are e-mail addresses.

    With this in mind, how about something like this:

    <pre>Sub ExtractAddresses()
    '2001-09-07 Jefferson Scher
    'licensed for use by non-spammers only
    Dim varBoundaryCharacters As Variant, rngAddress As Range
    varBoundaryCharacters = " " & vbCrLf
    Set rngAddress = ActiveDocument.Content
    With rngAddress.Find
    .ClearFormatting
    .Text = "@"
    .Forward = True
    .Wrap = wdFindStop
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    End With
    Do
    With rngAddress
    .Find.Execute
    If .Find.Found = False Then Exit Do
    'move start of range to beginning of address
    .MoveStartUntil varBoundaryCharacters, wdBackward
    'move end of range to end of address
    .MoveEndUntil varBoundaryCharacters, wdForward
    'sock it away somewhere
    MsgBox "<" & .Text & ">" 'for debug only, in the real world, save this
    .Collapse wdCollapseEnd
    End With
    Loop
    End Sub</pre>

    If you need additional "boundary" indicators, append them to varBoundaryCharacters. (If this doesn't bring tears of joy to your eyes, I don't know what will. The more Range methods I learn, the punchier I get.)

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    You've got in your comments that i need to apply this to the document somehow..

    Ideally, I would take the contents of that message box, put it in a new document, followed by a carriage return. The result would be a list of e-mail addresses which were in the document.

    How can I do this? I've tried it from nearly every angle, and can't seem to get it down..

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Oregon, USA
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    Hi Drk,

    The following untested should work.

    In the declarations, add:

    <pre>Dim strEmail as String</pre>


    Replace the MsgBox line with:

    <pre>strEmail = strEmail & .Text & vbcr</pre>


    At the very end of the sub add:

    <pre>Documents.Add
    Activedocument.Range.InsertAfter strEmail</pre>


    Chris

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro Help (97 SR2)

    Until you've done it once, this does seem kind of daunting, but the changes are really pretty straightforward. The key is to create two document objects so you can be 100% clear about where you are doing what.
    <pre>Sub ExtractAddresses()
    '2001-09-07 Jefferson Scher
    'licensed for use by non-spammers only
    Dim doc1 As Document, doc2 As Document
    Set doc1 = ActiveDocument
    Set doc2 = Documents.Add

    Dim varBoundaryCharacters As Variant, rngAddress As Range
    varBoundaryCharacters = " " & vbCrLf
    Set rngAddress = doc1.Content
    With rngAddress.Find
    .ClearFormatting
    .Text = "@"
    .Forward = True
    .Wrap = wdFindStop
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    End With
    Do
    With rngAddress
    .Find.Execute
    If .Find.Found = False Then Exit Do
    'move start of range to beginning of address
    .MoveStartUntil varBoundaryCharacters, wdBackward
    'move end of range to end of address
    .MoveEndUntil varBoundaryCharacters, wdForward
    'write it to doc2
    doc2.Range.InsertAfter "<" & .Text & ">" & vbCrLf
    .Collapse wdCollapseEnd
    End With
    Loop
    doc2.Activate
    End Sub</pre>

    And man, is it fast!

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    Any ideas on how I might manipulate this to work in Excel?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help (97 SR2)

    Geoff,

    VBA is pretty forgiving. I was putting your 97 replace function into my toolkit and noticed your var lngPos is declared as type String. I'm guessing you meant Long by the naming convention.

    The function works fine when VBA evaluates the conditional when lngPos = "0"

    If lngPos = 0 then
    'Condition is true
    Endif

    Therefore, "0" equals 0. Interesting. Must have something to do with the fact that instr returns a variant which is what a string really is??? And a variant type zero equals an integer type zero???
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro Help (97 SR2)

    I'm useless at Excel programming, but there are two gurus on the VBA board around the clock. Try posting a link back to this thread over there to see if you get some suggestions.

Posting Permissions

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