Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

using ( blat ) blat.exe in vba

Posted on 2004-04-29
22
Medium Priority
?
1,320 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
20 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

773 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