VBA - Macro to open excel file & create email based on Excel cell contents

[TheShyButterfly]

Hi again, and thank you for taking the time to check out my problem.

I am still not confident using VBA but have tried to amend code to enable me to get the functionality I am after, but I have failed.
I'll try to break it down for easier reading (hopefully).

The objective:
Whenever a user clicks on a macro button on the Outlook toolbar, it will open a specific Excel file, whereby the user selects a number and an Item Description which then opens an Outlook email with the selected number and Item Description in the Subject field.

3 stages:

Stage 1:

Macro button in Outlook opens Excel spreadsheet.

Stage 2:

A) User clicks on the next available number which automatically enters the user's ID/Name and the Date the Number is Taken (Date is to remain static so when another user opens the Excel file, the date the previous users recorded is not changed).

B) User selects Item Description in Excel from dropdown list.

C) When user is satisfied with the selection, the user clicks on email, which generates an Outlook email
based on a template.

Stage 3:

The email opens with the selected number and Item Description in the Subject of the email.

I do have a sample spreadsheet with layout email screen dump but that is of little use when it cant be uploaded So I'll try to generate the look of the spreadsheet in the table below ... with sample data.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Incident #[/TD]
[TD="align: center"]Taken By[/TD]
[TD="align: center"]Date & Time[/TD]
[TD="align: center"]Item Description[/TD]
[TD="align: center"]Email[/TD]
[/TR]
[TR]
[TD]Inc201411-0001[/TD]
[TD]iim[/TD]
[TD]20/10/14[/TD]
[TD]This is from a drop down list FROG[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Inc201411-0002[/TD]
[TD]bba[/TD]
[TD]21/10/14[/TD]
[TD]This is from a drop down list
Dog[/TD]
[TD]Email[/TD]
[/TR]
</tbody>[/TABLE]



I already have a spreadsheet with similar columns and functionality, (2 columns: INCnumber & User ID) which only
generated an email after double clicking on the IncNumberand inserting the INCnumber in the email subject and recording the user's ID.

I based my sample spreadsheet on the one I had, and just added 3 columns (Date & Time, Item Description and Email), because more functionality is required.

The code for the similar spreadsheet is Under 'General' and Worksheet_BeforeDoubleClick
****************************************************************************************
[

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
inc = Selection.Value
ans = MsgBox("Is this incident number correct?" & vbNewLine & vbNewLine & "Incident Number: " &
inc, vbYesNo, "Correct Incident Number?")
If ans = vbNo Then
Exit Sub
Else
End If
If Selection.Offset(0, 1).Value = "" Then
Selection.Offset(0, 1).Value = Environ("UserName")
Else
MsgBox "This incident number has already been assigned, please select again", , "Incident
Already Assigned"
Exit Sub
End If
Dim objOut As Outlook.Application
Set objOut = CreateObject("Outlook.Application")
'Note: Change the file path in the line below between the ""marks to the file path of where the
Outlook template is store,
'and include the name of the template
Set obJMailItem = objOut.CreateItemFromTemplate("S:\SRC\Dispatch\Incident Reports\Incident
report.oft") '#### REPLACE WITH THE FULL PATH TO YOUR TEMPLATE FILE #####
'Note: the line below enables you to include text in the Subject field of the email. Enter in text
ONLY between the "". You can edit the
'subject field once the email is generated.
obJMailItem.Subject = inc & " - " & "SAC brief SITE NAME eg STP - EVENT TYPE " '##### YOU WOULD
NEED TO HARD CODE THIS PART #####
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub

**************************************************************************************
The Outlook VBA code(macro) module is located under General and basOpenIncidents:

Sub basOpenIncidents()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open "S:\SRC\Dispatch\Incident Reports\Incident Number List and Report-NWK.xlsm" [h=2]Set objExcel = Nothing
End Sub

[/h]

Thanking you in advance for any assistance.

I'm sure there will be details that you'll need clarification on since I can't upload the spreadsheet.

Cheers,
ShyButterfly