Excel macro to download file from a email body URL.

[abhay_547]

I have email which I receive daily consisting of a hyperlink/URL, when i click on that link it gives me the option in internet explorer to open, save or save as, the file link is something like "http://www.somesite.com/servelet/retrievefile?instanceid=777541" now the last six digit keep changing and rest of the URL remains same. What I want to achieve through macro is to look for the recent email and download the file to a specific location. below is what i have so far, i get the error "Run time error '91' object variable or with block variable not set" on sDate = .... line

I want to download it from a specific outlook folder from a specific subject line email with specific date, all three criteria's should be set through excel worksheet cell.

Option Explicit
'Declarations
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 downloadfromurl ()
Dim itm As Outlook.MailItem    
Dim bodyString As String    
Dim bodyStringSplitLine    
Dim bodyStringSplitWord    
Dim splitLine    
Dim splitWord    
Dim Hyperlink As String    
Dim LocalFileName As String    
Dim sURL    As String    
Dim filename As String    
'Dim itm As Outlook.MailItem    
Dim myResult As Integer        
'Set itm = Application.ActiveInspector.CurrentItem    
sDate = Format(itm.SentOn, "dd mm yyyy")        
Const UNC = "myfolder\"
bodyString = itm.Body    
bodyStringSplitLine = Split(bodyString, vbCrLf)
    For Each splitLine In bodyStringSplitLine        
bodyStringSplitWord = Split(splitLine, " ")
        For Each splitWord In bodyStringSplitWord            
Test = InStr(splitWord, "myhyperlink")            
If Test = 1 Then            
Hyperlink = splitWord            
End If                    
Next
Next               
myResult = URLDownloadToFile(0, Hyperlink, UNC & "sourcefile.xlsx", 0, 0)
    
If myResult <> 0 Then        
MsgBox "Error downloading " & Hyperlink & Chr(10) & Error(myResult)    End If     
itm.UnRead = False
    Set itm = Nothing    
End Sub