Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Variable use in path names and Query

Status
Not open for further replies.
FaygoMan

FaygoMan

Programmer
Jun 15, 2009
4
US
Hello, I am working on a VBS script that will pull a list of email address from a database then email every one on the list with an attached file. I have a working script however I would like to reuse the main portion of it with out having to hard code each script.

'This section reads database and sends to every one in the table

Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\EmailList.mdb"
SQLQuery = "SELECT address FROM Email"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result("address")
Result.MoveNext
Loop
end if
OBJdbConnection.Close

'This sub sends the e-mail to all uses from the database.
Sub SendMail(address)
Dim objMessage, Rcpt

Rcpt = Chr(34) & "<" & address & ">"

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item(" = cdoSendUsingPort
.Item(" = "mail.domain.com"
.Item(" = cdoBasic
.Item(" = "domain\usename"
.Item(" = "mypassword"
.Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
Set .Configuration = objConf
.To = Rcpt
.From = "username@.com"
.Subject = "This is a CDO test message"
.TextBody = strBody
'use .HTMLBody to send HTML email.
.Addattachment "C:\2.pdf"
.Fields("urn:schemas:mailheader:disposition-notification-to") = "user@domain.com"
.DSNOptions = cdoDSNSuccessFailOrDelay
.Fields.update
.Send
End With


End Sub

This works fine, but I would like to replace the hard coded file attachemnt name and the table to run the query from.

I have tryed using this script to start that way I can change the path and table name.

Set oWSH = CreateObject("WScript.Shell")
oWSH.Environment("Volatile").Item("Tvar1") = "Tablename"
oWSH.Environment("Volatile").Item("Fvar1") = "Filename"
Set objShell = WScript.CreateObject("WScript.Shell")
Set objEnv = objShell.Environment("User")
objShell.Run "c:\database4.vbs"

The problem I am having is when I try and replace the table name for the query I am not getting the correct argument. When I try to use the var to change the file name it keeps tellign me file not found.

I just need some one to give me a little nudge in the correct direction.

Rob
The FaygoMan
 
Sort by date Sort by votes
See if this satisfies your need.

Code: 
'First grab the file we want to send
set oFO = CreateObject("SAFRCFileDlg.FileOpen")
' browse to file name thanks to safrcdlg.dll
' DLL available for download from:
' [URL unfurl="true"]http://www.thespidersparlor.com/publicdownloads/safrcdlg.zip[/URL]
oFO.OpenFileOpenDlg
' show what the user clicked on.
includeFile = oFO.FileName
TableName = UCase(InputBox("Enter table name...","Table To Query"))

Set OBJdbConnection = CreateObject("ADODB.Connection") 
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\EmailList.mdb"
SQLQuery = "SELECT address FROM " & TableName
Set Result = OBJdbConnection.Execute(SQLQuery) 
if Not Result.EOF then 
  Do While Not Result.EOF 
    SendMail Result("address", includeFile)
    Result.MoveNext 
  Loop 
end if 
OBJdbConnection.Close

'This sub sends the e-mail to all uses from the database.
Sub SendMail(address, includeFile) 
Dim objMessage, Rcpt 

Rcpt = Chr(34) & "<" & address & ">" 

Const cdoSendUsingPickup = 1 
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = cdoSendUsingPort
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "mail.domain.com"
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")[/URL] = cdoBasic
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername")[/URL] = "domain\usename"
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword")[/URL] = "mypassword"
  .Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
  Set .Configuration = objConf
  .To = Rcpt 
  .From = "username@.com" 
  .Subject = "This is a CDO test message"
  .TextBody = strBody
   'use .HTMLBody to send HTML email.
  .Addattachment includeFile
  .Fields("urn:schemas:mailheader:disposition-notification-to") = "user@domain.com"
  .DSNOptions = cdoDSNSuccessFailOrDelay
  .Fields.update
  .Send
End With


End Sub

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Tryed the script and it works great, however whant I need to do is have this running in the background with out user input. The idea was to have a small script with the var's kick off the second script that does the work of attaching and e-mailing. Thus I could just write an new startup scrit for every file I needed to do this with just change the table and file and save as a different name. This will be runnign on our back end system to save printing out a tun of reports.

Thanks
Faygo Man
 
Upvote 0 Downvote
OK, so then use arguments...
Code: 
TableName = WScript.Arguments(0)
IncludeFile = WScript.Arguments(1)

Set OBJdbConnection = CreateObject("ADODB.Connection") 
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\EmailList.mdb"
SQLQuery = "SELECT address FROM " & TableName
Set Result = OBJdbConnection.Execute(SQLQuery) 
if Not Result.EOF then 
  Do While Not Result.EOF 
    SendMail Result("address", includeFile)
    Result.MoveNext 
  Loop 
end if 
OBJdbConnection.Close

'This sub sends the e-mail to all uses from the database.
Sub SendMail(address, includeFile) 
Dim objMessage, Rcpt 

Rcpt = Chr(34) & "<" & address & ">" 

Const cdoSendUsingPickup = 1 
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = cdoSendUsingPort
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "mail.domain.com"
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")[/URL] = cdoBasic
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername")[/URL] = "domain\usename"
  .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword")[/URL] = "mypassword"
  .Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
  Set .Configuration = objConf
  .To = Rcpt 
  .From = "username@.com" 
  .Subject = "This is a CDO test message"
  .TextBody = strBody
   'use .HTMLBody to send HTML email.
  .Addattachment includeFile
  .Fields("urn:schemas:mailheader:disposition-notification-to") = "user@domain.com"
  .DSNOptions = cdoDSNSuccessFailOrDelay
  .Fields.update
  .Send
End With


End Sub

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Thanks , I only had to make a few small changes and it runs like a charm now.

I had to move the second Wscript.Argument to the sendmail sub because it was not excepting it as it was. See scrip below.


TableName = WScript.Arguments(0)

Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\EmailList.mdb"
SQLQuery = "SELECT address FROM " & TableName
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result ("address")
Result.MoveNext
Loop
end if
OBJdbConnection.Close

'This sub sends the e-mail to all uses from the database.
Sub SendMail(address)
IncludeFile = WScript.Arguments(1)
Dim objMessage, Rcpt

Rcpt = Chr(34) & "<" & address & ">"

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item(" = cdoSendUsingPort
.Item(" = "mail.server.com"
.Item(" = cdoBasic
.Item(" = "Domain\User"
.Item(" = "password"
.Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
Set .Configuration = objConf
.To = Rcpt
.From = "user@domain.com"
.Subject = "This is a CDO test message"
.TextBody = strBody
'use .HTMLBody to send HTML email.
.Addattachment includeFile
.Fields("urn:schemas:mailheader:disposition-notification-to") = "user@domain.com"
.DSNOptions = cdoDSNSuccessFailOrDelay
.Fields.update
.Send
End With
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bdwilcox
  • Locked
  • Question
Script seeing input variable value as blank but sees hard-coded value 4
Replies
6
Views
713
bdwilcox
bdwilcox
Palito1916
  • Locked
  • Question
Disable multiple computer accounts using TXT file with computer names
Replies
2
Views
293
Palito1916
Palito1916
stable-x
  • Locked
  • Question
How to set/define a variable from two variables?
Replies
3
Views
395
stable-x
stable-x
EdScripts
  • Locked
  • Question
Using a Variable in the Run Method - I would sure like to be able to do this !
Replies
0
Views
186
EdScripts
EdScripts
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
1K
SoftwareRT
SoftwareRT

Part and Inventory Search

Sponsor

Back
Top