If your mailId is configured to Outlook Express; following code will send mails of each excel sheet
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, _
ActiveSheet.Range("b1").Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub
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, _
ActiveSheet.Range("b1").Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub
No comments:
Post a Comment