Download File from Hyperlink in Body of Email

[Gremie]

Hi,

I am trying to automate the process of downloading a file which is contained at a web address that I receive a hyperlink to in a daily email. I have found various sources to follow the hyperlink and open the web address. However, I am now stuck when the internet explorer downloads box opens and asks me whether to Open, Save or Save As. I have no idea how to control this dialogue box. Can any one help? Thank you in advance!

This is the download manager which I need to control:

Download Manager.jpg

Here is my code so far:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub MyCustomSub (Item 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 IE As Object       
       
Const UNC = "MyDestination\"
bodyString = itm.Body    
bodyStringSplitLine = Split(bodyString, vbCrLf)
For Each splitLine In bodyStringSplitLine        
bodyStringSplitWord = Split(splitLine, " ")
        For Each splitWord In bodyStringSplitWord            
Test = InStr(splitWord, "MyHyperlink.HTML")            
If Test = 1 Then            
Hyperlink = splitWord            
End If                    
Next
    Next
    filename = "MyFilename" & Date   
LocalFileName = UNC & filename     
ShellExecute 0, "open", "C:\Program Files\Internet Explorer\iexplore.exe", Hyperlink, vbNullString, 1
End Sub 

[westconn1]

This is the download manager which I need to control:

see this thread that was to solve similar issue
http://www.vbforums.com/showthread.p...+saveas+dialog
you will need to change he dialog names etc

alternatively you could get all the links and use urldownloadtofile API to download with no dialogs
on testing, parsing out all the links has some problems fining where the links finish, but i found this to work correctly to return all links in an email

Dim m As MailItem
Set m = ActiveExplorer.Selection(1)
fn = "c:\temp\htmltest.html"
m.SaveAs fn, olHTML
Set wb = CreateObject("internetexplorer.application")
wb.navigate2 fn
Do Until wb.readyState = 4: DoEvents: Loop
For Each ele In wb.document.getElementsByTagName("a")
    Debug.Print ele.href  'you can use urldownloadtofile here
Next
wb.Quit

[Gremie]

I actually managed to solve this in the last half an hour.
Here is the code which works. I had been using URLdownloadtofile but it was not working previously because of a date into string issue.

Sub mysub(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 & "myfile.CSV", 0, 0)
    
If myResult <> 0 Then        
MsgBox "Error downloading " & Hyperlink & Chr(10) & Error(myResult)    End If     
itm.UnRead = False
    Set itm = Nothing    
End Sub

Thanks!

[SamT]

rgonuguntla

Your question was moved to http://www.vbaexpress.com/forum/show...from-Hyperlink