Email Embedded Microsoft Browser Map

[Galius]

Hey Guys,

So I have a basic spreadsheet here where I put addresses and then click View Map and it'll load the map in an embedded Microsoft Web Browser ActiveX Control. All I want to do is right a script to basically screenshot that embedded map then paste it in an email to send.

Google Map Excel.xlsm

drive.google.com

I've uploaded the sheet to the google drive above. I tried posting images but it kept telling me too large even though they were only 250kb.

 

[Galius]

So much for those security concerns.. LOL. Thanks. I'm not a huge fan of ActiveX controls on worksheets, but it's useful to at least have the option. While you're there, do you know what version of Excel you're using and its bitness (32 bit or 64 bit)?

Hey mate, these office PCs are on Office 2016 & 32bit.

 

[Dan_W]

Ok.

Well I had a look at that site you directed me too, and that is still limited to 32bit versions of Office and not 64bit, so I can't test it on my system. To that end, would you be able to run the following test subroutine - the idea is to check to make sure that we will be able to get access to the 'handle' of the web browser control. It also gets the handles of the workbook and the application, because I want to make sure that they are all returning different numbers. Basically, whichever handle is used in the snapshot routine determines the snapshot you're going to get, so I want to be able to isolate the webbrowser from the worksheet, etc, if that makes sense. I know that it looks like a lot, but I pulled this together before I saw your response re: using 32bit machines.

You will see that it identifies the WebBrowser as Sheet1.WebBrowserMap. I took this from your sample workbook. Please correct this if it has since changed. The following code should be inserted into the project workbook as a new standard module. Then run the TestSubroutine. It will show a messagebox - essentially, we're looking to make sure that there are no zeros. Let me know how it goes or if you have any questions.

It should look something like:

#If VBA7 Then
    #If Win64 Then
        Const Message   As String = "VBA7/Win64"
    #Else
        Const Message   As String = "VBA7"
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hWnd As LongPtr) As Long
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr

    Private hWnd        As LongPtr
    Private WBhWnd      As LongPtr
    Private ApphWnd     As LongPtr
#Else
    Const Message       As String = "Not VBA7"
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

    Private hWnd        As Long
    Private WBhWnd      As Long
    Private ApphWnd     As Long
#End If
Sub TestRoutine()
    Dim Results         As String
   
    GetHandle Sheet1.WebBrowserMap
   
    Results = Join(Array(Message, "WebBrowser: " & hWnd, "Workbook: " & WBhWnd, "XLMain: " & ApphWnd), vbLf)
   
    MsgBox Results
   
End Sub

Sub GetHandle(TargetObject As Object)
    On Error Resume Next

    Dim res As Long
    res = IUnknown_GetWindow(TargetObject, VarPtr(hwnd))
    ApphWnd = FindWindow("XLMAIN", vbNullString)
    If ApphWnd <> 0 Then WBhWnd = FindWindowEx(FindWindowEx(ApphWnd, 0&, "XLDESK", vbNullString), 0&, "EXCEL7", vbNullString)
End Sub

 

[Galius]

Hey mate,

This is what I get

 

[Galius]

Ok.

Well I had a look at that site you directed me too, and that is still limited to 32bit versions of Office and not 64bit, so I can't test it on my system. To that end, would you be able to run the following test subroutine - the idea is to check to make sure that we will be able to get access to the 'handle' of the web browser control. It also gets the handles of the workbook and the application, because I want to make sure that they are all returning different numbers. Basically, whichever handle is used in the snapshot routine determines the snapshot you're going to get, so I want to be able to isolate the webbrowser from the worksheet, etc, if that makes sense. I know that it looks like a lot, but I pulled this together before I saw your response re: using 32bit machines.

You will see that it identifies the WebBrowser as Sheet1.WebBrowserMap. I took this from your sample workbook. Please correct this if it has since changed. The following code should be inserted into the project workbook as a new standard module. Then run the TestSubroutine. It will show a messagebox - essentially, we're looking to make sure that there are no zeros. Let me know how it goes or if you have any questions.

It should look something like:
View attachment 68384

#If VBA7 Then
    #If Win64 Then
        Const Message   As String = "VBA7/Win64"
    #Else
        Const Message   As String = "VBA7"
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hWnd As LongPtr) As Long
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr

    Private hWnd        As LongPtr
    Private WBhWnd      As LongPtr
    Private ApphWnd     As LongPtr
#Else
    Const Message       As String = "Not VBA7"
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

    Private hWnd        As Long
    Private WBhWnd      As Long
    Private ApphWnd     As Long
#End If
Sub TestRoutine()
    Dim Results         As String
  
    GetHandle Sheet1.WebBrowserMap
  
    Results = Join(Array(Message, "WebBrowser: " & hWnd, "Workbook: " & WBhWnd, "XLMain: " & ApphWnd), vbLf)
  
    MsgBox Results
  
End Sub

Sub GetHandle(TargetObject As Object)
    On Error Resume Next

    Dim res As Long
    res = IUnknown_GetWindow(TargetObject, VarPtr(hwnd))
    ApphWnd = FindWindow("XLMAIN", vbNullString)
    If ApphWnd <> 0 Then WBhWnd = FindWindowEx(FindWindowEx(ApphWnd, 0&, "XLDESK", vbNullString), 0&, "EXCEL7", vbNullString)
End Sub

Hey Mate,

Any luck with this?

 

[Dan_W]

Hey Mate,

Any luck with this?

Apologies - I've been out-of-action for the past few days, but am back now. Will get this done this weekend.

 

[Dan_W]

Hey mate,

This is what I get
View attachment 68538

This is a good result, by the way. Out of curiosity, how are you referring to the webbrowser control? I've been using Sheet1.WebBrowserMap in the code thus far, but let me know if it should be something else.

 

[Galius]

This is a good result, by the way. Out of curiosity, how are you referring to the webbrowser control? I've been using Sheet1.WebBrowserMap in the code thus far, but let me know if it should be something else.

Hey Mate,

Yes that is what I've been using.

 

[Galius]

This is a good result, by the way. Out of curiosity, how are you referring to the webbrowser control? I've been using Sheet1.WebBrowserMap in the code thus far, but let me know if it should be something else.

Sorry to be a pain, but did you have any luck with this?

 

[Dan_W]

I'm sorry - I've been out of action for a while. I'll take a look at it now.

 

[Galius]

I'm sorry - I've been out of action for a while. I'll take a look at it now.

Thanks Dan! I've been trying to get it still and have had no success.