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

[westconn1]

object variable or with block variable not set

the line above which sets the object is commented out, also the line to dimension the variable
if you want to search for specific emails first you would need to do that instead, though you would probably still need the variable

is your code in outlook or?

[Zvoni]

*snipp* also the line to dimension the variable
*snipp*

It's not. It's his first Line of the Sub

[westconn1]

It's not. It's his first Line of the Sub

quite right, i missed that one only saw the commented one lower down the list

[abhay_547]

My code is in excel 2010, how can filter it for a specific subject line and to search in specific outlook folder

[westconn1]

how can filter it for a specific subject line and to search in specific outlook folder

once you set the correct mapi folder you can use the restrict method of the items collection to filter by subject

the below code shows how to set an object of the messages collection from the contacts folder, restrict by receivedtime and sort the restricted collection, you can edit to the correct folder and subject line

set olapp = createobject("outlook.application")
Set msgs = olapp.Session.GetDefaultFolder(olFolderInbox).Items
Set nm = msgs.Restrict("[ReceivedTime] >= '" & Format(CDate(msg.ReceivedTime), "mm/dd/yyyy hh:nn") & "'")
nm.Sort ("[receivedtime]")

you need to check if the outlook constants are valid, if not assign the constant values, or use literal values

[abhay_547]

Ok. I have added the folder reference below. Now I have the subject line in Cell B2 in the Sheet1 and Received Date in the Cell B3 of Sheet1, how do I refer to the same in the restrict condition.

set olapp = createobject("outlook.application")
Set msgs = olapp.Session.GetDefaultFolder(olFolderInbox).Folders("ITR").Items
Set nm = msgs.Restrict("[ReceivedTime] >= '" & Format(CDate(msg.ReceivedTime), "mm/dd/yyyy hh:nn") & "'")
nm.Sort ("[receivedtime]")

[westconn1]

to restirct by subject try

set nm = msgs.restrict("[Subject}") = '" & range("b2") & "'")

if you want to restrict by both try like

set nm = msgs.restrict("[Subject]") = '" & range("b2") & "' and "[ReceivedTime] >= '" & Format(range("b3"), "mm/dd/yyyy hh:nn") & "'")

i did not test either example, so may contain typing or other errors