Need help with combo boxes in messages!

Status
Not open for further replies.

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
I need help trying to implement a template for my emails to send to groups at my company. I want to do something like this
sample1.jpg

just focusing on the white part of that Screen Shot. Can I put those kind of combo-boxes into my email message body so that I could just select the appropriate categories and have it sent to my list of contacts?

Body

Date and Time: System Generated.(The computer's time should be time-stamped in this email.)

Event Type: {Outage, Degradation,Information,...}

Location: {Germantown, North Las Vegas, Detroit,...}

Event Status: {Investigation,Outage,Information}



An example of how I'm trying to set this up

Subject: (Status of the problem I'm sending)

Date and Time: 02/01/2013

Event Type: Outage

Location: Germantown

Event Status: Investigation

Please if anyone could help me out I would be so grateful. I'm new to programming and have been looking around for weeks and nothing that could point me in the direction that I should be going for this. It would make my reports much easier if I could get this going. Its such a tedious thing for filling this out every day :(
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You can do it in Outlook using a combination of a macro and userform.

Date: added by VBA

Event type, location, event status would be in a userform that pops up.

Subject: added by vba

I'm in the middle of something right now (taking a break to clear my head) - take a look at Select from a list of subjects before sending a message - Slipstick Systems - it will point you in the right direction. Add a button to the QAT to run the macro.

You'll need something like this in the userform

Code:
Private Sub UserForm_Initialize()
 With ComboBox1
   .AddItem "Event type1"
   .AddItem "Event type2" 
 
End With
 With ComboBox2
   .AddItem "Status1"
   .AddItem "Status2" 
 
End With
 With ComboBox3
   .AddItem "location1"
   .AddItem "location1" 
 
End With 
 
End Sub 
 
Private Sub CommandButton1_Click()
   lstEvent = ComboBox1.ListIndex
  lstStatus = ComboBox2.ListIndex
  lstLoc = ComboBox3.ListIndex
   Unload Me 
 
End Sub
and this is the macro - i don't think i missed anything.

Code:
Public lstEvent As Long 
 
Public lstStatus As Long 
 
Public lstLoc As Long 
 
Public Sub StatusMessage() 
 
Dim oMail As Outlook.MailItem 
 
UserForm1.Show
   Select Case lstEvent
   Case -1
        txtEvent = "some event"
   Case 0
         txtEvent = "different event"
   Case 1
        txtEvent = "bad event"
   End Select 
 
Select Case lstStatus
   Case -1
        txtstatus = "some status"
   Case 0
         txtstatus = "different status"
   Case 1
        txtstatus = "bad status"
   End Select 
 
Select Case lstLoc
   Case -1
        txtloc = "here"
   Case 0
         txtloc = "there"
   Case 1
        txtloc = "everywhere" 
 
End Select 
 
Set oMail = Application.CreateItem(olMailItem) 
 
oMail.body= Now() & vbcrlf  & txtEvent  & vbcrlf & txtstatus  & vbcrlf & txtloc 
 
oMail.subject =  txtstatus 
 
oMail.Display 
 
End Sub
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
Thank you so much for the reply Diane!!!!!!




I will try this out on Monday at work. But I'm highly positive this will work out for me by looking at your other work. Thanks again :)
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
Thanks again Diane!

This worked perfectly! I have another question to ask you or anyone else that could lend a helping hand :), I decided to put in a text box for the user to input for the generated email. But when i try and declare a string for the text box and stick it into my body it doesn't show up:

This is the forum with the Text boxes that I tried to add in bold

Code:
Private Sub UserForm_Initialize()
 With ComboBox2
   .AddItem "Outage"
   .AddItem "Degradation"
   .AddItem "Information"
   .AddItem "Investigation"
   .AddItem "Degredation and Restoration"
   .AddItem "Outage and Restoration"
   .AddItem "Change Window Open"
   .AddItem "Change Window Closed"
   .AddItem "Maintenance Window Open"
   .AddItem "Maintenance Window Closed" 
 
End With
 With ComboBox3
   .AddItem "Germantown"
   .AddItem "North Las Vegas"
   .AddItem "Detroit"
   .AddItem "{Name} Gateway" 
 
End With
 With ComboBox4
   .AddItem "Investigation - Initial"
   .AddItem "Investigation - Update"
   .AddItem "Investigation - Closed"
   .AddItem "Outage - Initial"
   .AddItem "Outage - Update"
   .AddItem "Outage - Resolved" 
 
End With
 With ComboBox5
   .AddItem "Jupiter Management System"
   .AddItem "Spaceway Management System"
   .AddItem "Ordering"
   .AddItem "Billing"
   .AddItem "Satellite Transport"
   .AddItem "Terrestrial Transport"
   .AddItem "Voice Services"
   .AddItem "Disaster Recovery Services"
   .AddItem "High Availability Network"
   .AddItem "Customer Care Portal"
   .AddItem "GTECH NOC 3"
   .AddItem "ROW44 Europe Transport"
   .AddItem "XCI Transport"
   .AddItem "XCI Management"
   .AddItem "Commissioning"
   .AddItem "Activation"
   .AddItem "Jupiter Transport Services"
  
 
End With
 With ComboBox6
   .AddItem "Consumers"
   .AddItem "Enterprise"
   .AddItem "Enteprise and Consumers"
  
 
End With 
 
End Sub 
 
Private Sub CommandButton1_Click()
  lstEvent = ComboBox2.ListIndex
  lstLoc = ComboBox3.ListIndex
  lstStatus = ComboBox4.ListIndex
  lstSer = ComboBox5.ListIndex
  lstCostumer = ComboBox6.ListIndex

[B]Dim strTic, strDes As String
  strTic = TextBox1.Text
  strDes = TextBox2.Text[/B]


   Unload Me 
 
End Sub
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
And this is the Macro part that you created for me with the added text boxes I've tried to add and nothing shows up for the Ticket number or the Description field when I enter it in.

Code:
Public lstNo As Long 
 
Public Sub StatusMessage() 
 
Dim oMail As Outlook.MailItem 
 
Notification.Show 
 
Select Case lstEvent 
 
Case -1 
 
txtEvent = "Outage" 
 
Case 0 
 
txtEvent = "Degradation" 
 
Case 1 
 
txtEvent = "Information" 
 
Case 2 
 
txtEvent = "Investigation" 
 
Case 3 
 
txtEvent = "Degredation and Restoration" 
 
Case 4 
 
txtEvent = "Outage and Restoration" 
 
Case 5 
 
txtEvent = "Change Window Open" 
 
Case 6 
 
txtEvent = "Change Window Closed" 
 
Case 7 
 
txtEvent = "Maintenance Window Open" 
 
Case 8 
 
txtEvent = "Maintenance Window Closed" 
 
End Select 
 
Select Case lstLoc 
 
Case -1 
 
txtLoc = "Germantown" 
 
Case 0 
 
txtLoc = "North Las Vegas" 
 
Case 1 
 
txtLoc = "Detroit" 
 
Case 2 
 
txtLoc = "{Name} Gateway" 
 
End Select 
 
Select Case lstStatus 
 
Case -1 
 
txtStatus = "Investigation (Initial)" 
 
Case 0 
 
txtStatus = "Investigation (Update)" 
 
Case 1 
 
txtStatus = "Investigation (Closed)" 
 
Case 2 
 
txtStatus = "Outage (Initial)" 
 
Case 3 
 
txtStatus = "Outage (Update)" 
 
Case 4 
 
txtStatus = "Outage (Resolved)" 
 
End Select 
 
Select Case lstSer 
 
Case -1 
 
txtSer = "Jupiter Management System" 
 
Case 0 
 
txtSer = "Spaceway Management System" 
 
Case 1 
 
txtSer = "Ordering" 
 
Case 2 
 
txtSer = "Billing" 
 
Case 3 
 
txtSer = "Satellite Transport" 
 
Case 4 
 
txtSer = "Terrestrial Transport" 
 
Case 5 
 
txtSer = "Voice Services" 
 
Case 6 
 
txtSer = "Disaster Recovery Services" 
 
Case 7 
 
txtSer = "High Availability Network" 
 
Case 8 
 
txtSer = "Customer Care Portal" 
 
Case 9 
 
txtSer = "GTECH NOC 3" 
 
Case 10 
 
txtSer = "ROW44 Europe Transport" 
 
Case 11 
 
txtSer = "XCI Transport" 
 
Case 12 
 
txtSer = "XCI Management" 
 
Case 13 
 
txtSer = "Commissioning" 
 
Case 14 
 
txtSer = "Activation" 
 
Case 15 
 
txtSer = "Jupiter Transport Services" 
 
End Select 
 
Select Case lstCustomer 
 
Case -1 
 
txtCustomer = "Consumers" 
 
Case 0 
 
txtCustomer = "Enterprise" 
 
Case 1 
 
txtCustomer = "Enterprise and Consumers" 
 
End Select 
 
Set oMail = Application.CreateItem(olMailItem) 
 
oMail.Body = [B]strTic & strDes &[/B] "Date and Time:" & vbTab & vbTab & vbTab & Now() & vbCrLf & "Event Type:" & vbTab & vbTab & vbTab & txtEvent & vbCrLf & "Location:" & vbTab & vbTab & vbTab & txtLoc & vbCrLf & "Event Status:" & vbTab & vbTab & vbTab & txtStatus & vbCrLf & "Description:" & vbTab & vbTab & vbTab & "*Insert description*" & vbCrLf & "Customers affected:" & vbTab & txtCustomer & vbCrLf & "Service Affected:" & vbTab & vbTab & txtSer & vbCrLf & "ETR:" & vbTab & vbTab & vbTab & vbTab & vbTab & "*Fill in date/time or uknown*" & vbCrLf & "Ticket number:" & vbTab & vbTab & vbTab & "*Fill in ticket number*" & vbCrLf 
 
oMail.Subject = txtStatus & " - " & txtSer 
 
oMail.Display 
 
End Sub 
 
Function GetCurrentItem() As Object 
 
Dim objApp As Outlook.Application 
 
Set objApp = Application 
 
On Error Resume Next 
 
Select Case TypeName(objApp.ActiveWindow) 
 
Case "Explorer" 
 
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1) 
 
Case "Inspector" 
 
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem 
 
End Select 
 
Set objApp = Nothing 
 
End Function
Hopefully I missed something minor and nothing big. Thanks in advance!!!!!!
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Couple of problems:

1) You need to declare the strings publicly so the values are passed from the userform to the macro.

Public lstNo, lstEvent, lstLoc, lstStatus, lstSer, lstCostumer As Long

Public strTic, strDes As String

That should fix your problem.

If you are not selecting a contact (or another outlook item) to pull data from it, you don't need the function at the end.

This was my sample mail - i entered my selection into the first text box - you need another vbcrlf or two (or spaces) in the body string... and the cases need updated. The -1 case is what to use as a default if no selection is made.

Information, Detroit, Investigation - Update, Voice Services, EnterpriseTesting text box 2Date and Time: 2/8/2013 12:53:26 PM

Event Type: Investigation

Location: {Name} Gateway

Event Status: Investigation (Closed)

Description: *Insert description*

Customers affected: Enterprise

Service Affected: Disaster Recovery Services

ETR: *Fill in date/time or uknown*

Ticket number: *Fill in ticket number*

You can either use the same value for -1 and 0 or it like this:

Select Case lstCustomer

Case -1

txtCustomer = "no value selected"

Case 0

txtCustomer = "Consumers"

Case 1

txtCustomer = "Enterprise"

Case 2

txtCustomer = "Enterprise and Consumers"

End Select

with the cases fixed, i got this - i forgot to add spaces in the body code but made sure i added them at the end of the line in the text box.

Outage and Restoration, North Las Vegas, Investigation - Closed, GTECH NOC 3, Enterprise seeing if this works Date and Time: 2/8/2013 1:00:55 PM

Event Type: Outage and Restoration

Location: North Las Vegas

Event Status: Investigation (Closed)

Description: *Insert description*

Customers affected: Consumers

Service Affected: GTECH NOC 3

ETR: *Fill in date/time or uknown*

Ticket number: *Fill in ticket number*
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
Wow your awesome!!

Question, where did you stick "Public lstNo, lstEvent, lstLoc, lstStatus, lstSer, lstCostumer As Long

Public strTic, strDes As String" at? I keep getting an error when I place this at the top of the page of the Macro page.

 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
It goes at the top of the macro - those two lines replace this line:

Public lstNo As Long

What error message do you get? It needs to be at the top of the module.
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
I just redid it and copied and pasted what i posted in here, so everything is the same just replaced the top line in the macro page. Everything is working fine, but now i dont see the the "ticket number" or "description" data i enter.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Did you remove
Dim strTic, strDes As String

from the userform code? (Sorry I forgot to mention that).

And to confirm, you have this line at the top too, after the one that sets all the indexes?

Public strTic, strDes As String
 

mixdkid

Member
Outlook version
Outlook 2007
Email Account
IMAP
Excellent!!!!!!!! You are the best Diane!!!!!!!

You saved me from a pounding headache of confusion and made my boss happy. :D

Thanks!!

Alex Zuniga
 
Status
Not open for further replies.
Top