邮件的每个工作表与在Excel中使用VBA A1单元格地址
此宏会将每张纸发送给一个人。
-
用工作表创建一个新的工作簿。
在邮寄之前保存工作簿在发送后，从硬盘中删除文件。
Sub Mail_every_Worksheet()
Dim strDate As String
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "@" Then
sh.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail ActiveSheet.Range("a1").Value, _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub