amazonがﾘﾌｧﾗを見ているようなので､いったん画像ﾌｧｲﾙをﾃﾝﾎﾟﾗﾘにﾀﾞｳﾝﾛｰﾄﾞしてから表示した方がいいようです｡
ﾃﾝﾎﾟﾗﾘのﾌｧｲﾙ名は tmpfname に指定してください｡

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 GetImageFile(ImgName As String, SaveName As String)
 Dim SaveFileName As String, DownloadFile As String, Ret As Long
 If ImgName = "" Then Exit Sub
 SaveFileName = SaveName
 DownloadFile = ImgName
 Ret = URLDownloadToFile(0, DownloadFile, SaveFileName, 0, 0)
 If Ret <> 0 Then
 MsgBox "ｴﾗｰが発生しました"
 End If
End Sub

Sub test()
 Dim imgfname As String, tmpfname As String
 imgfname = "http://ecx.images-amazon.com/images/I/51aKVkh6SFL.jpg"
 tmpfname = "c:\temp\tmp.jpg"  'ﾃﾝﾎﾟﾗﾘﾌｧｲﾙ名
 Call GetImageFile(imgfname, tmpfname)
 ActiveSheet.Pictures.Insert (tmpfname)
End Sub