Solved

Custom Outlook Form - Generate email notification from task request

Posted on 2014-01-31
7
673 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Resolve DNS query failed errors for Exchange
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

762 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

24 Experts available now in Live!

Get 1:1 Help Now