making cell reference variable

[james potter]

I already had a topic related to this topic but I think I was not making myself easy to understand. So I'll make another attempt.

First what will my vba-code do for me. It generates a email message where certains fields are filled with value of cells in my worksheet. This macro is triggered by changes in a certain cell.

This is the vba-code to trigger to call the macro. (part1)

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
     If Target.Address = "$K$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call report_b7

This next part is the actual vba-code of sub report_b7 (part2)

Sub report_b7() 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Range("$J$7")) 
    .Subject = "text" & Format(Range("$B$7")) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _ 
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "Afdeling Reporting" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub

I would like to make this second part (part2) vba-code cell reference independent. As you can see, is every cell reference in the same row. So I think there is a way to put the cell reference of the first vba-code (part1) in memory, like a name. Then in the second part vba-code (part2) I can refer to this name in combination with offset.

I think it's possible to do, but I do not know how. I have tried but failed.

Any help is appriciate!

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
     If Target.Address = "$K$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call report_b7(Target)
'rest of code

Sub report_b7(Rng As Range) 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Rng.Offset(0, -1)) 
    .Subject = "text" & Format(Rng.Offset(0, -9)) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Rng.Offset(0, -9)) & _ 
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "Afdeling Reporting" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub

 

[james potter]

Thanks a lot! It works like a charm.