VBA has an Environ function. To get a list of all the Environ variables you can see, you can use the method at VBA Express. I use the USERPROFILE argument like this
Public Function MyDocsPath() As String
MyDocsPath = Environ$("USERPROFILE") & "\Documents"
You can also use the Windows API as described at My Documents + Environment Variables