Range Question
 

I'm working on a program that will send out month end reports for me. I
have a spreadsheet with all the report names (Subject Line for Email)
and people that receive the emails (email addresses).

All the report names are lined up across row 1 in different columns,
and the people are in column A in different rows. If the person is to
receive a report, there will be an "X" for that person under the
report.
I'm not sure how to explain this, but it confuses me reading this
too...


This is a rough sketch of the layout of the report. We'll see if the
spacing will work out:

________Report 1___Report 2___Report 3___Report 4
Bill_________X________X
Joe________X__________________X
Jim__________________X________X
Bob________X__________________X_________X

What I'm wanting to do is do some code that will look at each report or
person (which ever is easier) and if there is an "X" there, then add the
person to the string "MailTo".

I set my all of my reports to a range, and my people to a range.
Set RptRng = Range("B1", Selection.End(xlToRight))
Set MailRng = Range("A2", Selection.End(xlDown))


I hope this is a good enough explanation to warrant some help!!


TIA

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=377095

Range Question
 

This code should work for you. It will dynamically determine the number
of reports and recipients, so if you add more reports and/or recipients
to the matrix you won't have to change the macro code. Please delete
the code line in *BOLD* below after you have tested it to ensure it
works for you:

Code:
--------------------
Sub sendReports()
Dim repCount As Integer, nameCount As Integer
Dim x As Integer, y As Integer
Dim MailTo As String

' Determine number of reports to send...
repCount = ActiveSheet.UsedRange.Columns.Count
' Determine number of names to send reports to...
nameCount = ActiveSheet.UsedRange.Rows.Count

' Initialize variable...
MailTo = ""

' Loop through report recipient matrix & send reports...
For x = 2 To repCount ' Skip 1 for name column
For y = 2 To nameCount ' Skip 1 for header row
' Determine if report should be sent to recipient...
If ActiveSheet.Cells(x, y) = "X" Then
' If true, update variable with recipient name & comma separator w/trailing space...
MailTo = MailTo & ActiveSheet.Cells(y, 1) & ", "
End If
Next y
' Strip last comma separator from variable...
MailTo = Left(MailTo, Len(MailTo) - 2)
*MsgBox "E-Mail " & ActiveSheet.Cells(1, x).Value & " to: " & MailTo*

'********** YOUR CODE TO E-MAIL REPORT GOES HERE **********

' Clear variable for next report...
MailTo = ""
' Loop to next column to get report recipients...
Next x
End Sub
--------------------

Hope this helps,
theDude


--
theDude
------------------------------------------------------------------------
theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550
View this thread: http://www.excelforum.com/showthread...hreadid=377095

Range Question
 

Impressive Dude, that was exactly what I was looking for. I always have
troubles when it comes to dealing with the Range.

Thank you!!


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=377095