Need help with combo boxes in messages!

Status
Not open for further replies.

mixdkid

Member
Outlook version
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 :(
 
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
 
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 :)
 
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
 
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!!!!!!
 
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*
 
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.

 
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.
 
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.
 
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
 
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.
Similar threads
Thread starter Title Forum Replies Date
S Custom Contact card - need help creating one Outlook VBA and Custom Forms 1
D Need help with MS Authenticator Using Outlook 4
I Outlook for Mac 2019 using on desktop and laptop IMAP on both need help with folders Using Outlook 1
FryW Need help modifying a VBA script for in coming emails to auto set custom reminder time Outlook VBA and Custom Forms 0
S.Champ Please help? I've imported a random workcalendar I dont even know who's. Can I undo it? and then I need to re-sync the google one again. Its a mess:( Using Outlook 2
L Need help modifying a VBA script for emails stuck in Outbox Outlook VBA and Custom Forms 6
Marc2019 Need help please! Cannot Setup my outlook email account on my Mac Outlook 2011 Using Outlook.com accounts in Outlook 2
C need help setting up outlook first time Using Outlook 1
N Need help syncing contacts to iPhone X Using Outlook 8
S Error using AddressEntry.GetContact - need help Outlook VBA and Custom Forms 2
E Outlook 2010 need help with rules Using Outlook 0
B Need Help - Willing to pay Outlook VBA and Custom Forms 10
J Need Help with Contacts VBA Outlook VBA and Custom Forms 1
H Need help setting up GetFolderPath-Makro with Vodafone IMAP Mail-Account Outlook VBA and Custom Forms 0
T Need help with finding/updating task Outlook VBA and Custom Forms 1
Denis Hi everyone!!! Need some help with Excel to Exchange Calendar??PLEASE>>> Using Outlook 1
E Need Help on Setting up a repeated Reminder on Task with Reoccurence Every Year Using Outlook 6
B Need Help Creating Email Based on Subject w Address in Body Outlook VBA and Custom Forms 1
G Need help configuring a PRF for Outlook 2010 and 2013 Using Outlook 0
K Excel Import Option: Need Help Updating Existing Records BCM (Business Contact Manager) 0
K Need help concerning emails linking to BCM. BCM (Business Contact Manager) 0
J Need help with installation of BCM in Outlook2013 BCM (Business Contact Manager) 5
F Outlook 2010 - Need help with a custom field. Using Outlook 3
D Need Help with Script. Keep getting Runtime Error 438 BCM (Business Contact Manager) 4
R Need help getting BCM to work with my Outlook 2014 Using Outlook 4
L Need help getting calendar/contacts/tasks/notes back! Using Outlook 3
R [VBA] complicated(?) outlook events - need help with code Using Outlook 15
P Need help/Gmail accounts show separately/Outlook 2013 Using Outlook 0
J Need Help Extracting Email Content into Excel Specifically the Attachment Name Using Outlook 3
A Need Help with BCM2010 Sharing on outlook 2013 Scenario! BCM (Business Contact Manager) 1
C Need Help with Simple Code Correction Using Outlook 11
W I Need help with vb.net and outlook 2010 Using Outlook 2
A My move to MS is not going too well: Need Help !!! Using Outlook 14
D Need help understanding which pst-file to use! Using Outlook 0
U Need a help please Outlook 2007 crashing Using Outlook 2
Z Need Help Outlook 2007 inbox and address book view Using Outlook 2
B Need help adding a background color to the compact list items in Month view. Using Outlook 1
B Need help adding an image next to the Month Name in the header. Using Outlook 1
B Ok - a complicated one - but really need help (auto formatting) Using Outlook 9
P To Use BCM or not...I need help BCM (Business Contact Manager) 1
T Need help making RULE to prepend email SUBJECT Using Outlook 3
V Need help on configuring Outook 2003 Rules and Alerts... Using Outlook 2
X business owner in dire need of help outlook is not syncin my contacts! Using Outlook.com accounts in Outlook 2
D Need email verification for international emails, help? BCM (Business Contact Manager) 2
S MAcro - Need help in getting the recipients in to filed / cc field toput in an array Outlook VBA and Custom Forms 4
K Need help Publishing a new Task form! Outlook VBA and Custom Forms 1
A Need help in creating a mass emailing plugin Outlook VBA and Custom Forms 1
T need help w/ sending an e-mail -- outlook 2007 addin Outlook VBA and Custom Forms 2
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 10
P OT: Need website like this one, but for Excel Using Outlook 0

Similar threads

Back
Top