Email a range of cells through Excel via Lotus Notes

[fenfool]

I have a spreadsheet that is a maintenance log. The idea is that the user fills out the information in the first five cells, (date, name of person submitting the issue, machine name, issue type, description of issue), then select a value in the sixth cell that would email the information from this row to a list of users on Sheet 2, Column B. When the repair happens, the person doing the repairs fills out three more cells (repair code, details, date of repair), then they would select a value in the final column (column J) that would send a second email to the same list with the new information.

So in short, a drop-down in F2 would send an email with the values of A2-E2; a drop-down in J2 would send A2-I2. There would be as many rows as needed, each email would contain only that row's information. We use Lotus Notes here. I've been able to send an email by selecting a drop-down, but I can't get it to include any information from the spreadsheet, or to email to an address on the spreadsheet, I can only manage to email someone within the code.

Any suggestions or help would be greatly appreciated, I am maybe an intermediate user...I mostly snatch code from the net and bend it to my needs, but I can't seem to bend anything for this task. Thanks in advance!

 

[fenfool]

I am close...I can get an email with data from the spreadsheet, but only if I hard-code the range, as well as the email address. The goal is to have a drop-down in Column F that copies columns A-E from the same row into an email. I have this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Dim x As Integer
Dim UserName As String
Dim MailDbName As String
Dim Recipient As Variant
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim stSignature As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False

'Open and locate current LOTUS NOTES User

Set Session = CreateObject("Notes.NotesSession")
Set WatchRange = Range("F3")
Set IntersectRange = Intersect(Target, WatchRange)
If IntersectRange Is Nothing Then
'Cells(1, 1).Value = Cells(1, 1).Value
Else
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If

' Create New Mail and Address Title Handlers

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Message"

'stSignature = Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)
' Select range of e-mail addresses
'Recipient = Worksheets("Sheet2").Range("B" & x).Value

MailDoc.SendTo = "email@address.com"
MailDoc.Subject = "Notification - Machine Repair Needed"
MailDoc.Body = Range("A3:E3").Value
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set Session = Nothing
.ScreenUpdating = True
.DisplayAlerts = True
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set Session = Nothing
End If
End With
End Sub

I commented out the email selection part, it doesn't work as it is here. Anyone have any thoughts on this?

Thanks in advance!
Jim