Click a cell to Open email msg template and add cell content to Subject field in Outlook

[Csmith2303]

Hello and thank you for your interest.

I would like to do the following ... please don't be put off by the size of my post ... I am trying to supply as much information and explanation as I can, as briefly as I can.


When a cell is clicked, I would like it to create a new email using an Outlook message template which inserts the cell contents below into the beginning of the without overwriting the rest of the email template subject.

I currently have a spread sheet (IncNumbers) that has columns that contain relative details

I would like to have an option that state if I would like to assign this matter the the individual - this will then pull out all the information below into a template

A sample of the set out is as follows:

Activity / Lead Generator	Lead Name	Job Title	Company	Partner / Partners Responsible	Notes	Action Due date
Test event 1	Joe Blogs	Partner	Bloggs & Co	Claire	Please email with further information	False Pict

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

I have been using the code below and it works to an extent but cannot pull the dat into the body of the email

any help would be amazing

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
inc = Selection.Value
ans = MsgBox("Assign this action to partner(s)" & vbNewLine & vbNewLine & "Lead Name: " & inc, vbYesNo, "Correct Incident Number?")
If ans = vbNo Then
Exit Sub
Else
End If
Selection.Offset(0, 1).Value = Environ("UserName")
Dim objOut As Outlook.Application
Set objOut = CreateObject("Outlook.Application")
Set obJMailItem = objOut.CreateItem(olMailItem)
strEmailAddr = "Partners Email Pulled Here "
obJMailItem.Recipients.Add strEmailAddr
obJMailItem.Body = "Dear Partner X , you hve been assigned new activy in the business development pipeline document & vbNewLine & vbNewLine Lead Name: & vbNewLine& vbNewLine Lead Description & vbNewLine & vbNewLine Lead Due Date " 'Put the body in here, let me know if you need help with this
obJMailItem.Subject = inc & "-" & "You have a new business development activity"
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub


many thanks
Chris












Kind regards,
TheShyButterflyHi All