Hi Rocketta
Thanks for the response but I'm not sure where I add/run this code?
Alt -F11
Insert>Module from the menu bar
Paste the sub
Change the sheet names and e-mail addresses in the macro
Alt-Q to go back to Excel
If you use Alt-F8 you get a list of your macro's
Select "Mail_test" and press Run
Maybe a other example is better on my site but start with this one
Change the sheet names and e-mail addresses in this two lines
Shname = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
Addr = Array("
[email protected]", "
[email protected]", "
[email protected]", "
[email protected]")
The example below will send each sheet in the Shname Array
to a person In the Addr Array.
In this example four separate mails will be send with one sheet.
Sheet1 to
[email protected]
Sheet2 to
[email protected]
Sheet3 to
[email protected]
Sheet4 to
[email protected]
Sub Mail_test()
Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Shname = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
Addr = Array("
[email protected]", "
[email protected]", "
[email protected]", "
[email protected]")
Application.ScreenUpdating = False
For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub
