Word Format address with USPS and get extra four digits

Joe likes pizza · #1 04-22-2024, 05:45 AM

I would like to know how to format a US address with the post office website to get the correct formatting and extra four digits for the zip code. I am using Word 2021.

Ideally, I would like to highlight the address and for it to automatically update the address. Can this be done with vba? If so, how?

The website that has this information is from
ZIP Code™ Lookup | USPS

How can this be done?

RMittelman · #2 04-22-2024, 01:44 PM

I have a common routine which does this, but it requires you create an account with the USPS to use their API. It uses XML format, so you need a reference to Microsoft MsXML library. It is run from VBA code in an Access database.

Here's the common code:

Code:

'
' Validate address with USPS
' returns valid address XML or error XML
'
' requires valid requester ID assigned by USPS
'
Public Function ValidateAddress(Address1 As String, Address2 As String, City As String, State As String, _
                                Optional Zip As String = "", Optional Zip4 As String = "") As String

    Dim Doc         As Object
    Dim xhr         As Object
    Dim url         As String
    Dim request_xml As String
    Dim isOK        As Boolean
    
    Set Doc = CreateObject("MSXML2.DOMDocument")
    request_xml = "<AddressValidateRequest USERID=''><Address><Address1/><Address2/><City/><State/>" _
                & "<Zip5/><Zip4/></Address></AddressValidateRequest>"
    
    isOK = Doc.LoadXML(request_xml)
    If isOK Then
        
        With Doc.DocumentElement
            .SelectSingleNode("//AddressValidateRequest").Attributes(0).Text = TempVars!UspsValidateId
            .SelectSingleNode("//Address1").Text = Address1
            .SelectSingleNode("//Address2").Text = Address2
            .SelectSingleNode("//City").Text = City
            .SelectSingleNode("//State").Text = State
            .SelectSingleNode("//Zip5").Text = Zip
            .SelectSingleNode("//Zip4").Text = Zip4
        End With
        
        Set xhr = CreateObject("Microsoft.XMLHTTP")
        url = TempVars!UspsValidateBaseUrl & Doc.Xml
        xhr.Open "GET", url, False
        xhr.Send
        
        'return response or error message
        If xhr.Status = 200 Then
          ValidateAddress = xhr.responseText
        Else
          ValidateAddress = xhr.Status & ": " & xhr.StatusText
        End If
        Set xhr = Nothing
        
    Else
        ValidateAddress = ""
    End If
    Set Doc = Nothing

End Function

This routine uses a temporary variable called TempVars!UspsValidateId, which was previously loaded with the USPS ID they supplied. You can store the ID, once obtained from USPS, in any way desired.

The routine returns XML text which is the reply from the USPS API.

Here is the code which calls the above function:

Code:

Private Sub validate_address()

    Dim doc     As Object
    Dim nod     As Object
    Dim isOK    As Boolean
    
    Set doc = CreateObject("MSXML2.DOMDocument")
    isOK = doc.LoadXML(ValidateAddress(Nz(Me.Address, ""), Nz(Me.Address2, ""), Nz(Me.City, ""), Nz(Me.State, ""), Nz(Me.Zip, ""), Nz(Me.Zip4, "")))
    
    If isOK Then
        With doc.DocumentElement
            isOK = .SelectSingleNode("//Error") Is Nothing
            If isOK Then
                Set nod = .SelectSingleNode("//Address1")
                If nod Is Nothing Then
                    Me.Address = Null
                Else
                    Me.Address = nod.Text
                End If
                Set nod = .SelectSingleNode("//Address2")
                If nod Is Nothing Then
                    Me.Address2 = Null
                Else
                    Me.Address2 = nod.Text
                End If
                Set nod = .SelectSingleNode("//City")
                If nod Is Nothing Then
                    Me.City = Null
                Else
                    Me.City = nod.Text
                End If
                Set nod = .SelectSingleNode("//State")
                If nod Is Nothing Then
                    Me.State = Null
                Else
                    Me.State = nod.Text
                End If
                Set nod = .SelectSingleNode("//Zip5")
                If nod Is Nothing Then
                    Me.Zip = Null
                Else
                    Me.Zip = nod.Text
                End If
                Set nod = .SelectSingleNode("//Zip4")
                If nod Is Nothing Then
                    Me.Zip4 = Null
                Else
                    Me.Zip4 = nod.Text
                End If
                Me.UspsValidation = FilterByEnum.UspsValid
            Else
                MsgBox "Error validating address with USPS:" & vbCrLf & doc.DocumentElement.SelectSingleNode("//Description").Text
                Me.UspsValidation = FilterByEnum.UspsInvalid
            End If
        End With
    Else
        MsgBox "Could not validate address with USPS." & vbCrLf & "Do you have an internet connection?", vbExclamation, "Error"
    End If
    Set doc = Nothing
    Set nod = Nothing

End Sub

The line near the top shows with blue where this subroutine calls the function.

The orange lines show what my program does with the results depending on success or failure. You can do whatever you want instead.

There is no need to supply the zipcode to this routine, the zip and zip+4 fields are optional. This information will be supplied by (or corrected by) the USPS.

I'm not sure if this works perfectly running from Word VBA. It should, with a little bit of tinkering. Remember, if you put this code in a Word document, it will need to be saved as a Macro-Enabled document, with the extension "docm" instead of "docx".

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Digits on the Word ruler	RRB	Word	3	02-21-2024 12:59 PM
Format a merge field with 22 digits	mason1100	Mail Merge	5	04-01-2023 09:20 PM
USPS Intelligent Mail Encoder How to Remove Text from Column?	16NOV2020	Excel Programming	7	11-17-2020 08:30 AM
Extra space in address field but no blanks in source	DIYman	Mail Merge	3	09-17-2015 03:19 AM
Problems merging in last 4 digits of an account higher than 16 digits	Glynda	Mail Merge	1	04-08-2011 12:17 AM