Referencing a cell for a value in VBA code

[Todd Huttenstine]

Below I have 2 lines of a code I am using. I have a value
in cell A1 on sheet2 anda value in cell A2 an sheet2. I
would like to specify for the first code to look in cell
A1 sheet2 to get the value "Your Stats" instead of hard
coding it in the actual VBA code like done below. I would
also like to specifiy the second code to look in cell A2
sheet2 to get the value "Here are your stats " instead of
hard coding it in the VBA code. How do I do this?
Thank you

Todd Huttenstine

objMessage.Subject = "Your Stats"

objMessage.Text = "Here are your stats " & cell.Offset(0,
1).Value

 

[Bob Phillips]

Todd,

objMessage.Subject = Worksheets("Sheet2").Range("A1").Value
objMessage.Text = Worksheets("Sheet2").Range("A2").Value & cell.Offset(0,
1).Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

here is the current code I am using. I used the concept
Bob suggested but it didnt work. I keep getting a debug
error on that part. I need it to look in that specific
range and its not working. Any suggestions?


Sub EmailNow()
'
' EmailNow Macro
' Macro recorded 11/16/2003 by SEGeneric
'

'
Sheets("Team Management Database").Select
Dim cell As Range, cell2 As Range
Dim shRng As Range
Dim sh As Worksheet, sharr() As String
Dim wb As Workbook
Dim i As Integer
Dim objSession As Object, objMessage As Object,
objOneRecip As Object
Dim objAttachmt As Object

Set sh = ThisWorkbook.Sheets("Team Management Database")
i = 1
For Each cell In sh.Range("a3", Range("a3").End(xlDown))
Set shRng = cell.Offset(0, 9)
ReDim sharr(1 To shRng.Offset(0, 50).End
(xlToLeft).Column - _
shRng.Column + 1)
For Each cell2 In sh.Range(shRng, shRng.Offset(0,
50).End(xlToLeft))
sharr(i) = cell2.Value
i = i + 1
Next cell2
ThisWorkbook.Sheets(sharr).Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:="C:\Sheets.xls"
Set objSession = CreateObject("MAPI.Session")
objSession.Logon
Set objMessage = objSession.Outbox.Messages.Add

'HERE IS WHERE THE PROBLEM STARTS
objMessage.Subject = Worksheets("Team Data").Range
("K1").Value
objMessage.Text = Worksheets("Team Data").Range
("K2").Value & cell.Offset(0,
'HERE IS WHERE THE PROBLEM ENDS

1).Value
Set objAttachmt = objMessage.Attachments.Add
objAttachmt.Source = ("C:\Sheets.xls")
Set objOneRecip = objMessage.Recipients.Add
objOneRecip.Name = cell.Offset(0, 2).Value
objOneRecip.Type = 1
objOneRecip.Resolve
objMessage.Send showDialog:=False
objSession.Logoff
wb.Close savechanges:=False
i = 1
Kill "c:\sheets.xls"
Next cell
End Sub