vba 如何逐行读取 Excel 文件并将电子邮件发送到工作表上的名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15368774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I read Excel file row by row and send email to the names on the worksheet?
提问by Kenny
We have an Excel file called empList.xlsxwith a list of users whose servers are very close to running out of space.
我们有一个名为empList.xlsx的 Excel 文件，其中包含服务器非常接近空间不足的用户列表。
The email is intended to advise users of the current space status and to delete some files to free up some space.
该电子邮件旨在告知用户当前空间状态并删除一些文件以释放一些空间。
The VB script below only reads employees' email addresses and sends them email notifications but doesn't read the rest of the data on a particular row.
下面的 VB 脚本仅读取员工的电子邮件地址并向他们发送电子邮件通知，但不读取特定行上的其余数据。
Set objExcel = CreateObject("Excel.Application")
Function getEmail()
Dim iCol As Integer, iRow As Integer
Dim sEmailBody As String
Dim sEmailTo as string ' the recipient
iCol = 1 ' column A
iRow = 2 ' row 2
Do
sEmailTo = cells(irow, 1).text
sEmailBody = sendData(iRow)
iRow = iRow + 1
Loop While Not Len(Trim(Cells(iRow, iCol))) = 0
End Function
Function sendData(ByVal iRow As Integer) As String
Dim iCol As Integer
For iCol = 2 To 11 ' B=2, K=11
sendData = sendData & Cells(iRow, iCol).Text & vbCrLf
Next
MsgBox sendData
End Function
Set objExcel = CreateObject("Excel.Application")
Set objEmail = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Logs\EmpList.xlsx")
Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtprey.domain.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
.Update
End With
Set objEmail.Configuration = objConf
x = 2
Do Until objExcel.Cells(x, 2).Value = ""
Set objEmail.Configuration = objConf
objEmail.From = "[email protected]"
objEmail.To = objExcel.Cells(x, 2)
objEmail.Subject = "Your H Drive is Full"
objEmail.Textbody = sendData(2)
objEmail.Send
x = x + 1
Loop
objExcel.Quit
Below is an example of how each row is laid out:
下面是如何布局每一行的示例：
[email protected]|H:\home\matt.tavakolian\|60.3 GB|54629.5 GB|2274|288|0.0 GB| 6.7%|3/4/2013 3:11 PM|3/11/2013 12:16 PM|9/23/2008 3:26 PM
Since I don't know of a way to add an attachment, I have used pipes (|) to indicate data on each cell per row.
由于我不知道添加附件的方法，因此我使用管道 (|) 来指示每行每个单元格上的数据。
The example above example represents row #2 from CellA to CellK.
上面的示例表示从 CellA 到 CellK 的第 2 行。
What we would like to do is send an email to say, [email protected] on row #2 and include all data on that row (from CellA to CellK) to show the current server hard drive space.
我们想要做的是发送电子邮件至第 2 行的 [email protected] 并包含该行的所有数据（从 CellA 到 CellK）以显示当前服务器硬盘空间。
Is this possible?
这可能吗？
回答by Our Man in Bananas
you could try something like this (using the Excel object and workbook object obviously):
你可以尝试这样的事情（显然使用 Excel 对象和工作簿对象）：
Function getEmail()
Dim iCol As Integer, iRow As Integer
Dim sEmailBody As String
Dim sEmailTo as string ' the recipient
iCol = 1 ' column A
iRow = 2 ' row 2
Do
sEmailTo=cells(irow,1).text
sEmailBody = sendData(iRow)
iRow = iRow + 1
Loop While Not Len(Trim(Cells(iRow, iCol))) = 0
End Function
in that function you call the below function to get the email body!
在该函数中，您调用以下函数来获取电子邮件正文！
Function sendData(ByVal iRow As Integer) As String
Dim iCol As Integer
For iCol = 2 To 11 ' B=2, K=11
sendData = sendData & Cells(iRow, iCol).Text & vbCrLf
Next
MsgBox sendData
End Function
so just call the function 'getEmaily' to get the email address and the body that goes into your email body!
因此，只需调用函数“getEmaily”即可获取电子邮件地址和进入电子邮件正文的正文！
HTH
HTH
Philip
菲利普
