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

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.

Examples of Excel Spreadsheet and layout when email is generated are attached.

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 I require 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"

Set objExcel = Nothing
End Sub

Thanking you in advance for any assistance
Cheers,
ShyButterfly

AttachmentSize
Number List and Report EXAMPLE1.xlsm236.36 KB