Solved

using ( blat ) blat.exe in vba

Posted on 2004-04-29
22
1,271 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

829 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