I change rows manually. I have to enter data into the spreadsheet which
comes to me in emails. And depending on circumstances the notification may
need to go to different people. So automation of that degree is just not
practical.

This way, I simply select any cell in the line concerning the report I want
to notify/remind someone of, and start the macro(via command button I added)
to get an email filled out with the proper names and dates from that row.
Each row shows status on a report from a different person.

The macro just keeps me from having to type the same emails 50 times a day.

Thanks for your help though. I used your second option, setting each
relative referance as a variable. It works great, and my code is much easier
to understand now as well.

"JLGWhiz" wrote:

Taking a closer look at your code, I don't know why the absolute cell
references could not be used. Unless I missed something, there is nothing
that makes the use of relative refences necessary, like changing rows. It
appears that all the information is on one row and that the data in each cell
is constant and applied to the same places within the email for each
application. So you could just as easily use:

Range("A2").Value

Instead of ActiveCell(1, 2), assuming A1 is the active cell. And the other
cells could all be treated the same. Range("A3").Value for ActiveCell(1,3),
etc.

"voayger2001dl" wrote:

I have created a macro to generate an automated email notification when a
command button is clicked, which uses data from the active cell and cells
near it using ActiveCell(1,x) where x is the number of cells to the right of
the active cell i want information from.

The problem is, It only works properly if the first cell in the row is the
Active Cell. I am trying to make it a little more idiot proof. (Less
computer literate personnel may be using this spreadsheet)

Is there a way to either move the active cell to the beginning of the row
when the macro starts, or referance the cells directly in the current row?
(ie, get info from cells in column A, B, and E of current row)

Here is the code I have now:

Sub InitialNotification()
' Is working in Office 2000-2007
' Generates an initial notification email

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FlightDate As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = _
Chr(13) & _
Chr(13) & _
ActiveCell(1, 2) & ", " & Chr(13) & _
Chr(13) & _
"An EPR must be written for " & ActiveCell & ". " & Chr(13) & _
"Suspense dates are listed below. " & Chr(13) & _
Chr(13) & _
"Flight: " & ActiveCell(1, 3) & Chr(13) & _
"Squadron: " & ActiveCell(1, 4) & Chr(13) & _
"Closeout: " & ActiveCell(1, 5) & Chr(13) & _
Chr(13) & _
"Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _
Chr(13) & _
Chr(13)

SigString = "C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace
FOUO with the name of your

'signature to make this work
If Dir(SigString) < "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If


With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "EPR- " & ActiveCell
.Body = strbody & Signature
.Display
End With


Set OutMail = Nothing
Set OutApp = Nothing
End Sub

This function is also included:

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
' This is used by all three email reminder generating Subs

Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function