We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Outlook VBA problems

Medium Priority
684 Views
Last Modified: 2013-11-10
Hi all,

I thought we had this one taken care of and looks like it isnt... I have done about all the homework I can think of on this problem and explain below in vivid detail whats going on.

Created a rule looking for the word "sendme" in the subject line. if found processs the following:

   Mark as Read (this works)
   Execute VBA Script which has a Shell ("") call in it (does NOT work).

As stated above, the email is marked as READ, but the next step of the rule seems to in part fail. After the message is marked read I execute the following VBA:

Sub olRule_sendme(olkMessage As Outlook.MailItem)

Dim params() As String
Dim addy As String
    params = Split(Replace(olkMessage.Subject, "  ", " "), " ")
    addy = GetSMTPAddress(olkMessage.SenderEmailAddress)
    Shell ("D:\newmonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy)
    olkMessage.Subject = "DELETE FOR GOOD"
    olkMessage.Save
    olkMessage.Delete
    Set olkMessage = Nothing
    Set olkMessage = Session.GetDefaultFolder(olFolderDeletedItems).Items.Find("[Subject] = 'DELETE FOR GOOD'")
    If TypeName(olkMessage) <> "Nothing" Then
        olkMessage.Delete
    End If

End Sub

This VBA should do the following:

1. Take the words of the subject and pass them along to the shell call
2. flag the message to be deleted
3. delete perm. the email

What appears to be happening is, the VBA IS called, I changed the code to try and see where it fails by adding msgbox lines:

Sub olRule_sendme(olkMessage As Outlook.MailItem)

Dim params() As String
Dim addy As String
    params = Split(Replace(olkMessage.Subject, "  ", " "), " ")
    addy = GetSMTPAddress(olkMessage.SenderEmailAddress)
    MsgBox ("Params " & params(1) & " " & params(2) & " " & params(3) & " " & addy)
    Shell ("D:\newmonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy)
    MsgBox ("Params " & params(1) & " " & params(2) & " " & params(3) & " " & addy)
    olkMessage.Subject = "DELETE FOR GOOD"
    olkMessage.Save
    olkMessage.Delete
    Set olkMessage = Nothing
    Set olkMessage = Session.GetDefaultFolder(olFolderDeletedItems).Items.Find("[Subject] = 'DELETE FOR GOOD'")
    If TypeName(olkMessage) <> "Nothing" Then
        olkMessage.Delete
    End If

End Sub

What does happen is, the 1st message box comes up and displays my params() promerly the first time. Then it dies. no error message, no shell call at all and no second message box to display the params() again. so its halting on the SHELL line.

Now on the batch file side, I have made the batch file to do nothing but echo the date time and the parameters passed to a text file. If I call the batch file manually via the command line in dos, I get entries into my text file. I have re-verified the path to this file as being correct. I also changed the SHELL line to call just notepad.exe and it WORKS. But it refuses to call my batch file. I am also using outlook as a local admin account.

So now I pass this on to ??? :)

Thanks!!
Comment
Watch Question

Author

Commented:
Before I forget, I know of the outlook security issue, I installed Outlook advanced security by Mapilabs inc and was prompted for an action to allow or disallow the script to run, I selected allow and checked ALWAYS ALLOW.

Thanks :)
CERTIFIED EXPERT
Top Expert 2011

Commented:
Haven't set up a test but try:

Shell ("D:\newmonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")

Chris

Author

Commented:
Didnt work :(
Same thing, 1st msg box popped, then died. second didnt pop and no entries in the text file were made.

The msgboxs will be removed of course after testing.
CERTIFIED EXPERT
Top Expert 2011

Commented:
I may be missing something in your setup or my understanding but:

Chris
Dim shell As Object
 
Set shell = CreateObject("wscript.shell")
shell.Run ("D:\newmonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
)

Open in new window

Author

Commented:
Well, sort of, the params are parsed from the subject line... I can try and incorperate your code above into the script?

Author

Commented:
Here is a more simpler version of the script, it just parses the subject line and passes the parameters.

Sub olRule_sendme(olkMessage As Outlook.MailItem)
Dim params() As String
Dim addy As String
    params = Split(Replace(olkMessage.Subject, "  ", " "), " ")
    addy = GetSMTPAddress(olkMessage.SenderEmailAddress)
    shell ("D:\NewMonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy)
    
End Sub

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
APologies I assumed you would be happy merging it so try this:

Chris
Sub olRule_sendme(olkMessage As Outlook.MailItem)
 
Dim params() As String
Dim addy As String
Dim shell As Object
    params = Split(Replace(olkMessage.Subject, "  ", " "), " ")
    addy = GetSMTPAddress(olkMessage.SenderEmailAddress)
Set shell = CreateObject("wscript.shell")
shell.Run ("D:\newmonthly\reports\endofcruise.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
    olkMessage.Subject = "DELETE FOR GOOD"
    olkMessage.Save
    olkMessage.Delete
    Set olkMessage = Nothing
    Set olkMessage = Session.GetDefaultFolder(olFolderDeletedItems).Items.Find("[Subject] = 'DELETE FOR GOOD'")
    If TypeName(olkMessage) <> "Nothing" Then
        olkMessage.Delete
    End If
 
End Sub

Open in new window

Author

Commented:
Didnt work, its running but dies at the shell command.  I modified the code to open a MsgBox to test it instead of the shell call, the message box popped up and showed my parameters, even changed the batch file name to eoc.bat just incase for whatever reason it didnt like the length of the path and filename which shouldnt be an issue, when the message box completed it then finished the script, deleting the email.  With the shell call it just dies, doesnt call the batch file and doesnt delete the message.

Here is the code & screen shot showing what I am talking about, I censored the email address to protect the innocent =)
Sub olRule_sendme(olkMessage As Outlook.MailItem)
 
Dim params() As String
Dim addy As String
Dim shell As Object
    params = Split(Replace(olkMessage.Subject, "  ", " "), " ")
    addy = GetSMTPAddress(olkMessage.SenderEmailAddress)
Set shell = CreateObject("wscript.shell")
'shell.Run ("D:\newmonthly\reports\eoc.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
MsgBox ("D:\newmonthly\reports\eoc.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
    olkMessage.Subject = "DELETE FOR GOOD"
    olkMessage.Save
    olkMessage.Delete
    Set olkMessage = Nothing
    Set olkMessage = Session.GetDefaultFolder(olFolderDeletedItems).Items.Find("[Subject] = 'DELETE FOR GOOD'")
    If TypeName(olkMessage) <> "Nothing" Then
        olkMessage.Delete
    End If
 
End Sub

Open in new window

eoc.bmp
CERTIFIED EXPERT
Top Expert 2011

Commented:
I appreciate the batch file is simple but can you supply it so I can test with htat one since my tests on the other computer were fine.

Chris

Author

Commented:
Yes it really was this simple... Just a test batch file that works from command line but not the script execution side...

This issue really does die at the SHELL command within the VBA... When I examine the _eoc.txt file there is no enties on it showing it never executed except manually from command line.

Thanks!


@ECHO OFF
ECHO TEST...
ECHO %DATE% %TIME% %0 %1 %2 %3 %4 >> D:\newmonthly\reports\_eoc.txt

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
Just run it as below, (different path) and the output file is created an populated as per the msgbox.

I don't think I changed anything other than the path data.

Chris


Sub olRule_sendme(olkMessage As Outlook.mailitem)
 
Dim params() As String
Dim addy As String
Dim shell As Object
    params = Split(Replace(olkMessage.subject, "  ", " "), " ")
    addy = Sharath.GetSMTPAddress(olkMessage.SenderEmailAddress)
Set shell = CreateObject("wscript.shell")
shell.Run ("D:\newmonthly\reports\eoc.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
'MsgBox ("D:\newmonthly\reports\eoc.bat " & params(1) & " " & params(2) & " " & params(3) & " " & addy & "")
    olkMessage.subject = "DELETE FOR GOOD"
    olkMessage.Save
    olkMessage.Delete
    Set olkMessage = Nothing
    Set olkMessage = Session.GetDefaultFolder(olFolderDeletedItems).items.Find("[Subject] = 'DELETE FOR GOOD'")
    If TypeName(olkMessage) <> "Nothing" Then
        olkMessage.Delete
    End If
 
End Sub

Open in new window

Author

Commented:
Hi there,

I tried it as requested and still it failed.  It marked the message as read and stopped at the shell execution.  It failed to delete the message, telling me it stopped at the shell execution.

Thanks!
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
OK, before I accept that last post by you (as it WORKED), I already did have the GET SMTP thing copied in, but when I removed all code and pasted ONLY that from above, it WORKED.

What is the problem is it location specific?

THANK YOU!
CERTIFIED EXPERT
Top Expert 2011

Commented:
Can't say as such since I can't see the old script BUT for example if your old copy used early binding it might have failed but it ought to have done so whatever you used i.e. the msgbox output as well as shell output.

Distasteful as it sounds all I can suggest is that the different sub declaration perhaps returned something slightly dodgy that msgbox could handle but that shell could not ... or the wind has changed direction!

Chris

Author

Commented:
Thank you so much for your help!
CERTIFIED EXPERT
Top Expert 2011

Commented:
Glad it helped, though as I said it's a bit distasteful that we couldn't narrow in on the precise problem

Chris
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.