Emacs development version now has this feature builtin (see this commit). This will go live in Emacs 25.1. You just have to issue M-x ffap RET. Below there is my old answer, for reference.
Starting from David's answer, I've written the following functions (they need AUCTeX).
(defun mg-TeX-kpsewhich-find-file (&optional name)
"Visit file associated to NAME searching for it with kpsewhich.
If NAME is nil prompt for a file name. If there is an active
region, use it as initial input. When it is called with
\\[universal-argument] prefix, visit file in another window, in
the current one otherwise."
(interactive)
(if (executable-find "kpsewhich")
(let* ((fun (if current-prefix-arg 'find-file-other-window 'find-file))
(default-directory (TeX-master-directory))
(name (or name (TeX-read-string
"File name: "
(if (TeX-active-mark)
(buffer-substring-no-properties
(region-beginning) (region-end))))))
(file (replace-regexp-in-string
"[\n\r]*\\'" ""
(shell-command-to-string (concat "kpsewhich " name)))))
(if (and (not (zerop (length file))) (file-exists-p file))
(funcall fun file)
(message (concat "File " name " not found."))))
(message "Kpsewhich not available.")))
(define-key TeX-mode-map (kbd "C-c k") 'mg-TeX-kpsewhich-find-file)
(defun mg-LaTeX-find-file-at-point ()
"Visit LaTeX file searching for it with kpsewhich.
File basename is guessed from text around point and its extension
is guessed from current macro. When it is called with
\\[universal-argument] prefix, visit file in another window, in
the current one otherwise.
See also `mg-TeX-kpsewhich-find-file'."
(interactive)
(let* ((file-name-regexp "-~/A-Za-z0-9_.$#%:+")
;; Get filename at point.
(name
;; Check whether character at point is a valid file name character.
(if (string-match (concat "[" file-name-regexp "]")
(string (char-after)))
(save-excursion
(skip-chars-backward file-name-regexp)
(looking-at (concat "\\([" file-name-regexp "]+\\)"))
(TeX-match-buffer 1))))
;; Get current macro once.
(current-macro (TeX-current-macro))
;; Guess file extension based on current macro.
(extension (cond
((or (equal "usepackage" current-macro)
(equal "RequirePackage" current-macro)
(equal "RequirePackageWithOptions" current-macro))
".sty")
((or (equal "documentclass" current-macro)
(equal "documentstyle" current-macro)
(equal "LoadClass" current-macro)
(equal "LoadClassWithOptions" current-macro))
".cls")
((equal "include" current-macro) ".tex")
((equal "input" current-macro)
;; `input' macro accepts a file name with extension, in
;; that case use an empty but non-nil extension.
(if (and name (file-name-extension name)) "" ".tex"))
((equal "bibliography" current-macro) ".bib")
((equal "addbibresource" current-macro) "")
(t nil))))
(if (and name extension)
(mg-TeX-kpsewhich-find-file (concat name extension))
(message "Cannot guess file name at point."))))
(define-key LaTeX-mode-map (kbd "C-c f") 'mg-LaTeX-find-file-at-point)
As you can see, mg-LaTeX-find-file-at-point (my key binding: C-c f) works not only with \usepackage but also with \RequirePackage{,WithOptions}, \document{class,style}, \LoadClass{,WithOptions}, \include, \input, \bibliogaphy, and \addbibresource. You can search for any (La)TeX file with kpsewhich using M-x mg-TeX-kpsewhich-find-file or C-c k, and if there is an active region it will be used as initial input. This is useful for macros not known by mg-LaTeX-find-file-at-point, e.g. \lstinputlisting: you mark input file name and search for it with C-c k.
findtexmf --start filewhich useskpsewhichto find the file. – Ulrike Fischer May 09 '13 at 09:35