Excel macro to download file from a email body URL.

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.

Code:

---

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

---

Quote:

---

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?

Quote:

---

Originally Posted by westconn1

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

---

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

Quote:

---

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

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

Quote:

---

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

Code:

---

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

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.

Code:

---

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]")

---

to restirct by subject try

Code:

---

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

---

if you want to restrict by both try like

Code:

---

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