james potter
New Member
- Joined
- Aug 15, 2005
- Messages
- 45
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)
This next part is the actual vba-code of sub report_b7 (part2)
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!
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)
Code:
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)
Code:
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!