If the only thing in the message body is the text you want to recover
and
Your macro in Excel includes a callback e.g. startSaveModule(strURL as String)
Then the following should work.
I have not included code to test whether the workbook is already open, nor to close it after completion.
Option Explicit
Sub Test()
Dim olMsg As MailItem
On Error Resume Next
Set olMsg = ActiveExplorer.Selection.Item(1)
runExcelMacro olMsg
lbl_Exit:
Exit Sub
End Sub
Sub runExcelMacro(olItem As Object)
Dim ExApp As Object
Dim ExWbk As Object
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim oPara As Object
Dim strURL As String
Const strWB As String = "W:\Viewpoint\Viewpoint Import\Programs\SheetsForNavigationAndImportingIntoViewpoint FROZEN.xlsm"
If Not TypeName(olItem) = "MailItem" Then GoTo lbl_Exit
If InStr(1, olItem.Subject, "extraction") = 0 Then GoTo lbl_Exit
With olItem
.BodyFormat = olFormatHTML
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
For Each oPara In oRng.Paragraphs
If Len(oPara) > 1 Then
Set oRng = oPara.Range
oRng.End = oRng.End - 1
strURL = Trim(oRng.Text)
Exit For
End If
Next oPara
Debug.Print strURL
End With
On Error Resume Next
Set ExApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set ExApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set ExWbk = ExApp.workbooks.Open(strWB)
ExApp.Visible = True
ExApp.Run "Module1.startSaveModule", strURL
lbl_Exit:
Set ExApp = Nothing
Set ExWbk = Nothing
Exit Sub
End Sub