from outlook, open excel, open workbook, run macro based on URL in body

[danmc58]

so what i need help with is in getting the URL from the outlook body to be the parameter to my excel macro. here is what i have so far.

Sub runExcelMacro()Dim ExApp As Excel.Application
Dim ExWbk As Workbook
Set ExApp = New Excel.Application
Set ExWbk = ExApp.workboooks.Open("W:\Viewpoint\Viewpoint Import\Programs\SheetsForNavigationAndImportingIntoViewpoint FROZEN.xlsm")
ExApp.Visible = True


ExWbk.Application.Run "Module1.startSaveModule"
'need to read URL from body (there will be a keyword in the subject line - "extraction". there will only be the URL in the body, nothing else, no signatures as well.
ExWbk.Close savechanges:=False


End Sub

thoughts

[gmayor]

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

[danmc58]

Thanks gmayor, i am implementing that now. nice piece of work. kudos. might have a question or two on implementation.

[danmc58]

so a question on this line:

ExApp.Run "Module1.startSaveModule", strURL

it does not run the macro and i don't know why. i verified that the workbook opened up (it did) and then nothing else happened. what am i doing wrong.

note: i got it to run successfully, so thanks for your code. just one more thing, how can i get the test() to run automatically, in other words, can it be memory resident and always scanning for this type of file?

[danmc58]

bump, can anyone help with this. just need to make it run automatically.

thanks

[gmayor]

To run it automatically, associate it with an Outlook rule that runs when the messages arrive.

[danmc58]

im pretty comfortable with rules in outlook, but don't know how to associate the macro with the email. i can create a rule that moves a message to a folder based on keywords in the subject line, but get lost figuring out how to associate it with a macro. does that make sense?

[gmayor]

In the rule creation dialog create a rule that applies to messages as they arrive and the action is Run a script. The script would be runExcelMacro
If there is no script option - see https://www.extendoffice.com/documen...t-missing.html

[danmc58]

is there a reference that i need? when i go to outlook and create a rule, the action dialog box does not include "Run a Script". see attachment.

rules wizard actions in outlook.jpg

[gmayor]

Did you miss the comment at the end of my previous post?

[danmc58]

yes i did miss that. sorry. so i went through the article and i don't have the Outlook key and of course no Security key. can you tell me the properties of both so i can create them? sorry if this is a pain.

[danmc58]

so i found slipstick had a reg key for it and i got the script to run. now outlook can see running a script. but when i do that, nothing comes up in the macro dialog box, i made sure that my subs are in a ThisOutlookSession and that they can run manually. what am i doing wrong?

[danmc58]

how can i mark the message as read?

note still have issue where rule script has nothing in the dialog box, how do i make the macro appear in that box?

found article on how to make macro appear, so now when a new message appears with the right info, then it runs but does not complete. will get back to you once i have this figured out.

[danmc58]

so got it to mark the item as read, run though the script, close excel. thanks for all your help.