Solved

using ( blat ) blat.exe in vba

Posted on 2004-04-29
22
1,249 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now