Solved

Custom Outlook Form - Generate email notification from task request

Posted on 2014-01-31
7
683 Views
Last Modified: 2014-02-03
Hello,

To take a very complicated process and simplify it --

I've designed a custom outlook form for use as a task request. Is there a way to apply a button of some sort that when pushed will pull fields from the form and generate an email to be sent to a separate distribution list.
Ex. Group A will apply new employee information to the custom form and send it to Group B to enter into a database. Group A must then write up an email to notify Group C about the employee using only a few data points from the form (Hire date, name, job title, department, supervisor). I would like to eliminate this manual step so that Group A can press a button and an email will populate that states:
New hire John Doe will start on February 2, 2014 as a Accountant in Accounting reporting to Susan Smith.
0
Comment
Question by:etdowdle
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 39825035
Hi, etdowdle.

Yes, that's possible.  What are the names of the properties the controls are bound to?
0
 

Author Comment

by:etdowdle
ID: 39825109
The fields are:

Name: Employee Name Property: Employee Name
Name: Effective Date Property: Effective Date
Name: Business Title Property: Business Title
Name: Department Property: Dept ID
Name: Supervisor Property: Supervisor

So email text would be:

New hire [Employee Name] will start on [Effective Date] as a [Business Title] in [Dept ID] reporting to [Supervisor].
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 39825244
Here's my solution for doing this.  I've tested it on my computer and it works the way you described.  To use this

1.  Open your custom form in the form editor
2.  Add a command button to the form
3.  Click the View Code button on the ribbon
4.  Copy the code below and paste it into the editor
5.  Change "CommandButton1" in the subroutine name to whatever your button is named.
6.  Close the editor
7.  Re-publish your form

Sub CommandButton1_Click()
    Dim varName, varDate, varTitle, varID, varSupervisor, olkApp, olkMsg
    varName = Item.UserProperties.Item("Employee Name").Value
    varDate = Item.UserProperties.Item("Effective Date").Value
    varTitle = Item.UserProperties.Item("Business Title").Value
    varID = Item.UserProperties.Item("Dept ID").Value
    varSupervisor = Item.UserProperties.Item("Supervisor").Value
    Set olkApp = GetObject(,"Outlook.Application")
    Set olkMsg = olkApp.CreateItem(0)
    olkMsg.HTMLBody = "New Hire " & varName & " will start on " & varDate & " as a " & varTitle & " in " & varID & " reporting to " & varSupervisor
    olkMsg.Display
    Set olkMsg = Nothing
    Set olkApp = Nothing
End Sub

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:etdowdle
ID: 39825443
This is great! Thank you

Two sub questions:

Can I also auto-populate the subject line?
Subject line: New Hire [Employee Name] [Effective Date]

And can I set the font type and size for the body of the email?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39826432
You're welcome.

Yes, the code can populate the subject line.  I've modified the code to do that.

Sub CommandButton1_Click()
    Dim varName, varDate, varTitle, varID, varSupervisor, olkApp, olkMsg
    varName = Item.UserProperties.Item("Employee Name").Value
    varDate = Item.UserProperties.Item("Effective Date").Value
    varTitle = Item.UserProperties.Item("Business Title").Value
    varID = Item.UserProperties.Item("Dept ID").Value
    varSupervisor = Item.UserProperties.Item("Supervisor").Value
    Set olkApp = GetObject(,"Outlook.Application")
    Set olkMsg = olkApp.CreateItem(0)
    olkMsg.Subject = "New Hire " & varName & " " & varDate
    olkMsg.HTMLBody = "New Hire " & varName & " will start on " & varDate & " as a " & varTitle & " in " & varID & " reporting to " & varSupervisor
    olkMsg.Display
    Set olkMsg = Nothing
    Set olkApp = Nothing
End Sub

Open in new window


You can also set the font type and size.  You'll do that through the use of CSS.  To set that for the body, you'd modify line 11 of the code to something like this

        olkMsg.HTMLBody = "<span style=""font-family: Tahoma; font-size: 12pt;"">New Hire " & varName & " will start on " & varDate & " as a " & varTitle & " in " & varID & " reporting to " & varSupervisor & "</span>"

Open in new window


This would set the body to 12pt Tahoma.
0
 

Author Comment

by:etdowdle
ID: 39830807
Thanks again!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39831109
You're welcome!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

832 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