问题:

So I receive a weekly email that always has the same form and has a hyperlink to a PDF file in the body. I know how to parse the email to retrieve the URL, but is it possible to have VBA code then download that file from the hyperlink/url and save it in a folder?

回答1:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
  "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
    szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub DownloadFile(sURL, sSaveAs)
    Dim rv As Long

    rv = URLDownloadToFile(0, sURL, sSaveAs, 0, 0)
    If rv = 0 Then
        MsgBox "Download has been succeed!"
    Else
        MsgBox "Error"
    End If
End Sub

回答2:

I ended up using a different method I found on another board, though I know most of the examples I could find used UrlDownloadtoFile. The code is below:

Dim myURL As String
myURL = "http://www.somesite.com/file.csv"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile ("C:\file.csv")
    oStream.Close
End If