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:

11330

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
www.vbforums.com/showthread.php?671093-RESOLVED-Excel-VBA-Internet-Explorer-Download-Problem&highlight=close+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 (http://www.vbaexpress.com/forum/member.php?67078-rgonuguntla)

Your question was moved to http://www.vbaexpress.com/forum/showthread.php?62327-Download-File-from-Hyperlink