Solved

using ( blat ) blat.exe in vba

Posted on 2004-04-29
22
1,275 Views
Last Modified: 2012-05-04
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
Comment
Question by:lightcross
  • 9
  • 9
  • 2
22 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 10954899
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
 
LVL 2

Author Comment

by:lightcross
ID: 10954959
this would suck big time.  any solution to multiple email recipients or proof that shell has limited characters?  thanks!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10958123
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:lightcross
ID: 10960555
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10960741
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
 
LVL 2

Author Comment

by:lightcross
ID: 10961757
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10961896
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
 
LVL 2

Author Comment

by:lightcross
ID: 10961980
this print will put the line in a file i specify on the hard-drive?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10962029
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
 
LVL 2

Author Comment

by:lightcross
ID: 10962661
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 125 total points
ID: 10962795
Try:
Shell ("command.com /c C:\dbms\BlatEmail.bat", vbHide)

Nic;o)
0
 
LVL 2

Author Comment

by:lightcross
ID: 10962894
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10963044
Try:
Shell ("C:\dbms\BlatEmail.bat", vbHide)

Nic;o)
0
 
LVL 2

Author Comment

by:lightcross
ID: 10963089
nope...its red.

???  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10963142
Hmm looks to be the vbHide not liking the ( ), try:

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

Nic;o)
0
 
LVL 2

Author Comment

by:lightcross
ID: 10963771
Yep.  that was the problem.  I tried so many combinations.  missed the correct one, as usual :)

Thanks Nico
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10963924
I know the problem <LOL>

Nic;o)
0
 
LVL 34

Expert Comment

by:flavo
ID: 10965440
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
 
LVL 2

Author Comment

by:lightcross
ID: 12009285
Yes, this is correct.  I thought I already accepted this answer.  Sorry.  thanks again nico!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12012001
No problem lightcross, glad I could help :-)

Nic;o)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question