Start application from VBA shell

Question

In excel I'm trying to insert a button to parse the contents of selected cells to a new email. Since I use Mozilla Thunderbird, I have to parse through Shell. My code so far:

    Dim dr As String
    If dir("C:\Program Files (x64)", vbDirectory) = "" Then
        dr = "C:\Program Files (x86)"
    Else
        dr = "C:\Program Files"
    End If
    dr = dr & "\Mozilla Thunderbird\thunderbird.exe"

    Dim args As String
    args = "to='" & MailTo & "',cc='" & MailCC & "',bcc='" & MailBCC & "'"
    args = args & ",subject='" & MailSubject & "',body='" & strbody & "'"
    If MailBijlage <> "" Then args = args & ",attachment='" & TempFilePath & TempFileName & ".pdf"

    Dim Command As String

    Command = "cmd.exe /S /K " & """" & dr & """" & " -compose " & """" & args & """"

    conf = MsgBox(Command, vbOKCancel, "Confirm command")

    If conf = vbOK Then
        Shell (Command)
        MsgBox "Email sent.", , "Info"
    ElseIf conf = vbCancel Then
        MsgBox "Canceled.", , "Info"
    End If

Creating the Command is handled well, but Shell won't start Thunderbird. It just says:

'C:\Program' is not recognized as an internal or external command, operatable program or batch file.

I don't know what's wrong, the message box showing the code clearly shows the double quotes around the path.

Thanks in advance.

==EDIT==

Trying to get it working using a batch file now (as suggested by S O)... I opened a bat file (which is created) and let VBA output commands to the bat-file. When I get to the point of the msgbox asking to send the mail, when I open the bat-file in notepad, it's empty. VBA calls the empty batch file and nothing happens (mail was not sent). When I reopen the file after the macro has ended, all output is in the batch file and when I run it, a mail is composed as it should.

        Open Environ("userprofile") & "\desktop\tbmail.bat" For Output As #1
    If dir("C:\Program Files (x64)", vbDirectory) = "" Then
        Print #1, "cd ""%programfiles(x86)%"""
    Else
        Print #1, "cd ""%programfiles%"""
    End If
    Print #1, "cd ""Mozilla Thunderbird"""

        Print #1, "thunderbird.exe -compose """ & args & """"
    Close #1

    If conf = vbOK Then
        Call Shell(Environ("userprofile") & "\desktop\tbmail.bat")
        MsgBox "De mail is verstuurd.", , "Informatie"
    ElseIf conf = vbCancel Then
        MsgBox "Versturen geannuleerd.", , "Informatie"
    End If

What's wrong?

Could you provide the data stored in the variable named "Command"? Also when you do Shell you don't need the CMD.EXE or do you? See:http://stackoverflow.com/questions/2290365/how-can-i-execute-a-shell-command-using-vba — NoChance, Jan 24 '15 at 11:51
I did. The variable dr is created first and gives the absolute path to the Thunderbird application (thunderbird.exe) and args contains all arguments which need to be added to compose a mail from command line. Not calling cmd.exe in Shell(), would it make any difference? — Cordin90, Jan 24 '15 at 20:09

score 0 · Answer 1 · answered Jan 24 '15 at 12:29

Try changing cmd.exe to just CMD

If it still isn't working you could try try:

CreateObject("WScript.Shell").Run Command, 1, False

Change 1 to 0 if you want to hide the console, Change False to True if you want the wait for user return in console.

Or you could so something like:

Open Environ("USERPROFILE") & "\desktop\test.bat" For Output As #1
Print #1, Command
Close #1
Shell Environ("USERPROFILE") & "\desktop\test.bat"
Kill Environ("USERPROFILE") & "\desktop\test.bat"

which is writing the command to a batch file and using the Shell command to call the batch file.

I generally prefer to use the WScript.Shell object because it allows for things like reading the StdOut to which is very useful.

cmd.exe or CMD doesn't matter, still the same error. The CreateObject also gives the same error. It's as if the code passed to cmd ignores double quotes. — Cordin90, Jan 24 '15 at 20:14

Start application from VBA shell

1 Answers1