|
黄雨森 发表于 2014-6-4 13:17
我自己倒腾了一下,还是不行,能帮我再修改一下吗?谢谢!附件中添加了多个收件人还有主题和正文示例,多 ...
请测试:
Sub ExcelSplit2()
'---------------Define BILL Split
Dim cnn As Object, sql$
Dim arr, brr, m
Dim wb As Workbook
'---------------Define Outlook
Dim wbStr As String, nlist As String
Dim OutlookApp
Dim newMail
Set OutlookApp = CreateObject("Outlook.Application")
Dim dic, n, k, j
Dim c As Long, subj
Set dic = CreateObject("Scripting.Dictionary")
'----------------run split
Application.ScreenUpdating = False
With Sheet1
arr = .Range("A1", "E2") 'ARRAY for Title
Set cnn = CreateObject("Adodb.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" _
& "Data Source=" & ThisWorkbook.FullName
sql = "select distinct 班级 from [总表$A2:E] where 班级 is not NULL"
'--check File number and name only
'Sheet2.[a1].CopyFromRecordset cnn.Execute(sql)
brr = cnn.Execute(sql).getrows
End With
'----------------------------------
ReDim subj(0 To [A65536].End(xlUp).Row - 5)
For n = 2 To [A65536].End(xlUp).Row - 4
For c = 4 To Cells(n, Cells.Columns.Count).End(xlToLeft).Column
subj(n - 2) = subj(n - 2) & Cells(n, c) & ";"
Next c
subj(n - 2) = Mid(subj(n - 2), 1, Len(subj(n - 2)) - 1)
dic(Range("A" & n).Value) = subj(n - 2)
Next
'----------------------------------
For m = 0 To UBound(brr, 2)
Set wb = Workbooks.Add
sql = "select * from [总表$A2:E] where 班级='" & brr(0, m) & "'"
wb.Sheets(1).[a3].CopyFromRecordset cnn.Execute(sql)
wb.Sheets(1).Range("A1", "E2") = arr
wb.SaveAs ThisWorkbook.Path & "\" & brr(0, m) & ".xlsx"
k = dic(brr(0, m))
'---------------run OUTLOOK EMAIL--------------
wbStr = ActiveWorkbook.FullName
ActiveWorkbook.Close
Set newMail = OutlookApp.CreateItem(olMailItem)
With newMail
.Subject = Format(Date, "yymmdd") & brr(0, m)
.Body = "请查收" & brr(0, m) & "成绩,谢谢!"
Set myAttachments = newMail.Attachments
myAttachments.Add wbStr, olByValue, 1, "workbook"
.To = k
'.To = Replace(Join(k, ";"), " ", "")
.Save
End With
k = ""
'ActiveWorkbook.Close
Set newMail = Nothing
Next
dic.RemoveAll
Application.ScreenUpdating = True
cnn.Close: Set cnn = Nothing
Set OutlookApp = Nothing
End Sub
|
评分
-
1
查看全部评分
-
|