|
本帖最后由 zhenghui13 于 2014-6-3 16:02 编辑
是按班级拆分成不同workbook,然后发送吗?发送给谁呢?
请测试一下代码是否可行,EMAIL会保存在草稿箱里.
- Sub ExcelSplit()
- '---------------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
- 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
- '----------------------------------
- For n = 2 To Sheet2.[A65536].End(xlUp).Row
- dic(Range("A" & n).Value) = Range("B" & n).Value
- 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 = "Records"
- .Body = "Please help to find Record Inforamtion of your class"
- 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
复制代码
|
评分
-
2
查看全部评分
-
|