Solved

Custom Outlook Form - Generate email notification from task request

Posted on 2014-01-31
7
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

695 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