?
Solved

Custom Outlook Form - Generate email notification from task request

Posted on 2014-01-31
7
Medium Priority
?
723 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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

764 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