Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Custom Outlook Form - Generate email notification from task request

Posted on 2014-01-31
7
Medium Priority
?
733 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Mailbox Overload?
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
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…
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…

604 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