Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Macro for grabbing letter document mail address (office word 2007)

    Is there a macro that will scan a letter document and grab the TO: name and address and copy it to the clip board. I have my own envelope templates and would like to paste the name and address into it automatically. Similar to the letter macro in word but I don't want to use their envelopes and formatting.
    Can I get at that code and use it???
    Thanks
    Jerome

  2. #2
    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 for grabbing letter document mail address (office word 2007)

    When I want to create an envelope in Word 2003, I have to select the address block myself first. Does Word 2007 automatically recognize an address block?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    >scan a letter document and grab the TO: name and address and copy it to the clip board.
    I wrote a macro that will take a selection and create an envelope, complete with return address.
    It is called "Make Envelope From Selection", and you can still d/l a free copy from
    http://vbasolutions.ca/WordUser.htm while supplies last.

    You will want to install your own Personal details, using the !P button.
    Attached Images Attached Images
    • File Type: jpg 1.JPG (35.1 KB, 0 views)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    Hi I went to this site. but I don't believe that i still see the macro.
    Could you please clarify
    Thanks
    Jerome

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    >but I don't believe that i still see the macro.
    My apologies. You are sane!
    It is I who forgot to mention that it sits within Under-The-Hood.
    Please return there and click on the left-hand link to read more, the right-hand link to download the zip file.
    (Image cropped to fit on Woody's Lounge)
    Attached Images Attached Images
    • File Type: jpg 2.JPG (11.9 KB, 0 views)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    Okay, I downloaded it and in word 2007 when I open it it appears to either do nothing or crash word 2007
    For the envelope is there a way of grabbing the macro ? name ?/ visual basic and running just the macro ???
    Thanks Jerome

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    >when I open it it appears to either do nothing or crash word 2007
    I'm not sure why you are opening it.
    Did you follow the instructions in the ReadMe file and install it properly?
    I have installed this on Words 2000/2002/2003/2007 successfully in the past.
    I am currently running it on a daily basis on Word 2000, so it should run on Word2007.

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    THanks, but no good, results in a conflict with office and an HP Printer driver, known issue per Microsoft .
    Do you have the vba for this macro only that I can paste into the normal. dot macro ie create a new macros??
    THanks
    Jerom

  9. #9
    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 for grabbing letter document mail address (office word 2007)

    Got a chance to play with Word 2007 this morning. Can you use something like this? If there is no envelope in the document, it inserts one, grabs the address, and then runs Undo. It returns a string; I didn't try to figure out how to select and copy the address.

    <code>Function GetEnvelopeAddress() As String
    ' Experimentally developed in Word 2007
    Dim strAddress As String, blnDel As Boolean
    With ActiveDocument
    ' Try to access address
    On Error GoTo CreateEnv
    strAddress = .Envelope.Address
    If blnDel Then .Undo
    On Error GoTo 0
    End With
    If Len(Trim(strAddress)) > 0 Then
    GetEnvelopeAddress = Trim(strAddress)
    Else
    GetEnvelopeAddress = vbNullString
    End If
    Exit Function

    CreateEnv:
    If Err.Number = 5852 Then ' No envelope
    ActiveDocument.Envelope.Insert
    blnDel = True
    Err.Clear
    Resume
    Else
    MsgBox "Error accessing envelope address: " & _
    Err.Number & " - " & Err.Description
    Err.Clear
    Resume Next
    End If
    End Function

    Sub TESTGetEnvelopeAddress()
    MsgBox GetEnvelopeAddress
    End Sub
    </code>

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail address (office word 2007)

    <P ID="edit" class=small>(Edited by jscher2000 on 09-Nov-08 17:03. Reformatted for better readability using [tab] and [code].)</P>Hi thanks , I tried copying and pasting as is into macros visual basic, and I think somehow it is wrong, can't seem to run it just errors out after message box
    I pasted the code right after the botox macro

    <code>Sub BotoxConsent()
    '
    ' BotoxConsent Macro
    ' Macro recorded 11/13/2006 by Jerome Klein MD
    '
    Documents.Open filename:="""c:msofficewinwordconsent botox.doc""", ConfirmConversions:= _
    False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    End Sub

    Function GetEnvelopeAddress() As String
    ' Experimentally developed in Word 2007
    Dim strAddress As String, blnDel As Boolean
    With ActiveDocument
    ' Try to access address
    On Error GoTo CreateEnv
    strAddress = .Envelope.Address
    If blnDel Then .Undo
    On Error GoTo 0
    End With
    If Len(Trim(strAddress)) > 0 Then
    GetEnvelopeAddress = Trim(strAddress)
    Else
    GetEnvelopeAddress = vbNullString
    End If
    Exit Function

    CreateEnv:
    If Err.Number = 5852 Then ' No envelope
    ActiveDocument.Envelope.Insert
    blnDel = True
    Err.Clear
    Resume
    Else
    MsgBox "Error accessing envelope address: " & _
    Err.Number & " - " & Err.Description
    Err.Clear
    Resume Next
    End If
    End Function
    End Sub
    Sub TESTGetEnvelopeAddress()
    MsgBox GetEnvelopeAddress
    End Sub
    </code>

  11. #11
    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 for grabbing letter document mail address (office word 2007)

    There seems to be an extra "End Sub" after "End Function". To know whether that is a problem, try the compiler. In the VBE, Debug>Compile.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail address (office word 2007)

    sorry just can't get it to work, this is how i have it pasted in after the botox macro
    i run test get env address macro and just gives me blank message box
    i don't know what other macro it references??
    Jerome
    Sub BotoxConsent()
    '
    ' BotoxConsent Macro
    ' Macro recorded 11/13/2006 by Jerome Klein MD
    '
    Documents.Open filename:="""c:msofficewinwordconsent botox.doc""", ConfirmConversions:= _
    False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    End Sub

    Function GetEnvelopeAddress() As String
    ' Experimentally developed in Word 2007
    Dim strAddress As String, blnDel As Boolean
    With ActiveDocument
    ' Try to access address
    On Error GoTo CreateEnv
    strAddress = .Envelope.Address
    If blnDel Then .Undo
    On Error GoTo 0
    End With
    If Len(Trim(strAddress)) > 0 Then
    GetEnvelopeAddress = Trim(strAddress)
    Else
    GetEnvelopeAddress = vbNullString
    End If
    Exit Function

    CreateEnv:
    If Err.Number = 5852 Then ' No envelope
    ActiveDocument.Envelope.Insert
    blnDel = True
    Err.Clear
    Resume
    Else
    MsgBox "Error accessing envelope address: " & _
    Err.Number & " - " & Err.Description
    Err.Clear
    Resume Next
    End If
    End Function
    Sub TESTGetEnvelopeAddress()
    MsgBox GetEnvelopeAddress
    End Sub

  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 for grabbing letter document mail address (office word 2007)

    The function uses Word 2007's built in envelope feature to find the address automatically, as you described in your original post. Does the built-in envelope feature work with the document you are testing? If not, then the macro will not work, either. If it does, then the macro should work as well.

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail address (office word 2007)

    Hi good point, I checked the microsoft and it did not work so I redid the letter (i had used a pasted name and address) and now it works. THe macro also works in that it gives the name and address in the message box. But does Not create an envelope to print or appended. I run Test Get envelope address under the macros. Is there another macro name i should be trying??
    Thanks Jerome

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for grabbing letter document mail addres

    >results in a conflict with office and an HP Printer driver, known issue per Microsoft .
    It sounds to me as if you have more serious problems with your system.
    My macros don't go anywhere near laser printers.
    I suggest that you resolve your known conflicts before struggling to install macros that seem to work well on other systems.
    I have pasted the macro below.
    It won't do you much good pasted into Normal.dot, because it makes use of a library of established procedures which, you seem to indicate, conflict with your laser printer!
    In particular it pastes an advertisement (allowable by Canada post) top-centre of the envelope, and loads a return address, all of which (functions) are supported by librray functions within Under. (If you explore the Under toolbar you'll see a slew of envelope-related functions there)
    I note too that Jefferson's solution seems not to work on your system

    <pre>Public Sub MakeEnvelopeFromSelection()
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''
    ''' Function: MakeEnvelopeFromSelection
    '''
    ''' Comments: Use the selected text as an address, and build a printable envelope on that text.
    '''
    ''' Arguments: None.
    '''
    ''' Returns: None
    '''
    ''' Date Developer Action
    ''' --------------------------------------------------------------------------
    ''' 2006/03/21 Chris Greaves Created
    '''
    Call Statistics("MakeEnvelopeFromSelection")
    ' First collect the address text from the active document
    If Len(Selection().Text) <= 1 Then
    Selection.MoveUp Unit:=wdParagraph, Count:=1
    Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
    Else
    End If
    Dim strText As String
    strText = strTrim(strfixpath(strEnv) & strcApplication, strcApplication, Selection.Text, strcApplication)
    Call docEnv_MakeDocument
    Call MakeEnvelopeTable
    Call LoadReturnAddress
    Selection.MoveRight Unit:=wdCell, Count:=1
    Call LoadAdvertisement
    Selection.MoveRight Unit:=wdCell, Count:=3
    ' Now key in the saved text from above
    If lngStyleInDocument(ActiveDocument, "EnvAddress") > 0 Then
    Else
    Call AddStyle("EnvAddress")
    End If
    Selection.Style = ActiveDocument.Styles("EnvAddress")
    Selection.TypeText (strText)
    Call FormatAddressCell(Selection)
    Call DocUnDirty(ActiveDocument)
    End Sub</pre>


Page 1 of 3 123 LastLast

Posting Permissions

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