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

[TheShyButterfly]

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 into the beginning of the without overwriting the rest of the email template subject.</SPAN>

I currently have a spread sheet (IncNumbers) that has columns that contain incident numbers that I have manually created based on the year, month and incident number (refer to sample below).</SPAN>

I have created a column for each MonthYear, and a column next to it for the User to enter their User ID when they ‘take’ the next available number so we know that number has been used.
</SPAN>
A sample of the set out is as follows: </SPAN>
[TABLE="align: left"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN></SPAN>
[/TD]
[TD]Sep-2013</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Oct-2013</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Nov-2013</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN></SPAN>
[/TD]
[TD]Incident #</SPAN></SPAN>
[/TD]
[TD]Taken by</SPAN></SPAN>
[/TD]
[TD]Incident #</SPAN></SPAN>
[/TD]
[TD]Taken by</SPAN></SPAN>
[/TD]
[TD]Incident #</SPAN></SPAN>
[/TD]
[TD]Taken By</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN></SPAN>
[/TD]
[TD]Inc201309-0001</SPAN></SPAN>
[/TD]
[TD]xyz</SPAN></SPAN>
[/TD]
[TD]Inc201310-0001</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201311-0001</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN></SPAN>
[/TD]
[TD]Inc201309-0002</SPAN></SPAN>
[/TD]
[TD]abc</SPAN></SPAN>
[/TD]
[TD]Inc201310-0002</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201311-0002</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN></SPAN>
[/TD]
[TD]Inc201309-0003</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201310-0003</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201311-0003</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN></SPAN>
[/TD]
[TD]Inc201309-0004</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201310-0004</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Inc201311-0004</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I have ‘pre-filled’ out coming months so I don’t have to go back each month to create the new incident number based on format above.
</SPAN>
The idea is the operator will click on the next available incident number for the period which will open an Outlook email template (Incident Report), which will populate the beginning of the email Subject with the contents of the Cell (eg. Inc201309-0001), </SPAN>but not overwrite the contents of the template email. … (is this possible?).
</SPAN>
When a new incident has occurred, the user clicks on the next available incident number which again opens a new email message template- (Incident Report) etc….
</SPAN>
I have looked high and low via the internet and can’t seem to find a solution similar to what I need.

I have tried to modify some code that I thought may work but I couldn't work it out (I'm not that familiar/confident with VBA coding)
</SPAN>
I thought about generating a macro, but how to ‘fill’ that down the 1000 rows per each Incident # column?
</SPAN>
OR

is there another solution where a unique, sequential number can be generated and a email generated – I am trying to keep it simple for the users, but trying to ensure we don’t get duplicate incident numbers.

I guess the worst case scenario is that the user will have to copy and paste the job number into the Email Subject field. </SPAN>
I am using Office 2010.

Thanking you in advance. If you need further/better clarification, please don't hesitate to contact me

Kind regards,
TheShyButterfly</SPAN>

 

[TheShyButterfly]

Hi Again Stildawn,

Thank you for that ... I was searching and trying to modify some code I had found on this site (all want to attach file etc), and when I was going to open a new post ... there you were ... on the ball (so to speak )

I followed your instructions and had no problems whatsoever ... I am familiar with some things

There is no more I could wish for .... and even some sense of humour to make me smile (cheeky LOL) ... but I think after all we have been through we've earned that.

You deserve a good rest from me now ...
Thank you again

 

[Stildawn]

I'm glad its all working for you.

No problem at all, I have learned a wealth of knowledge from this site, its good to give back sometimes.

Let me know if you have any issues and I'll try to help out if I can.