• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1329
  • Last Modified:

using ( blat ) blat.exe in vba

I am trying to use blat in vba using the shell command.  Please look at the code I have so far.  

1. My first problem was being able to use a subject that had multiple words with spaces.  then i figured it out by putting double quotes around the subject line.

2. My next issue is the amount of characters allowed in the subject line.  Is it just me or does everyone else only get 14 characters?  How can I get more?  I do not have this issue while executing blat directly from a command prompt!

3. My final issue and most important is trying to list multiple -to email addresses.  I cant use the comma to seperate email addys without vba freaking out.  any solutions?

Thanks.

Private Sub Command3_Click()
Dim varMsgBody As String

varMsgBody = "You have received this message because this device failed miserably."
   
    Open "C:\dbms\BlatEmail.txt" For Output As #1
    Write #1, varMsgBody
    Close #1

Shell "command.com /c c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt " _
& "-s ""emergency test"" -t vsalazar@kotura.com -i LayoutDBMS@kotura.com", vbHide
   
End Sub
0
lightcross
Asked:
lightcross
  • 9
  • 9
  • 2
1 Solution
 
flavoCommented:
I think there may be a limit on the number of chars you can have in the Shel command.. I had the same problem (but i think i had more than 14 chars)..

Dave!
0
 
lightcrossAuthor Commented:
this would suck big time.  any solution to multiple email recipients or proof that shell has limited characters?  thanks!
0
 
nico5038Commented:
Did you try to use:

Shell "command.com /c c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt " _
& "-s ""emergency test"" -t vsalazar@kotura.com -cc ""LayoutDBMS@kotura.com,test@xyz.com""", vbHide

Another option is ofcourse to put all into one .bat file and just execute that from the shell command.

nic;o)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
lightcrossAuthor Commented:
I like the bat file idea.  would it look something like this?
varBlatBatFile = "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com"
   
    Open "C:\dbms\BlatBatFile.bat" For Output As #1
    Write #1, varBlatBatFile
    Close #1

Shell "command.com /c c:\dbms\blatbatFile.bat"

but then i dont know how to make the bat file without quotes using this method
   
0
 
nico5038Commented:
For getting quotes in the batfile lines you can also use the "double" "double-quote" like:

varBlatBatFile = "c:\winnt\system32\blat.exe ""c:\dbms\BlatEmail.txt"" -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com"

Will give after the Write a line with:
c:\winnt\system32\blat.exe "c:\dbms\BlatEmail.txt" -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com

You can even write this "straight away" like:
    Write #1, "c:\winnt\system32\blat.exe ""c:\dbms\BlatEmail.txt"" -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com"

And having multiple rows in a table would allow a processing loop like:

dim rs as dao.recordset

set rs = currentdb.openrecordset ("select emailaddress from tblAddress")
rs.movefirst
while not rs.eof
    Write #1, "c:\winnt\system32\blat.exe ""c:\dbms\BlatEmail.txt"" -s emergency test -t " & rs!emailaddress & " -i LayoutDBMS@kotura.com"
    rs.movenext
wend

Getting the idea ?

Nic;o)
0
 
lightcrossAuthor Commented:
the problem is that write creates quotes around the whole line in the bat file.  so it will look like this:
"c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com"

I dont want quotes surrounding the line.  I believe that wont work in a bat file.  it needs to be like this:
c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt -s emergency test -t vsalazar@kotura.com -i LayoutDBMS@kotura.com
0
 
nico5038Commented:
Oops, me bad use:

    Print #1, "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt" -s emergency test -t " & rs!emailaddress & " -i

To get no surrounding quotes.

Nic;o)
0
 
lightcrossAuthor Commented:
this print will put the line in a file i specify on the hard-drive?
0
 
nico5038Commented:
Yes, in the Opne/Close construction like:

open "c:\test.bat" for output as #1
    Print #1, "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt" -s emergency test -t address1@email.com"
    Print #1, "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt" -s emergency test -t address2@email.com"
    Print #1, "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt" -s emergency test -t address3@email.com"
close #1

Just copy/paste this in e.g. a function and see the effect.

Nic;o)
0
 
lightcrossAuthor Commented:
This is what I have now.  It doesnt seem to work when the shell calls the bat file.  Is it coded correctly?  Otherwise if i manually run the bat file it will work.


Private Sub Command3_Click()

Dim varMsgBody As String
Dim varBlatBat As String

varMsgBody = "This message is a test.  This message is a test. This message is a test.  This message is a test."
varBlatBat = "c:\winnt\system32\blat.exe c:\dbms\BlatEmail.txt -S ""testing blat testing blat testing blat testing blat"" -t ""vsalazar@kotura.com, zmills@kotura.com"""
   
    Open "C:\dbms\BlatEmail.txt" For Output As #1
    Print #1, varMsgBody
    Close #1
   
    Open "C:\dbms\BlatEmail.bat" For Output As #1
    Print #1, varBlatBat
    Close #1

Shell "command.com /c C:\dbms\BlatEmail.bat, vbHide"
0
 
nico5038Commented:
Try:
Shell ("command.com /c C:\dbms\BlatEmail.bat", vbHide)

Nic;o)
0
 
lightcrossAuthor Commented:
nope..  i tried both
Shell ("command.com /c C:\dbms\BlatEmail.bat", vbHide)            ' turns red in vb
'and
Shell ("command.com /c C:\dbms\BlatEmail.bat, vbHide")               'just doesnt work
0
 
nico5038Commented:
Try:
Shell ("C:\dbms\BlatEmail.bat", vbHide)

Nic;o)
0
 
lightcrossAuthor Commented:
nope...its red.

???  
0
 
nico5038Commented:
Hmm looks to be the vbHide not liking the ( ), try:

Shell "C:\dbms\BlatEmail.bat", vbHide

Nic;o)
0
 
lightcrossAuthor Commented:
Yep.  that was the problem.  I tried so many combinations.  missed the correct one, as usual :)

Thanks Nico
0
 
nico5038Commented:
I know the problem <LOL>

Nic;o)
0
 
flavoCommented:
or myVar = Shell("C:\dbms\BlatEmail.bat", vbHide)

if you use ( ) around function VB is expecting to return a value to something.

$0.02. Was  at the pub last night, missed all the action.

Dave
0
 
lightcrossAuthor Commented:
Yes, this is correct.  I thought I already accepted this answer.  Sorry.  thanks again nico!
0
 
nico5038Commented:
No problem lightcross, glad I could help :-)

Nic;o)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now