You can actually use the Jet OLEDB provider to query Excel spreadsheets. Then you don't need to rely on Excel automation (which I imagine requires Excel to be installed)
Also, this script doesn't put messages in the pickup directory. It connects to port 25 and uses SMTP to send the mail. Cheers Ken From: Sam Cayze [mailto:[email protected]] Sent: Saturday, 14 November 2009 2:06 AM To: NT System Admin Issues Subject: RE: blat to many Here is a script I wrote that reads all the email address in a XLS, and sends to each. Also has a testing mechanism. This one uses CDO, but it could easily be tweaked for Blat. Bulk Email Script.vbs '____________________________________________________________________________ 'A tool used to blast emails to a XLS list of email address 'Uses CDO to talk to Exchange; it inserts the messages into the PickUp directory for Queue. 'Sam Cayze 'Rollouts '____________________________________________________________________________ '**************************************************************************** 'START - USER EDITABLE VARIABLES 'Where is the XLS located? Note, have the emails in Column A. Full Path, Quotes Needed. excelPath = "C:\PATH\File.xls" 'Which row does the data start / is there a header? If no header, 1; if yes, 2. intRow = 2 'From Address. Example: "Joe User <[email protected]<mailto:[email protected]>>" FromAddress="Joe User <[email protected]<mailto:[email protected]>>" 'Testing Address TestToAddress="[email protected]<mailto:[email protected]>" 'Subject Line Subject="Thank you for your response" 'Location of the email template, in HTML format. Use http://tinymce.moxiecode.com/examples/full.php# to create the HTML code. 'Be sure to use the format "file://C:\Path\FileName.html<file:///C:\Path\FileName.html>" HTMLPath="file://C:\Path\Email.html<file:///C:\Path\Email.html>" 'END - USER EDITABLE VARIABLES '**************************************************************************** DIM cell Dim currentWorkSheet Dim usedColumnsCount Dim usedRowsCount Dim row Dim column Dim top Dim left Dim Cells Dim curCol Dim curRow Dim countSend Set objExcel = CreateObject("Excel.Application") Set objExcel = CreateObject("Excel.Application") objExcel.DisplayAlerts = 0 objExcel.Workbooks.open excelPath, false, true Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1) usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count usedRowsCount = currentWorkSheet.UsedRange.Rows.Count top = currentWorksheet.UsedRange.Row left = currentWorksheet.UsedRange.Column 'Loop through each row, and read the first 3 lines. Cell = objExcel.Cells(1, 1).Value Cell2 = objExcel.Cells(2, 1).Value Cell3 = objExcel.Cells(3, 1).Value Ret=Msgbox("The spreadsheet @ " & vbCRLF & excelPath & vbCRLF & "Contains " & usedRowsCount & " rows. (Including header, if any.) " & vbCRLF & vbCRLF _ & "First 3 Cells:" & vbCRLF & Cell & vbCRLF & Cell2 & vbCRLF & Cell3 & vbCRLF & vbCRLF & "Do you wish to send?",VBYesNo,"Bulk Emailer") If Ret=6 then countSend = 0 'Loop through each row, and send the email. Do Until objExcel.Cells(intRow,1).Value = "" Cell = objExcel.Cells(intRow, 1).Value intRow = intRow + 1 Set myMail=CreateObject("CDO.Message") myMail.Subject=Subject myMail.From=FromAddress myMail.To=Cell myMail.CreateMHTMLBody HTMLPath myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing";)=2<http://schemas.microsoft.com/cdo/configuration/sendusing> myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver";)="10.10.0.20<http://schemas.microsoft.com/cdo/configuration/smtpserver>" myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport";)=25<http://schemas.microsoft.com/cdo/configuration/smtpserverport> myMail.Configuration.Fields.Update myMail.Send countSend = countSend + 1 Loop MsgBox countSend & " Email(s) Sent", vbInformation + vbOKOnly, "Bulk Emailer" Else Ret=Msgbox("Do you wish to send a test email to " & TestToAddress & "?",VBYesNo,"Bulk Emailer") If Ret=6 then countSend = 0 Set myMail=CreateObject("CDO.Message") myMail.Subject=Subject myMail.From=FromAddress myMail.To=TestToAddress myMail.CreateMHTMLBody HTMLPath myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing";)=2<http://schemas.microsoft.com/cdo/configuration/sendusing> myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver";)="10.10.0.20<http://schemas.microsoft.com/cdo/configuration/smtpserver>" myMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport";)=25<http://schemas.microsoft.com/cdo/configuration/smtpserverport> myMail.Configuration.Fields.Update myMail.Send countSend = countSend + 1 MsgBox countSend & " Email(s) Sent", vbInformation + vbOKOnly, "Bulk Emailer" end if end if objExcel.Quit set myMail=nothing ________________________________ From: David W. McSpadden [mailto:[email protected]] Sent: Friday, November 13, 2009 8:50 AM To: NT System Admin Issues Subject: blat to many How can I take a list of emails and use blat to send to each of them? Is there a batch file anyone has laying around? ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
