Image path

[me_king]

I have an image in excel which i want to use as a hyperlink image. The link is sent to all users through mail. I can send hyperlinks but what I want is that the image to be the hyperlink , something like this :

"<br>" & "<a href=" & Replace(url, " ", "%20") & "> <img src=" & picture1 & " alt=" & "timer.GIF" & "/></a>"

[JosephP]

if the image isn't stored somewhere everyone can access it I think you'll have to encode the file into base64 and embed that string into the source. you may need to save the file to disk before you can do that.

[me_king]

if the image isn't stored somewhere everyone can access it I think you'll have to encode the file into base64 and embed that string into the source. you may need to save the file to disk before you can do that.

I just want to insert the image into the html tag mentioned above. I tried using names but i think html tags cant read that so i have to specify the path of that image..

[JosephP]

the image does not have a path unless you extract it from the container file (if it's an openxml format file) and specifying a local path won't help since nobody else will be able to access it.

[me_king]

the image does not have a path unless you extract it from the container file (if it's an openxml format file) and specifying a local path won't help since nobody else will be able to access it.

So is there any way i can specify the img src tag in html to take the image in excel. Actually I am sending the link of a file path to all users through outlook... and its automated. So for the body of the outlook i have that html coding.... Earlier i used to simply sent the hyperlink but now i want the image to be the hyperlink. Could it be done...

[JosephP]

you can write the file to disk then encode it and embed that data using code like this

Function GetPicCode(shp As Shape, sPath As String) As String
    Dim oPic As Object
    Dim cht As Chart
    Dim shp As Shape
    
    With shp
        Set cht = .Parent.Shapes.AddChart(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height).Chart
        .CopyPicture xlScreen, xlPicture
    End With
    cht.Paste
    cht.Export sPath, "GIF"
    cht.Parent.Delete
    GetPicCode = Base64Encode(sPath)
End Function
Function Base64Encode(strFilePath As String)
   Dim oXML, oNode

   Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
   Set oNode = oXML.CreateElement("base64")
   oNode.DataType = "bin.base64"
   oNode.nodeTypedValue = FileToBinary(strFilePath)
   Base64Encode = oNode.Text
   Set oNode = Nothing
   Set oXML = Nothing
End Function

Function FileToBinary(strFilePath As String)
'Create Stream object
   Dim BinaryStream      As Object
   Const adTypeBinary As Long = 1
   Set BinaryStream = CreateObject("ADODB.Stream")

   With BinaryStream
      .Type = adTypeBinary
      .Open
      .LoadFromFile strFilePath
      FileToBinary = .Read
   End With

   Set BinaryStream = Nothing
End Function

which you call with this

Dim sBody as string
Dim sPicPath as string
sPicPath = ThisWorkbook.Path & "\Temp.gif"

    sBody = "<br><a href=" & Replace(url, " ", "%20") & "> <img src=""data:image/gif;base64," & GetPicCode(activesheet.shapes("Picture 1"), sPicPath) & """ alt=""timer.GIF""/></a>"

as an example.

[me_king]

you can write the file to disk then encode it and embed that data using code like this

Function GetPicCode(shp As Shape, sPath As String) As String
    Dim oPic As Object
    Dim cht As Chart
    Dim shp As Shape
    
    With shp
        Set cht = .Parent.Shapes.AddChart(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height).Chart
        .CopyPicture xlScreen, xlPicture
    End With
    cht.Paste
    cht.Export sPath, "GIF"
    cht.Parent.Delete
    GetPicCode = Base64Encode(sPath)
End Function
Function Base64Encode(strFilePath As String)
   Dim oXML, oNode

   Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
   Set oNode = oXML.CreateElement("base64")
   oNode.DataType = "bin.base64"
   oNode.nodeTypedValue = FileToBinary(strFilePath)
   Base64Encode = oNode.Text
   Set oNode = Nothing
   Set oXML = Nothing
End Function

Function FileToBinary(strFilePath As String)
'Create Stream object
   Dim BinaryStream      As Object
   Const adTypeBinary As Long = 1
   Set BinaryStream = CreateObject("ADODB.Stream")

   With BinaryStream
      .Type = adTypeBinary
      .Open
      .LoadFromFile strFilePath
      FileToBinary = .Read
   End With

   Set BinaryStream = Nothing
End Function

which you call with this

Dim sBody as string
Dim sPicPath as string
sPicPath = ThisWorkbook.Path & "\Temp.gif"

    sBody = "<br><a href=" & Replace(url, " ", "%20") & "> <img src=""data:image/gif;base64," & GetPicCode(activesheet.shapes("Picture 1"), sPicPath) & """ alt=""timer.GIF""/></a>"

as an example.

Appreciate your time and effort...will let you know how it goes....

[me_king]

Appreciate your time and effort...will let you know how it goes....

Image but with an "x" i mean invalid image, the file gets saved and i have checked the path is also correct. Don't know for some reason its not showing the image.

[me_king]

Image but with an "x" i mean invalid image, the file gets saved and i have checked the path is also correct. Don't know for some reason its not showing the image.

Yes it worked but what I did is that I saved the image and then used the same path to refer to it, used piccode function and removed the other ones...

[JosephP]

since I don't know what you were doing with the output I can't comment. I tested the code by pasting the output into an htm file and that loaded the image perfectly in IE.