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>

 

[TheShyButterfly]

Hi Stildawn,

Thank you so very much for all your assistance ... you're a star

It's been a long day & night LOL ... now that I've re-read your second message, it dawned on me ... 'replace' the code ... doh!!! Did I mention I was blonde (and greying .... it's all working against me )

Oh WOW ... this is just fantastic ... with the message box stating that the number has already been used if there is data (username) in B3.

God Bless and thank you.

Cheers,
TheShyButterfly

 

[Stildawn]

Sorry mate about all the errors, was in a bit of a rush yesterday and didn't give you the full setup instructions... Many thanks to VoG for helping you out while I was asleep haha.

Glad you like it, in regards to the template thing though like I said I don't know how to bring up a template in outlook, my code (as your probably aware) only creates a new blank email... You can however build a template in the code itself by putting things into the (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") lines of code.

For a quick example you can use this to write a really simple email body:

objMailItem.Body = "Hello" & vbNewLine & vbNewLine & "This email is to confirm that an incident report has been raised using incident number: " & inc & vbNewLine & vbNewLine & "Please reply with any details on the incident that could help our processing" & vbNewLine & vbNewLine & "If you have any questions please let me know" & vbNewLine & vbNewLine & "Regards," & vbNewLine "TheShyButterfly" & vbNewLine & vbNewLine & "Incident Management Department"


Haha just a silly example, as you see its quite long and drawn out but works perfectly... Just remember that you must put all text you want in the body inside "" and use the & symbol to link elements. The vbNewLine part is like an enter key, so two of them straight after each other will give you a blank line between text.

You can also build the signiture part in using the users username (similar to how it puts their username next to the taken incident number). To do that I usually use a Select Case code, let me know if you want this its quite long to set up (depending on how many users you have of course).

See how you get on.

Cheers

 

[Stildawn]

With a little bit of research you can use this code to open up a email template. The body remains but I think it would be easier to hard code the subject here:

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")
Set obJMailItem = objOut.CreateItemFromTemplate("C:\Users\tbaker\Documents\Outlook Template Test.oft") '#### REPLACE WITH THE FULL PATH TO YOUR TEMPLATE FILE #####
obJMailItem.Subject = inc & " - " & "Incident Report" '##### YOU WOULD NEED TO HARD CODE THIS PART #####
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub

This means you can make the body of the email look all pretty in Outlook, save as a template .oft and then load it up by replacing my path with the correct one, and replacing the Subject part with what you want.

I've also removed the email part, as its probably much easier just to use the address book feature in Outlook.

EDIT: Also just a note, you would have to save the outlook template .oft file to a network drive that all the different users can access, to make sure it works. Also when creating the Outlook template, delete any automatic signitures as the code above will add them again (so it will double up if the template already has one in it).

 

[TheShyButterfly]

Hi Stildawn,

You are absolutely incredible .... I was just about to ask you how to open up a message template .... you exceed my expectations

I'm just trying this out now .... thank you so much

Cheers,
TheShyButterfly

 

[Stildawn]

Only just learned myself haha, had to figure it out. Good to know I'll probably use it myself in future.

 

[TheShyButterfly]

Hi Stildawn,
I'm sorry to report that I am not able to get the code to call up the Outlook template ....

I saved the template on our network (on a drive that I can access ... for testing purposes) and replaced the file path with (example: \\npd1231\hbpdyser01\users\iim\2013\Outlook templates\Incident Report.oft

But it comes up with this Run-time error:
Cannot open file: \\npd1231\hbpdyser01\users\iim\2013\Outlook templates\Incident Report.oft ... the file may not exist, you may not have permission to open it, or it may be open in another program. Right click the folder that contains the file and then click 'Properties' to check your permissions for this folder.

I have checked the security and I have full access.

I then thought I'd try it locally from my local drive and entered ....

D:\Documents and Settings\iim\Application Data\Microsoft\Templates\Security Incident Report.oft

But this gave me the same error code as above.

I replaced the space between the Security Incident Report.oft with a \ as I thought perhaps this was the reason .... but still recieved the same error above.

I'm not sure the problem can be resolved by you ... it may be a network issue. But I have opened the Incident Report.oft from within Outlook without any problems from both the network drive and from my local drive.

I might see if my IT network guru has an idea if it is a network security/access issue.

Thank you though, for all your diligence and perserverance ... I am most grateful

I'll let you know how it goes with the network guru.

Kind regards
TheShyButterfly

With a little bit of research you can use this code to open up a email template. The body remains but I think it would be easier to hard code the subject here:

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")
Set obJMailItem = objOut.CreateItemFromTemplate("C:\Users\tbaker\Documents\Outlook Template Test.oft") '#### REPLACE WITH THE FULL PATH TO YOUR TEMPLATE FILE #####
obJMailItem.Subject = inc & " - " & "Incident Report" '##### YOU WOULD NEED TO HARD CODE THIS PART #####
obJMailItem.Display
Set obJMailItem = Nothing
Set objOut = Nothing
End Sub

This means you can make the body of the email look all pretty in Outlook, save as a template .oft and then load it up by replacing my path with the correct one, and replacing the Subject part with what you want.

I've also removed the email part, as its probably much easier just to use the address book feature in Outlook.

EDIT: Also just a note, you would have to save the outlook template .oft file to a network drive that all the different users can access, to make sure it works. Also when creating the Outlook template, delete any automatic signitures as the code above will add them again (so it will double up if the template already has one in it).

 

[Stildawn]

Hmm

What exactly have you got in the code? Must be in the brackets and within the "" so it should look like this:

Set obJMailItem = objOut.CreateItemFromTemplate("D:\Documents and Settings\iim\Application Data\Microsoft\Templates\Security Incident Report.oft")

The first network drive example may be an issue cause it doesnt look like the network drive has been mapped, it should look like a normal path for example: F:\npd1231\hbpdyser01\users\iim\2013\Outlook templates\Incident Report.oft (if F was the letter mapped to the network drive.

Alternatively you might have got the path by navigating to it using a shortcut or something. Open up "My Computer" and in the list of drives (usually under Network Locations) click on the drive there and naviage to the folder where the template is saved, then click on the address bar in the explorer window to get the proper mapped path?


Also make sure the template is not open in Outlook, it might even be a good idea to close Outlook completely and then reopen. Also find the template, right click on the file then go properties and along the bottom of the window that pops up there is the Hidden/Read Only check boxes, make sure its not Read Only and hit apply.

 

[TheShyButterfly]

Well, thank again

I thought I had the path to the next work correct but with your tip of copying the path from the address field .... obvious ... but I didn't think of it (tsk tsk) .... so I entered it and progress was made At least when I run the code it no longer gives me an error on that line (I'll try the code from the local drive a little later)....

YIPPIE ... it worked a charm ... it opened up the Outlook template from the network drive
Thank you so much .... I tested it and completed the fields within the email template without any problems and I recieved it with the inputted information ....

I wish I could send you an award .... this is so perfect ... thank you

Would you mind if I add you as a friend/contact ?

I hope your day is as good as you've made mine

Cheers,
TheShyButterfly

 

[TheShyButterfly]

H e l l o ... ... trying to stretch the friendship ....

Great news is .... it works like a dream from the local D: as well ....

Just a twist ..... would you have an idea of how to create a macro button in MS Outlook that will open up the spread sheet when it is clicked.... ????

Don't stress out over it if you can't help me with that ... you have done wonders for me already

 

[Stildawn]

Certainly:

You want code that looks like this:

Sub basOpenIncidents()

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open "G:\Airfreight_Export\Export Airfreight\Export Air Pre-Alert Sender.xlsm" '#### CHANGE TO THE ACTUAL PATH TO YOUR SPREADSHEET REPLACING MINE HAHA ####

Set objExcel = Nothing
End Sub

To set this up:

- In Outlook (you'll need to do this on every users machine) click Alt + F11 to bring up the VBA window.
- Insert -> Module
- Paste the code above into the blank white area
- Hit the save button
- Close the VBA window

- Back in Outlook, right click on a blank space of your ribbon (the tool bar along the top with all the buttons on it)
- Select "Customize The Ribbon"
- This will open up a window, on the right side panel in the list box it will have "Main Tabs" then "Home (Mail)" with "Home (Mail)" highlighted hit the "New Group" button, then rename this button (to whatever you want, but I call mine "Macros")
- Highlight this new group (which should be under "Home (Mail)")
- On the left side panel in the drop down box find the option "Macros"
- This should list the available macros in the listbox below the drop down box (still on the left hand side)
- It should only show one and it will be called "Project1.basOpenIncidents", highlight that and then click "Add >>" button in the middle. This should now show in the new group in the right hand panel. You can rename it here to whatever you want to call it.
- Then hit OK at the bottom and if all things going to plan a new shiny button should be on your ribbon along the top of Outlook.

Now if you get an error when clicking the new button, first thing to do is to make sure Macros are enabled. To do this:

- In Outlook click the "File" tab along the top (probably to the left of Home)
- On this screen there should be a "Options" button down the side, click this it will bring up a window.
- In the list on the left hand panel, find "Trust Center" click that
- Then in the main panel click "Trust Center Settings"
- In this new screen on the left hand panel find "Macro Settings" click that
- Then in the main pannel select the option "Enable All Macros"
- Then click OK at the bottom
- Ok again haha
- Now close Outlook and restart.
- Your button should work now


If you get any other errors let me know what they are

I hope you saved your excel file as xlsm otherwise you might have lost all the code by the way.