Email To: cell value on another worksheet

[marcosis]

Hey all!

I have a excel macro that does all kinds of things in an email. Copies snippets of data into there, saves and attaches its self to the email and sends it to an email address shown is a cell, as well as CC'ing whoever i need.
The code used for this section is.

With OutMail
    .To = [z6].Value & "; " & [aa6].Value & "; " & [ab6].Value
    .CC = ""
    .BCC = ""

Which is perfect, BUT.......... id like the cells it reads the values from to be on another tab/sheet that will be hidden.

How do i alter the above to read, for example, Z6 on another sheet named Email Template.

Thanks in advance!

[ByteMarks]

You could just store the recipients in a variable:

Dim recip As Variant
Dim rng As Range
'if recipients are listed in column A
With worksheets("Email Template")
    Set rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
'assign to variable
recip = Join(Application.Transpose(rng.Value), "; ")
'test
msgbox recip
with OutMail
    .To = recip
end with

[danielexcelvba]

You could change

 [z6].value

to

 sheets("Email Template").[z6].value

[marcosis]

Cheers both.

But in particular danielexcelvba. I'm after simplicity and what more could i ask for!