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>

	A</SPAN></SPAN>	B</SPAN></SPAN>	C</SPAN></SPAN>	D</SPAN></SPAN>	E</SPAN></SPAN>	F</SPAN></SPAN>
1</SPAN></SPAN>	Sep-2013</SPAN></SPAN>		Oct-2013</SPAN></SPAN>		Nov-2013</SPAN></SPAN>
2</SPAN></SPAN>	Incident #</SPAN></SPAN>	Taken by</SPAN></SPAN>	Incident #</SPAN></SPAN>	Taken by</SPAN></SPAN>	Incident #</SPAN></SPAN>	Taken By</SPAN></SPAN>
3</SPAN></SPAN>	Inc201309-0001</SPAN></SPAN>	xyz</SPAN></SPAN>	Inc201310-0001</SPAN></SPAN>		Inc201311-0001</SPAN></SPAN>
4</SPAN></SPAN>	Inc201309-0002</SPAN></SPAN>	abc</SPAN></SPAN>	Inc201310-0002</SPAN></SPAN>		Inc201311-0002</SPAN></SPAN>
5</SPAN></SPAN>	Inc201309-0003</SPAN></SPAN>		Inc201310-0003</SPAN></SPAN>		Inc201311-0003</SPAN></SPAN>
6</SPAN></SPAN>	Inc201309-0004</SPAN></SPAN>		Inc201310-0004</SPAN></SPAN>		Inc201311-0004</SPAN></SPAN>
etc</SPAN></SPAN>

<TBODY>
</TBODY>

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>

 

[Stildawn]

Right click on your sheet then click "View Code"

Then paste all of this code in the blank white area, save and test it out. It works by double clicking on the cell with the incident number you want to use, this will bring up a outlook email, I'm not the greatest with Outlook automation so I don't know how to load up a template, but you can build your template in the code below anyway, let me know if you need help with this...

It will also put your username in the cell to the right of the cell you double click on...

Try it out and let me know how you go:

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
Selection.Offset(0, 1).Value = Environ("UserName")
Dim objOut As Outlook.Application
Set objOut = CreateObject("Outlook.Application")
Set obJMailItem = objOut.CreateItem(olMailItem)
strEmailAddr = "email address" 'enter email address in here
obJMailItem.Recipients.Add strEmailAddr
obJMailItem.Body = "" 'Put the body in here, let me know if you need help with this
obJMailItem.Subject = inc & "-" & "any thing else in the subject here"
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub

 

[Stildawn]

To add extra user error checking you can add this code in replacing the "Selection.Offset(0, 1).Value = Environ("UserName")" line

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

 

[TheShyButterfly]

Thank you Stildawn for your quick response

I'll give this a try out .... can I put this code in at the end of the code below? or where would it be best placed.

I'm so glad that my lengthy explanation didn't put you off

Thank you so very much again

Cheers,
TheShyButterfly

 

[TheShyButterfly]

Hi Stildawn, I have followed your instructions and have started by just using the code you initially provided.

I hate to be the bearer of bad news, but when I double click on A3 cell (which contains Inc201309-001) to 'run' the code, VB opens with a 'Compile error'....


I'm afraid that I am unable to solve what is causing the error .

I thought I would do a screen dump so that you can see the layout of my sheet, but also the VB screen to show what was entered in the code window and spot where the code failed and the Error message.

At this point I don't know if there are any other glitches (I'm thinking positively) ...

If this coding eventually works, together with the additional coding you provided in your second reply, I will be ecstatic. As I mentioned in my previous reply, I'm not sure where to insert the additional code.

I tried searching the FAQs here regarding posting and could find nothing to indicate whether it was OK to paste screen dumps in the posts, so I apologise in advance if it's not permissible.

Cheers,
Ingelise

 

[TheShyButterfly]

Ok ... here is the VB feedback that couldn't be seen because of the hundreds lines of code ...

So the Compile error is at line 2
inc = Selection.Value

The VB Error message says, 'Compile Error: Variable not defined'

Cheers,
TheShyButterfly

 

[VoG]

Delete "Option Explicit" at the top of the module. It may then work.

 

[TheShyButterfly]

LoL, Thank you very much ... I deleted it but now I'm getting a 'Compile Error' "User-defined type not defined" at line 9,

Dim objOut As Outlook.Application

I tried to define it similar to the inc = Selection.Value, but that didn't do anything but give me the same compile error. So how do I 'define' "objOut = Outlook.Application" ?

Would that mean that I would also need to define "obJMailItem" too? because that may be cause for another compile error (I'm just thinking ahead


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
inc = Selection.Value
objOut = Outlook.Application
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
Selection.Offset(0, 1).Value = Environ("UserName")
Dim objOut As Outlook.Application
Set objOut = CreateObject("Outlook.Application")
Set obJMailItem = objOut.CreateItem(olMailItem)
strEmailAddr = "email address" 'enter email address in here
obJMailItem.Recipients.Add strEmailAddr
obJMailItem.Body = "" 'Put the body in here, let me know if you need help with this
obJMailItem.Subject = inc & "-" & "any thing else in the subject here"
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub

I do appreciate any assistance in advance.

Cheers,
TheShyButterfly

 

[VoG]

This uses early binding so:

1) Go to the VBA editor with the shortcut Alt - F11
2) Click on Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
Where ? is the Outlook version number

then try again.

 

[TheShyButterfly]

Thank you,

I checked the References ... and yes, there was no tick mark .... now there is

I then tried it and ..... like magic

Thank you so very much ... I would never have thought (I never knew about the 'References tools' .... I might try that out on other applications (like MS Access, Word .. ) in case I have courage to try some VB code in the future.

I am so very grateful to you and the hard work & effort Stildawn made...

I now am going to create the Outlook email template and see how I go with getting it to be called up ...

Have a wonderful day/evening

Cheers,
TheShyButterfly