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/documents/outlook/4640-outlook-rule-run-a-script-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.

23689

[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.