Need function to run Outlook macro from Excel

[newcoder54]

Need a function for Excel XP that will run a Outlook XP macro that I specify. Thanks - Larry

 

[xlbo]

This isn't a helpdesk site. Rather than just asking for a working answer, you are expected to have done some research / tried some ideas out 1st. Your question would indicate that you have not. If you have done any research / workings, please share them as it usually provides useful info as to how you are trying to make your project work

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[newcoder54]

Yes - you're right. Here is where I am. I am stumped at the line "OutlookApp.Run". I have similar code in Access to run some print jobs in Word and that works fine but think this must be quite different. Pretty much new material for me.

Function RunOutlookMacro()

'This sub should run an Outlook macro from Excel
Dim OutlookApp As Object
Set OutlookApp = CreateObject("Outlook.Application")
'Run the macro "Send_STP"
'error message caused by next line - "Object doesn't support thie property or method"
OutlookApp.Run "Send_STP"
Set OutlookApp = Nothing

End Function

 

[xlbo]

ok - I may be wrong here but I cannot see any way of doing that. What you may need to do is get the code from the macro and transfer it into excel, then reset all the object references by prefixing them with "OutlookApp"

So, if your macro originally looked like this in Outlook

Sub SaveAndRemoveAttachments()
Dim sSubject As String, sDate As Date, sName As String

Set myItem = ActiveInspector.CurrentItem
Set myAttachments = myItem.Attachments

sName = myAttachments.Item(1).DisplayName
'sName = Left(myattachments.Item(1).DisplayName, Len(myattachments.Item(1).DisplayName) - 4)
sSubject = myItem.Subject
sDate = myItem.CreationTime

sName = CleanString(sName)
sSubject = CleanString(sSubject)

myAttachments.Item(1).SaveAsFile "C:\Home\" & sSubject & " " & Format(sDate, "ddmmyyyy") & " " & sName & ".xls"

myAttachments.Remove 1
end sub

you would need to change it to:

Sub SaveAndRemoveAttachments()
Dim sSubject As String, sDate As Date, sName As String
dim OutlookApp as object

set OutlookApp = createobject("Outlook.Application")
[b]with OutlookApp[/b]
Set myItem = [b].[/b]ActiveInspector.CurrentItem
Set myAttachments = myItem.Attachments

sName = myAttachments.Item(1).DisplayName
sSubject = myItem.Subject
sDate = myItem.CreationTime

sName = CleanString(sName)
sSubject = CleanString(sSubject)

myAttachments.Item(1).SaveAsFile "C:\Home\" & sSubject & " " & Format(sDate, "ddmmyyyy") & " " & sName & ".xls"

myAttachments.Remove 1
[b]end with[/b]
set OutlookApp = nothing
end sub

hope this makes sense

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question