Solved

Email Template in Outlook

Posted on 2013-10-31
29
691 Views
Last Modified: 2014-01-10
Please advise how to design an email template (for Microsoft Outlook 2010) that will hold certain fields (a mixture of free text, numbers and some standard wording to come preferrably from a drop down list).

The purpose:
Someone is advising a wider team to take into account these fields when putting their time against a project.
The emails are cc'd to a central mailbox
Each month I want to be able to pull the data from these fields into a database so that we track all of these different projects and the reasons that these projects are having this notification sent out.
300 points for a how to reply - 500 points for a ready-made example please :-)
Sample-for-Email-template.xlsx
0
Comment
Question by:Lomed_Geoff
  • 14
  • 10
  • 2
  • +1
29 Comments
 

Author Comment

by:Lomed_Geoff
ID: 39666722
It has now been 3 weeks!  Please can you explain how this can happen for a request that is pretty routine and not complicated.
Although I pay monthly but most months do not utilise the entitlement, when I do use it there has to be a response within a short time otherwise what am I paying for?!  
Please consider refunding me for 1 month - assuming that there is a response in the next couple of days!
Thank you
0
 
LVL 47

Expert Comment

by:apache09
ID: 39679683
The recipients or Users of the email form...

Are they only going to be internall Users
I.e. all users use the same exchange server and are a member of the Same Exchange GAL?

Or are these emails going to external mail accounts.
Then these recipients are to complete the form and send back cc: the central mailbox?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39679686
Hi, Lomed_Geoff.

Will this be used in a corporate environment with Exchange as the mail server, where all the users have an Exchange mailbox?
0
 
LVL 24

Expert Comment

by:diverseit
ID: 39679759
Hi Lomed_Geoff,

You have two of the best Experts on this...I'm only here to assist if needed. Once we get your answer to these questions we'll be able to get this resolved for you quickly.

Looking forward to your reply!
0
 

Author Comment

by:Lomed_Geoff
ID: 39681187
Thank you - this sounds great....
Yes, it will be only within our corporate environment ie respondents will be employees only.  
We are a worldwide company so we do have several different server locations around the world but I am not sure that is significant?
We do all already have central mailboxes and  I have even set one up in the distant past but I have now forgotten how to do it :-(
But essentially people around the world will have access to a central mailbox.  I want to be able to circulate the template to them and have them start entering the data into it for centralised capture.
Let me know if you need more information.
Lome_Geoff
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39681215
The best solution is probably a custom Outlook form.  Will people be sending the form (i.e. creating an email and sending it to the central mailbox) or will they go to the central mailbox and create an item directly in it (like creating a post)?
0
 
LVL 47

Expert Comment

by:apache09
ID: 39681793
If the form in used internally only, I think we could get a form working in some fashion or another.

Im just think though......

If the idea is for them to complete some data in the form, the email it, only for the data to be taken out and put into a list of some sort so someone can look at it or track projects...

Is it possible something else could be used here, that may meet your needs better?

For example, does your organization have an intranet? or a SharePoint site that accessible throughout the organization?

If they do, what about using a SharePoint list to allow users to directly input this data?
Then if they need to change something because they made a mistake or update it, they can do it directly.

Alternatively, it may be possible that staff/users can enter their data directly into an Excel Workbook.

Perhaps even using a shared workbook or similar
http://office.microsoft.com/en-nz/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx

Then if you want to take this data out and put it into a data base it may be a lot easier, since you leaving out the middle man (Outlook From)
0
 

Author Comment

by:Lomed_Geoff
ID: 39682322
To BlueDevilFan:  It will be the former - creating their email and sending to the central mailbox.

To apach-09:  We do have an intranet - not sure about a Sharepoint site.  I believe (though it may have changed in last couple of years), that whilst the mailboxes in outlook can be used by all, we have not been able to access common drives.  This is an internal constraint.  So a Finance team can have a worldwide common directory and an operations team can have, but the silos are kept separate.  I think IT probably has a common drive/directory for all their staff but nobody else.  
But I don't know about Sharepoint.  
Also bear in mind that not all the people sending emails will feel comfortable with Excel.  Some more some less.  To ensure compliance I would want to avoid asking people who never use Excel to remember to daily update an excel spreadsheet.
The email actually serves two purposes - it is a notifcation of an event to their line manager.  By ccing to the central mailbox these notifications and the detail within them are then captured for consolidation.

Hope this helps
Lomed_Geoff
0
 

Author Comment

by:Lomed_Geoff
ID: 39690301
Hi  - I am in the UK and guessing that you are both in the USA hence the Thanksgiving hiatus?!  Hope to hear from you soon :-)
Lomed_Geoff
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39695121
Lomed_Geoff,

I've put together the following custom form.  In your original post you mention that you want some fields to use a drop-down list, but you didn't mention which fields and didn't provide the values that would go in the lists.  For that reason, I've used text fields in the form.  If you'll tell me which fields need to use a drop down and provide the values that go in the list, then I'll change the form accordingly.

Also, please pass on any other requirements you have.  I also need to know what you'd like to call the custom form.

Screenshot
0
 

Author Comment

by:Lomed_Geoff
ID: 39695546
Thank you so much!  I didn't provide the data you mention only because I am hoping to be able to understand this enough to make my own amendments eg may need to add more task fields.  Would this editing be straightforward?
I am travelling at the moment but will provide sample lists for the dropdowns so you can put something in for me to further edit...
Geoffrey
0
 

Author Comment

by:Lomed_Geoff
ID: 39716386
Hi BlueDevilFan - this is really strange - I checked in to see why no response over past week - and now see that a further message from me with updated attachments isn't here and that you are probably still therefore waiting for me - don't know what happened.
Anyway please see attached.  I have changed the form a little to give people more scope to add in branch, task codes.  And provided 3 lists on subsequent tabs to be used in the drop downs.  So 3 Function Code boxes would all reference the same Function Code drop down list etc.
These lists are dummy and I would anyway need the ability to get behind the scenes to amend these lists as they would change regularly.  Indeed once set up I would want to know how to change the form itself should the need arise.
Please let me know if you need anything else.
Sample-for-Email-templatev2.xlsx
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 39728886
Geoffrey,

The form described in Sample-for-Email-template2.xlsx is different from the original form description, so I stuck with the original form description (which I'd already built a template for) and added one pull-down menu to show you how that's done.  I've attached the form to this post.  Here's what you'll need to do.

1.  Download the attachment
2.  Change the attachment name from LomedGeoff1.oft.txt to LomedGeoff1.oft
3.  Open Outlook
4.  Click New Items and select More Items, Choose Form
5.  Click the pulldown next to Look In and select User Templates in File System
6.  Navigate to where you saved the attachment and select it
7.  When the form opens, go to the Developer tab and click Design This Form
8.  When the form opens in the designer, edit the form as desired, then click Publish and select Publish Form As.
9.  When the Publish Form As dialog appears, decide on where you want to publish the form.  If you want everyone to have access to the form, then you'll want to publish it to the Organizational Forms Library.  You will need the necessary permissions to do that.  If you aren't the email admin at your company, then you'll need to talk to them about getting the necessary permissions or you'll need to give them the form and let them publish it for you.
LomedGeoff1.oft.txt
0
 

Author Comment

by:Lomed_Geoff
ID: 39738369
Thank you BlueDevilFan,
Got all the way up to your step 7 above - but I don't have a Developer Tab showing in Outlook - how do I get that to appear?
Lomed_Geoff
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 76

Expert Comment

by:David Lee
ID: 39738711
Lomed_Geoff,

See this Microsoft page for instructions.

http://msdn.microsoft.com/en-us/library/bb608625.aspx
0
 

Author Comment

by:Lomed_Geoff
ID: 39746325
Thank you BlueDevilFan,
Ok - I have got there.  As you know I will be adding more detailed drop down lists so I looked behind the one you have put there for me and gone into Properties and then Value and I can see that you have the list there in the field called Possible Values.  My question is how did you get that in there other than manual copying in?  If I have a list that could easily run to a couple of hundred choices, I was expecting there to be a reference to a list somewhere and I would then direct it to that list as in Excel.  Is this not the case?

If I want to add more boxes, I couldn't see where you create these, but I seem to be able to copy and paste existing ones.  In fact I tried this just now with the drop down example - and couldn't see how to add in any choices to the drop down.  Sorry if this is obvious!

Thank you so much for your help so far and I wish you and yours a Happy 2014.
Lomed_Geoff
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39746513
No, that's not the case.  You can type it in, copy and paste it in, or write some code that adds the values when the form opens.  If you went with the latter, then you could reference an outside list.  For that to work, the list would have to be stored in a location that's accessible to everyone who would use the form.

When you're in the form editor there should be a button labeled "Control Toolbox" on the ribbon.  Click it and a palette of controls should appear.  Click the control type you want, then draw that control on the form.

Thanks!  Happy 2014 to you too!
0
 

Author Comment

by:Lomed_Geoff
ID: 39746719
Hi
I just tried pasting a vertical list into the Possible Values box - and it only took the very top one.  Presumably then I have to somehow convert that vertical list into a horizontal list if I am not to type in each one individually?
As there are >200 items on the list I probably am going to have to use this code you are talking about.  I want the template to have this stored as you have done in your example before people open it, so then are we talking about a code not to populate it, but to make the vertical list a horizontal one so I can paste it in myself before storing it centrally?
Maybe that is a simple thing to do?
Thank you I have found the Control Toolbox - so I other than this list thing - I am all set and we're done.
Lomed_Geoff
0
 
LVL 24

Expert Comment

by:diverseit
ID: 39754496
Hi Lomed_Geoff,

Do you have any more questions or have the Experts provided you with a solution?

Please let us know otherwise you can close this question by selecting the appropriate comments as your solution.

Thanks,

diverseit
EE Zone Advisor
0
 

Author Comment

by:Lomed_Geoff
ID: 39758874
Thank you - my last outstanding question to BlueDevilFan is inside my last comment above.  Without this, I can't use the solution because I can't populate the dropdowns. Here it is again:

As there are >200 items on the list I probably am going to have to use this code you are talking about.  I want the template to have this stored as you have done in your example before people open it, so then are we talking about a code not to populate it, but to make the vertical list a horizontal one so I can paste it in myself before storing it centrally?
Maybe that is a simple thing to do?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39758882
Geoff,

Sorry, I thought I'd replied.  What type of file is your vertical list stored in?  I can't tell you how easy it'll be to convert it to a horizontal list without knowing that.
0
 

Author Comment

by:Lomed_Geoff
ID: 39758890
Thank you - it will always be in Excel.  Usually 4 letter codes, possibly longer (but rarely).  No punctuation.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39758892
That's easy enough.  Is it one code per row with the code in column A?
0
 

Author Comment

by:Lomed_Geoff
ID: 39758911
It can certainly be sourced in that way.  I am going to be editing the template to add in more drop downs - all with similar format.  ie say there will be 5 drop down lists, I can ensure that each of these is one code per row with the code in col A.  So if what you provide me is something that I can "run" against any file with col A, that would be all I needed.
Then I could paste them one after the other each month into the required areas, horizontally so that the template picks them up.
Thank you
Geoff
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39761831
Geoff,

I've written a script (attached) that will convert any Excel workbook to a horizontal list so long as the values are in column A and there are no blank cells between the first row and the end of the list.  Follow these instructions to add the script to your computer

1.  Download the attached file
2.  Change the file name by removing the .txt extension so the file name is ConvertToList.vbs
3.  Move the renamed file to your "Send To" folder.  The path will be something like c:\Users\<username>\AppData\Roaming\Microsoft\Windows\SendTo where <username> is your user name.

To use the script

1.  Open Windows Explorer
2.  Right-click on any Excel file and select Send To -> ConvertToList.vbs
3.  When Notepad opens, select the contents, then copy and paste as needed
ConvertToList.vbs.txt
0
 

Author Comment

by:Lomed_Geoff
ID: 39765836
Brilliant - this works!  So, I have tested this by amending your template by moving some boxes around and creating new ones - and published it to my personal forms area.  I have now gone back to your original instruction:
When the Publish Form As dialog appears, decide on where you want to publish the form.  If you want everyone to have access to the form, then you'll want to publish it to the Organizational Forms Library.  You will need the necessary permissions to do that.  If you aren't the email admin at your company, then you'll need to talk to them about getting the necessary permissions or you'll need to give them the form and let them publish it for you.
I was not able to put it into Org Forms Library so I guess I now need to get permission to do that.
Assuming this all goes well then the last step - and last question for you please - is how to extract the answers given in these emails, from their central mailbox location - and into a database?  This is outside of my original question so I am happy to award all points for what has been done so far and set this last question up as a new request - but then how can I be sure to get back to you?  Alternatively, can we upgrade this question from say 500 points to 750 points and you answer this last one as part of the same correspondence?
- If so I would I think at the end, "Accept as Solution" your answer Posted on 2013-12-19 at 11:34:00.  Let me know - and thank you.  Geoff
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39770658
Geoff,

This is outside of my original question so I am happy to award all points for what has been done so far and set this last question up as a new request - but then how can I be sure to get back to you?

Another question is appropriate.  Create the question and then either add a post here with a link to the new question or email me a link to the new question.  My email address is in my profile.  I'll take a look at the question as soon as I can.  If you mention in the new question that this is a follow-on to a question I worked with you on, then most of the experts will leave the question alone.  However, if someone gets to it before I do and gives a solid answer, then that's fine too.  The important thing is answering the question, not who answers it.

Alternatively, can we upgrade this question from say 500 points to 750 points and you answer this last one as part of the same correspondence?

There is no way to raise the point value of a question beyond 500 points.
0
 

Author Comment

by:Lomed_Geoff
ID: 39771809
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
If you don't know how to downgrade, my instructions below should be helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now