I'd guess that the line:
Msg = Msg & "Your target for FY06: " & Sheets("Sheet1").Range("B1").Value &
vbCrLf & vbCrLf
is the one causing the error? If so, it means you no longer have a sheet
called Sheet1. You'll need to change the sheet (and maybe the range?) from
which you want to get the value
--

Simon


"periro16" wrote:

>
> That has made it more clear - thanks
>
> How do I get rid of the Subscript out of range error??
> I am using he following and it gives me the range error above. Is
> there a way around it?
>
> Code:
> --------------------
> .....
>
> Msg = vbCrLf
> Msg = Msg & "Dear " & Cells(r, 3) & vbCrLf & vbCrLf
>
> Msg = Msg & "Total Executive Interviews to date: " & Cells(r, 17) & vbCrLf & vbCrLf
> Msg = Msg & "Your target for FY06: " & Sheets("Sheet1").Range("B1").Value & vbCrLf & vbCrLf
> Msg = Msg & "Remaining to hit target: " & Cells(r, 21) & vbCrLf & vbCrLf
> Msg = Msg & "In order to achieve this you need to conduct"
> ....
> --------------------
>
>
>
> --
> periro16
> ------------------------------------------------------------------------
> periro16's Profile: http://www.excelforum.com/member.php...o&userid=26345
> View this thread: http://www.excelforum.com/showthread...hreadid=396386
>
>