How to send different ranges to multiple e-mail recipients

[Meghan]

I am trying to set up a macros that will allow me to send a specific range of
cells in a worksheet to multiple e-mail recipients. I don't have any problem
creating the first range of cells and e-mail address, but I am not sure how
to set up the next commands. Do you have any suggestions?

Sub MacroTest()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:H7", "A9:H12").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = False

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "(e-mail address removed)"
.Item.Subject = "Test"
.Item.Send
End With

End Sub

 

[Ron de Bruin]

Hi Meghan

The same range to more people ?
Maybe
http://www.rondebruin.nl/mail/tips2.htm

 

[Meghan]

Hi Ron,

I'm trying to send different ranges to different people...each range has
information pertaining to a specific employee. Thanks!

Hi Meghan

The same range to more people ?
Maybe
http://www.rondebruin.nl/mail/tips2.htm

 

[Ron de Bruin]

Not much time now but you can create two arrays and loop through them

In this macro I do it with sheetname and e-mail addresses
Try to use it in your macro

Post back if you have problems
I check this posst later this evening
See also this page for another way to do it
http://www.rondebruin.nl/mail/folder3/mail4.htm


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 (e-mail address removed)
Sheet2 to (e-mail address removed)
Sheet3 to (e-mail address removed)
Sheet4 to (e-mail address removed)


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("(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)")

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




--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi Ron,

I'm trying to send different ranges to different people...each range has
information pertaining to a specific employee. Thanks!