VBScript SMTP自动电子邮件 [英] VBScript SMTP Auto Email

问题描述

我有一个脚本来自动发送一个存储在Excel中的地址列表，但它只发送到第一个地址，而不是循环到其他地址，我似乎无法解决它：

 设置objMessage = CreateObject（CDO.Message）
设置app = CreateObject（Excel.Application）
设置fso = CreateObject （Scripting.FileSystemObject）
 
对于每个f在fso.GetFolder（Y：\Billing_Common\autoemail）。文件
如果LCase（fso.GetExtensionName（f）） =xls然后
设置wb = app.Workbooks.Open（f.Path）
 
 set sh = wb.Sheets（Auto Email Script）
 row = 2 
 email = sh.Range（A& row）
 LastRow = sh.UsedRange.Rows.Count 
 
 Const ForReading = 1，ForWriting = 2，ForAppending = 8 
 Dim f 
设置f = fso.OpenTextFile（Y：\Billing_Common\autoemail\Script\Email.txt，ForReading）
 BodyText = f.ReadAll 
 
对于r =行到LastRow 
如果App.WorkSheetFunction.CountA（sh.Rows（r））<> 0然后
 objMessage.Subject =Billing：Meter Read
 objMessage.From =[e-mail]
 row = row + 1 
 objMessage.To = email 
 objMessage.TextBody = BodyText 
 
 objMessage.Configuration.Fields.Item _ 
（http://schemas.microsoft.com/cdo/configuration/sendusing）= 2 
 
 
'远程SMTP服务器的名称或IP 
 objMessage.Configuration.Fields.Item _ 
（http://schemas.microsoft.com/cdo/configuration / smtpserver）=SERVER ADDRESS HERE
 
'服务器端口
 objMessage.Configuration.Fields.Item _ 
（http://schemas.microsoft.com/cdo / configuration / smtpserverport）= 25 
 
 objMessage.Configuration.Fields.Update 
 objMessage.Send 
 
如果
结束下一个
 
 f.Close 
设置f = Nothing 
设置fso = Nothing 
 wb.Close 
如果
结束
  

任何帮助将是非常感谢的人！

谢谢！

解决方案例

  row = 2 
 email = sh.Range（A&行）
 ... 
对于r =行到LastRow 
 ... 
 objMessage.To =电子邮件
 ... 
下一个
  

您将电子邮件设置为单元格A2，永远不要更改。如果你想发送一封邮件给多个收件人，你应该使这个

  objMessage.To = sh.Range（A & r）.Value 
  

或（更好地）构建收件人列表（假设您的二手范围开始第一个表行中的标题）：

  ReDim收件人（LastRow  -  row）
对于r =行到LastRow 
收件人（r  -  row）= sh.Range（A& r）.Value 
下一个
 objMessage.To =加入（收件人，;）
  

，并发送消息一次。维珍诺·普拉萨德·卡鲁姆（Vishnu Prasad Kallummel）指出，这个MTA将处理其余的。

---

您的代码不会关闭它开始的Excel实例的注释。与VBScript中创建的其他对象不同，Office应用程序不会自动终止脚本，因此您必须自己处理：

  ... 
 wb.Close 
 app.Quit 
  

I have a script to auto email a list of address' stored in Excel, but it is only sending to the first address and not looping to the rest, I cannot seem to fix it:

Set objMessage = CreateObject("CDO.Message") 
Set app = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder("Y:\Billing_Common\autoemail").Files
  If LCase(fso.GetExtensionName(f)) = "xls" Then
    Set wb = app.Workbooks.Open(f.Path)

set sh = wb.Sheets("Auto Email Script")
row = 2
email = sh.Range("A" & row)
LastRow = sh.UsedRange.Rows.Count

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim f                                   
Set f = fso.OpenTextFile("Y:\Billing_Common\autoemail\Script\Email.txt", ForReading)                                        
BodyText = f.ReadAll

For r = row to LastRow
    If App.WorkSheetFunction.CountA(sh.Rows(r)) <> 0 Then 
    objMessage.Subject = "Billing: Meter Read" 
    objMessage.From = "[e-mail]"
    row = row + 1
    objMessage.To = email
    objMessage.TextBody = BodyText

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2


'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER ADDRESS HERE"

'Server port
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 

objMessage.Configuration.Fields.Update
objMessage.Send

    End if
Next

f.Close
Set f = Nothing
Set fso = Nothing
wb.Close
End If
Next

Any help would be much appreciated guys!

Thanks!

解决方案

row = 2
email = sh.Range("A" & row)
...
For r = row to LastRow
  ...
  objMessage.To = email
  ...
Next

You set email to the value of the cell "A2" and never change it. If you want to send a mail to multiple recipients, you should make that

objMessage.To = sh.Range("A" & r).Value

or (better) build a recipient list (assuming that your used range starts with headers in the first table row):

ReDim recipients(LastRow - row)
For r = row To LastRow
  recipients(r - row) = sh.Range("A" & r).Value
Next
objMessage.To = Join(recipients, ";")

and send the message just once. The MTA will handle the rest.

---

Side note: as Vishnu Prasad Kallummel pointed out in the comments your code doesn't close the Excel instance it started. Unlike other objects created in VBScript, Office applications won't automatically terminate with the script, so you have to handle it yourself:

...
wb.Close
app.Quit

这篇关于VBScript SMTP自动电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！