Outlook VBA Script Moving Text From Custom Form to Message Body

Question

I have never used Outlook VBA (2010), but my manager tasked me to create a user form that sends IT requests and IS requests. I have the custom form created, where I gather all my text fields and print the text to a single text box.

This action is all defined within Sub CommandButton1_Click(), which ends with Send(). Within the sub, I have something like this for all text boxes:

 Set Sj =Item.GetInspector.ModifiedFormPages("P.2").Controls("Subject_Text")
 Set YNbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("YNBox")
 Set Rbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("ReasonBox")

What I want to know is how do I take the texts within "P.2" and paste it into the Message area of "Message" Page?

Here is the code snippet for the printing out to a single text box:

FinalBox.Text = "Subject: " & Sj.Text & vbCrLf & _
vbCrLf & "Can work around the issue?: " & YNbox.Text & _
vbCrLf & "Reason For Ticketing: " & Rbox.Text & _
vbCrLf & "Department: " & Dbox.Text & _
vbCrLf & "Impact: " & Ibox.Text & _
vbCrLf & "Urgency: " & Ubox.Text & _
vbCrLf & "System/Machine Number: " & Mbox.Text & _
vbCrLf & "Was trying to accomplish: " & Abox.Text & _
vbCrLf & "Has it occured before?: " & Bbox.Text & _
vbCrLf & "First Noticed: " & Tbox.Text & _
vbCrLf & "Others affected by the issue: " & Affbox.Text & _
vbCrLf & "Additonal Comments: " & Addbox.Text

So, how do I take this and append it to the actual message field in Message page?

.

Thank you very much!!

P.S. I've been having issues with MailItem.body and whenever I create an object, for example:

Dim objMsg As Object,

I get an error that says "Expected end of statement"... I understand VB and VBA is different, but I didn't think it'd be giving me this much headache.

Edit:

Hello dbMitch and Tony Dallimore, thanks for helping me clarify my questions. Like I mentioned, I am a mere beginner when it comes to VBA, and I Just wanted to

`Sub Commandbutton
 Set Sj = Item.GetInspector.ModifiedFormPages("P.2").Controls("Subject_Text")
 Set YNbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("YNBox")
 Set Rbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("ReasonBox")
 Set Dbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("DepartmentDropbox")
 Set Mbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("MachineBox")
 Set Ibox = Item.GetInspector.ModifiedFormPages("P.2").Controls("ImpactBox")
 Set Ubox = Item.GetInspector.ModifiedFormPages("P.2").Controls("UrgencyBox")
 Set Abox = Item.GetInspector.ModifiedFormPages("P.2").Controls("AccomplishBox")
 Set Bbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("BeforeText")
 Set Tbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("Timebox")
 Set Affbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("AffectedBox")
 Set Addbox = Item.GetInspector.ModifiedFormPages("P.2").Controls("AdditionalBox")
 Set Tbox8 = Item.GetInspector.ModifiedFormPages("P.2").Controls("TextBox8")
 Set MESBOX = Item.GetInspector.ModifiedFormPages("Message").Controls("Message")

 Tbox8.Text = "Subject: " & Sj.Text & vbCrLf & _
    vbCrLf & "Can work around the issue?: " & YNbox.Text & _
    vbCrLf & "Reason For Ticketing: " & Rbox.Text & _
    vbCrLf & "Department: " & Dbox.Text & _
    vbCrLf & "Impact: " & Ibox.Text & _
    vbCrLf & "Urgency: " & Ubox.Text & _
    vbCrLf & "System/Machine Number: " & Mbox.Text & _
    vbCrLf & "Was trying to accomplish: " & Abox.Text & _
    vbCrLf & "Has it occured before?: " & Bbox.Text & _
    vbCrLf & "First Noticed: " & Tbox.Text & _
    vbCrLf & "Others affected by the issue: " & Affbox.Text & _
    vbCrLf & "Additonal Comments: " & Addbox.Text
 Send
End Sub
`

Sub I found online that is suppose to take an item and append the text onto message body. There seems to be an error every time I try to declare the object's type (Ex. ____ As _____). I was not sure how to modify this to make it fit with my code, but the error is thrown at Dim objItem As Object that says

Expected end of statement

Sub TestAppendText()
Dim objItem As Object
Dim thisMail As Outlook.MailItem
'On Error Resume Next

Set objItem = Application.ActiveExplorer.Selection(1)
If Not objItem Is Nothing Then
    If objItem.Class = olMail Then
        Set thisMail = objItem
        Call AppendTextToMessage(thisMail, "Some text added at " & Now())
    End If
End If

Set objItem = Nothing
Set thisMail = Nothing
End Sub

Sub AppendTextToMessage(ByVal objMail As Outlook.MailItem, ByVal strText As String)
    Dim objCDO As MAPI.Session
    Dim objMsg As MAPI.Message
    Dim objField As MAPI.Field

    Set objCDO = CreateObject("MAPI.Session")
    objCDO.Logon "", "", False, False

    If Not objMail.EntryID = "" Then
        Set objMsg = objCDO.GetMessage(objMail.EntryID, _
                                   objMail.Parent.StoreID)
        objMsg.Text = objMsg.Text & vbCrLf & strText
        objMsg.Update True, True
        Set objField = objMsg.Fields(CdoPR_RTF_COMPRESSED)
        If Not objField Is Nothing Then
            objField.Delete
            objMsg.Update True, True
        End If
        Set objField = Nothing
        Set objField = objMsg.Fields(CdoPR_RTF_SYNC_BODY_COUNT)
        If Not objField Is Nothing Then
            objField.Delete
            objMsg.Update True, True
        End If
     Else
        strMsg = "You must save the item before you add text. " & _
                 "Do you want to save the item now?"
        intAns = MsgBox(strMsg, vbYesNo + vbDefaultButton1, "Append Text to Message")
        If intAns = vbYes Then
            Call AppendTextToMessage(objMail, strText)
        Else
            Exit Sub
        End If
    End If

    Set objMsg = Nothing
    objCDO.Logoff
    Set objCDO = Nothing
End Sub

I've also tried using a CDO object, but it gives me an error that says

ActiveX component can't create object: 'CDONTS.NewMail'

Set Item1 = Item.MessageClass
Set objCDONTS = CreateObject ("CDONTS.NewMail")
objCDONTS.Body = Tbox8.Text

Item1.Message = "Hi"

I apologize if this seems very simple to others.. I just have 0 experience with VBA/VBScript and Outlook designs. That's why I'm here, to learn!

Answer 1

Please do not ask a question like this again. I do understand the difficulty of telling one’s manager that you lack the background to tackle a task. The typical response seems to be: “It is easy: just look it up on the internet.” In one sense this is true. There is nothing in the answer below that you could not find in other answers. What you lack is the background to identify the features you require and to fit them together to create the solution. VBA is not a difficult language but it has many significant differences from other languages that will confuse an experienced programmer new to VBA. The Outlook Object model takes time to master.

There are many online Excel VBA and Outlook VBA tutorials. I think the Excel VBA tutorials are better. If you are asked to perform a similar task again, you must insist on having the time to study first. These answers of mine might help:

• Update excel sheet based on outlook mail

• How to copy Outlook mail message into excel using VBA or Macros

I wanted to create a complete solution since I did not believe another snippet would help. You need: “This works. Adjust it step by step to your exact requirement.”

I am not familiar with custom forms and can find nothing that says what advantage they offer over user forms which work with all versions of VBA. I did not want to take the time to study custom forms particularly as I have code for user forms that I can easily adjust to your requirement. If your custom form is working to your satisfaction, replace that part of my solution.

There are five parts to my solution:

1. The user form.
2. The first part of the subroutine SendTicket() which loads the user form and calls it to get data from the user.
3. The subroutine UserForm_Initialize() within the user form’s code which builds the form from parameters supplied by SendTicket().
4. The subroutine CommandButton1_Click() which performs minimal validation of the user’s data and stores it for SendTicket().
5. The second part of the subroutine SendTicket() which builds the email from the user’s data and sends it to the IT department.

1. The user form

I inserted a new user form, adjusted its size and added controls. If you do not know how to do this then look at one of the tutorials that introduce user forms.

If I add a label control to a user form and name it X, I can, for example:

• Move it by changing X.Top.
• Display some text by changing X.Caption.

The user form includes a Collection named Controls. A collection is what most languages call an unsorted list. Within Controls there will be an entry for every control (label, text box, command button, etc.) on the form. If the label X is the first control on the user form, I can access its properties as Controls(0).Top and Controls(0).Caption. This means I can have code specific to label X that reference it by name or I can have general code that handles all or selected labels by their position within Controls. For your requirement, I believed general code would be easier so that is what I have provided.

I created a label control and a text box control. I left the default names but changed some properties:

              LABEL                  TEXT BOX
* Name        Label1                 TextBox1
  Caption     Prompt/Name
  Font        Tahoma 10              Tahoma 10
  Height      12                     18
  Left        12                     230
* Multiline                          True
* Scrollbars                         2 - frmScrollBarsVertical 
  TextAlign   3 – frmTextAlighRight  1 – frmTextAlignLegt
  Text/Value                         abcdefghijklmnopqrstuvwyz
* Visible     False                  False
  Width       200                    400
* Word wrap   True                   True

Properties starting “*” are important. The others are because I like the way they affect the form’s appearance.

In the UK, “Label1” and “TextBox1” are the default names for the first label and textbox. With Excel, default names vary with the local language; I do not know if this is true for Outlook. Within the code, it tells you how to change the default names if necessary.

My code assumes the captions for the labels will fit on a single line but the text entered into the text boxes may require several lines.

Having created my standard label and text box, I selected them, copied them, pasted them and then moved the copies so they were exactly under the first pair. I repeated this with the four controls then the eight controls and finally the sixteen controls. I ended with a column of sixteen labels and a column of sixteen text boxes. I was not concerned with the vertical position of the controls; I handle that later. If either column is not perfectly aligned, you can select a column and set the Left property for the entire group. I have created forms with hundreds of controls with acceptable performance so add more if you wish.

I created a command button. I lined it up with the text boxes but made the font larger. I retained the default name and caption.

By creating the command button last, the controls are in the correct tab order. The form opens with the cursor in the first (top) field and each tab takes the cursor to the next field and then to the button.

Finally, I set the width of the form so it was a little wider than the controls. I set the height so it was close to the screen height which on my laptop is about 560. It is possible to get the screen height from the system but that is beyond the scope of this answer. I set the caption to “Report issue”. I named it P2 which the closest I can get to your name (P.2 is an invalid name for a user form). The result was:

** 2. Part 1 of subroutine SendTicket()**

A macro cannot directly send parameters to a user form or receive values back. It must use global variables. P2Params is the global I use to pass data to the user form and I use P2Values to pass data back.

P2Params = Array("Subject", …) is the statement that loads P2Paramswith parameters. The first three parameters are "Subject", 18, True which specify the first text box. The prompt/name is “Subject”, the height is 18 and it is mandatory. Each further set of three parameters defines another text box.

I could have defined all this information within the form but getting a form like this looking the way you want can be very fussy particularly if you change your mind about the height of a text box so all the lower ones have to be moved down. With this approach you can change a text box’s height or change the sequence or add a new field with no hassle at all. Note: height defines the height allocated to the control. On my laptop and with my choice of font, 54 is enough for four lines. If the user types a fifth line, the scroll bars will appear against the relevant control so the user can see all the lines. You specify height of each text box based on some average or typical ticket but it does not matter if the user wants to enter more text than you expected.

Load P2 loads the form into memory and calls UserForm_Initialize() to initialise the form. .Show vbModal passes control to the form. Control is not returned until the user does something to return control. In this case, clicking the command button returns control providing the entered values pass the validation code.

3. The subroutine UserForm_Initialize()

I do not intend to say too much about this sub-routine. Comments within the code fully explain what the code does and the image below shows the result:

If you look down the parameters in P2Params. You can see where this layout came from. The beauty of this approach is that with a different set of parameters, a very different form can be produced. A requirement to import a list of text values is not uncommon so I have used variations of this code before and will again.

4. The subroutine CommandButton1_Click()

The user can enter values into the text boxes in required. Once they are correct, the user clicks the command button which was been re-captioned “Send” by the first part of subroutine SendTicket().

This routine validates that all mandatory fields have a value. I have implemented permitted ranges and other validation but this is sufficient for your requirement. If the field values are acceptable, the routine loads the entered values into array P2Values. As I said, only by storing values in a global variable can a user form return values to the caller.

5. The second part of the subroutine SendTicket()

This code takes the values from P2Values builds the email and sends it. I have send emails to an experimental Gmail account. You will need to replace the recipient with the address of your IT Department.

Summary

There is a lot here for you to think about. Work through it slower and come back with questions if necessary

SendTicket()

Option Explicit

  Public Type FieldDtl
    CtrlLabel As Long
    CtrlTextBox As Long
    Height As Long
    Mandatory As Boolean
    Prompt As String
  End Type

Public P2Params As Variant
Public P2Values() As String
Sub SendTicket()

  Dim InxFld As Long
  Dim InxPrm As Long
  Dim MailItemCrnt As MailItem

  P2Params = Array("Subject", 18, True, _
                   "Can you work around the issue?", 18, True, _
                   "Reason For Ticketing", 30, True, _
                   "Department", 18, False, _
                   "Impact", 18, True, _
                   "Urgency", 18, True, _
                   "System/Machine Number", 18, True, _
                   "Was trying to accomplish", 54, True, _
                   "Has it occured before?", 18, True, _
                   "First Noticed", 18, False, _
                   "Others affected by the issue", 42, True, _
                   "Additional Comments", 54, True)

  ' Used to test total height of control exceeding height of screen
  'P2Params = Array("Subject", 50, True, _
  '                 "Can you work around the issue?", 50, True, _
  '                 "Reason For Ticketing", 50, True, _
  '                 "Department", 50, False, _
  '                 "Impact", 50, True, _
  '                 "Urgency", 50, True, _
  '                 "System/Machine Number", 50, True, _
  '                 "Was trying to accomplish", 54, True, _
  '                 "Has it occured before?", 50, True, _
  '                 "First Noticed", 50, False, _
  '                 "Others affected by the issue", 54, True, _
  '                 "Additional Comments", 54, True)

  Load P2
  With P2
    .CommandButton1.Caption = "Send"
    .Show vbModal
  End With

 ' The bounds of P2Values are 1 to number of fields
 ' The bounds of P2Params could be 1 to NumberOfFields*3 but is almost
 ' certainly 0 to NumberOfFields*3-1

 Set MailItemCrnt = CreateItem(olMailItem)
 With MailItemCrnt
   .BodyFormat = olFormatPlain
   .Recipients.Add "AbbeyRuins33@gmail.com"
   .Subject = P2Values(1)           ' Assumes subject is first field
   .Body = P2Params(LBound(P2Params) + 3) & ": " & P2Values(2)
   InxFld = 3
   For InxPrm = LBound(P2Params) + 6 To UBound(P2Params) Step 3
     .Body = .Body & vbCrLf & P2Params(InxPrm) & ": " & P2Values(InxFld)
     InxFld = InxFld + 1
   Next
   .Display
   ' .Send
 End With
 Set MailItemCrnt = Nothing

End Sub 

Code for user form

Option Explicit

' In UK, the default name for a label is "LabelN" and the default name for a text box
' is "TextBoxN".  In case the default name is different is non-English speaking
' countries, I use constants for these values. Change the value of these constants
' as necessary.
Const NameLabel As String = "Label"
Const NameTextBox As String = "TextBox"

' This code assumes there are N labels named NameLabel & 1 to NameLabel & N and
' N text boxes named NameTextBox & 1 to NameTextBox & N.  NameLabelX is used to
' label TextBoxX which is used to obtain the Xth value from the user.

' User type FieldDtl is defined in the SendTicket module
Dim Fields() As FieldDtl
Private Sub CommandButton1_Click()

  Dim ErrMsg As String
  Dim InxFld As Long

  ' Check values have been entered for mandatory fields
  ErrMsg = ""
  For InxFld = 1 To UBound(Fields)
    If Fields(InxFld).Mandatory And Controls(Fields(InxFld).CtrlTextBox).Text = "" Then
      If ErrMsg <> "" Then
        ErrMsg = ErrMsg & vbLf
      End If
      ErrMsg = ErrMsg & "Please enter a value for " & Fields(InxFld).Prompt
    End If
  Next

  ' No value entered for one or more mandatory fields
  If ErrMsg <> "" Then
    Call MsgBox(ErrMsg, vbOKOnly)
    Exit Sub
  End If

  ' Save values for caller
  ReDim P2Values(1 To UBound(Fields))
  For InxFld = 1 To UBound(Fields)
    P2Values(InxFld) = Controls(Fields(InxFld).CtrlTextBox).Text
  Next

  Unload Me

End Sub
Private Sub UserForm_Initialize()

  Const GapBetweenCtrls As Long = 5

  Dim InxCtrl As Long
  Dim InxFld As Long
  Dim InxPrm As Long
  Dim NumFields As Long
  Dim NumParams As Long
  Dim TopNext As Long

  ' Note: LBound(P2Params) can be zero or one but will almost certainly be zero.
  ' This code allows for either possibility.
  NumParams = UBound(P2Params) - LBound(P2Params) + 1
  Debug.Assert NumParams Mod 3 = 0

  NumFields = NumParams / 3
  ReDim Fields(1 To NumFields)

  ' Import values from P2Params
  ' P2Params must contain 3N paramerers where N is the number of values
  ' to be obtained from the user.  The three values are:
  '   Prompt/Name for value.
  '   Height of value (so control can be sized for multi-line values).
  '   Mandatory? (True is a value must be entered)
  InxFld = 1
  For InxPrm = LBound(P2Params) To UBound(P2Params) Step 3
    Fields(InxFld).Prompt = P2Params(InxPrm)
    Fields(InxFld).Height = P2Params(InxPrm + 1)
    Fields(InxFld).Mandatory = P2Params(InxPrm + 2)
    InxFld = InxFld + 1
  Next

  ' Controls can be accessed by name (for example Label1.Caption) or
  ' by position within the collection Controls (for example
  ' Controls(1).Caption).  Add control numbers to Fields().
  For InxCtrl = 0 To Controls.Count - 1
    If Left$(Controls(InxCtrl).Name, Len(NameLabel)) = NameLabel Then
      ' Extract number at end of name
      InxFld = CLng(Mid(Controls(InxCtrl).Name, Len(NameLabel) + 1))
      If InxFld <= NumFields Then
        ' This control will be used
        Fields(InxFld).CtrlLabel = InxCtrl
      End If
    ElseIf Left$(Controls(InxCtrl).Name, Len(NameTextBox)) = NameTextBox Then
      InxFld = CLng(Mid(Controls(InxCtrl).Name, Len(NameTextBox) + 1))
      If InxFld <= NumFields Then
        ' This control will be used
        Fields(InxFld).CtrlTextBox = InxCtrl
      End If
    End If
  Next

  '  For InxFld = 1 To NumFields
  '    Debug.Print Fields(InxFld).Name & " " & Fields(InxFld).Height & " " & _
  '                Fields(InxFld).Mandatory & " " & Fields(InxFld).CtrlLabel & _
  '                " " & Fields(InxFld).CtrlTextBox
  '  Next

  ' Now have information necessary to build form.

  ' This code assumes/relies on:
  '   * All  properties of the textbox controls being correct
  '     except for Top and Height.
  '   * All  properties of the label controls being correct except for Top.
  '   * The Height of the label controls being less than the Height of any
  '     Textbox control.
  '   * The Visible property of the label and textbox controls being false.
  '   * The Multiline property of the textbox controls being true
  '   * The Scrollbars property of the textbox controls being
  '     2 = frmScrollBarsVertical
  '   * The Width of the label property being such that all captions fit.

  TopNext = GapBetweenCtrls

  For InxFld = 1 To NumFields
    With Controls(Fields(InxFld).CtrlLabel)
      .Top = TopNext
      .Caption = Fields(InxFld).Prompt
      .Visible = True
    End With
    With Controls(Fields(InxFld).CtrlTextBox)
      .Top = TopNext
      .Height = Fields(InxFld).Height
      .Text = ""
      .Visible = True
    End With
    TopNext = TopNext + Fields(InxFld).Height + GapBetweenCtrls
  Next

  With CommandButton1
    .Top = TopNext
    TopNext = TopNext + .Height + GapBetweenCtrls
  End With

  ' Set scroll height so if total height of controls exceeds height
  ' of form, user can scroll from top to bottom.
  ScrollHeight = TopNext

End Sub