Transfer Outlook TextBox Text Into Email Body

[cdc99999]

Hi all,

I have what I thought was a simple idea but I can' t find any info or get any help, seems like it's complicated if feasible at all. Trying my luck here

I would like for users to simply fill out fields (form maybe?) and the said fields (TextBoxes maybe?) get inserted into the body of an email template. This would avoid users having to copy and paste data into an email template and then have to adjust the font, formatting, etc.

Any way to achieve this? If so, how? Is via an Outlook form a possibility, where it would transfer TextBox data to specific places inside the body of an email?

Thanks in advance

[gmayor]

This is fairly straightforward using a pre-prepared template a simple userform and a macro to create the message. Here there are four fields, but you can have more or fewer as required and the labels simply reflect the content of the text fields.


The code for the form is:

Option Explicit

Private Sub CommandButton1_Click()
    Hide
    Tag = 1
End Sub

Private Sub CommandButton2_Click()
    Hide
    Tag = 2
End Sub

The code works in conjunction with an Outlook template here 'C:\Path\TemplateName.oft' which for the purpose of the exercise looks like

Note there are four texts bounded by ##. These are texts that the macro following will replace, and equate to the userform text fields.

The main code goes in a new module in the Outlook project. The line
Const sFindText As String = "#Name#|#Desc#|#Acres#|#Value#"
is a list of the fields/texts separated by the pipe character '|'. Obviously these have to match what you put in your template.

The macro opens the template and replaces the texts with the values entered in the userform then displays the message

Option Explicit

Sub ReplaceFromUserForm()
Dim olItem As MailItem
Dim olEmail As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim i As Long, j As Long
Const sFindText As String = "#Name#|#Desc#|#Acres#|#Value#"
Dim sReplaceText As String
Dim vFind As Variant
    With UserForm1
        .Caption = "Complete the fields"
        .CommandButton1.Caption = "Continue"
        .CommandButton2.Caption = "Cancel"
        .Show
        vFind = Split(sFindText, "|")
        Set olItem = CreateItemFromTemplate("C:\Path\TemplateName.oft")
        With olItem
            .BodyFormat = olFormatHTML
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            .Display
            For j = 0 To UBound(vFind)
                Set oRng = wdDoc.Range
                Select Case j
                    Case 0
                        sReplaceText = UserForm1.TextBox1.Text
                    Case 1
                        sReplaceText = UserForm1.TextBox2.Text
                    Case 2
                        sReplaceText = UserForm1.TextBox3.Text
                    Case 3
                        sReplaceText = UserForm1.TextBox4.Text
                    Case Else
                End Select
                With oRng.Find
                    Do While .Execute(findText:=vFind(j))
                        oRng.Text = sReplaceText
                        oRng.collapse 0
                        DoEvents
                    Loop
                End With
            Next j
            .Display
        End With
    End With
    Unload UserForm1
lbl_Exit:
    Exit Sub
End Sub

[cdc99999]

Thanks gmayor, this is great!

I used your data as a reference but created my own template and changed the path in the code.

My form works but the button doesn't. The only way I can get it to work is to:

1) Enter the data in the form (not press any of the buttons)
2) Run the macro from Outlook

Only then does it open my email template with the form fields info in it based on where I placed the various ## references.

Will continue to troubleshoot and hopefully find the fix.

Many thanks, your help is much appreciated

[gmayor]

There is a line missing from the code.
Immediately after

.Show

there should be a line

If .Tag = 0 Then GoTo lbl_Exit

which will allow you to click CommandButton2 to cancel the process.

What 'button'? For running the process, create a button on the ribbon/QAT (Quick Access Toolbar) to run the macro 'ReplaceFromUserForm'
This will open the userform and allow you to enter the data, then the message is created.
You may run into problems with Outlook security - in which case see
http://www.gmayor.com/create_and_employ_a_digital_cert.htm

[cdc99999]

Thank you Sir!

The button I was referring to was the button on the form (continue button) which doesn't work, it does not automatically get the template and open it with the data entered.

I amended the code but it still does not work. I cannot use the button and when I activate the macro from Outlook it gives me a VB error “Run-time error ‘13”: Type mismatch.

When I press “Debug: it outlines the following code line in yellow: If .Tag = 0 Then

I’m guessing the new code line needs a minor adjustment?

Cheers


I have amended the code to include the include what you mentioned, below is what I now have:

Option Explicit
Sub ReplaceFromUserForm()
Dim olItem As MailItem
Dim olEmail As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim i As Long, j As Long
Const sFindText As String = "#Name#|#Desc#|#Acres#|#Value#"
Dim sReplaceText As String
Dim vFind As Variant
With UserForm1
.Caption = "Complete the fields"
.CommandButton1.Caption = "Continue"
.CommandButton2.Caption = "Cancel"
.Show
If .Tag = 0 Then GoTo lbl_Exit
vFind = Split(sFindText, "|")
Set olItem = CreateItemFromTemplate("U:\Operations Centre\Email Templates\form_test.oft")
With olItem
.BodyFormat = olFormatHTML
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
.Display
For j = 0 To UBound(vFind)
Set oRng = wdDoc.Range
Select Case j
Case 0
sReplaceText = UserForm1.TextBox1.Text
Case 1
sReplaceText = UserForm1.TextBox2.Text
Case 2
sReplaceText = UserForm1.TextBox3.Text
Case 3
sReplaceText = UserForm1.TextBox4.Text
Case Else
End Select
With oRng.Find
Do While .Execute(findText:=vFind(j))
oRng.Text = sReplaceText
oRng.collapse 0
DoEvents
Loop
End With
Next j
.Display
End With
End With
Unload UserForm1
lbl_Exit:
Exit Sub
End Sub

[gmayor]

I don't know what I was thinking this morning when I answered the question originally The code for Userform1 should be

Option Explicit

Private Sub CommandButton1_Click()
    Hide
    Tag = 1
End Sub

Private Sub CommandButton2_Click()
    Hide
    Tag = 0
End Sub

i.e. Tag = 0 is the response from the Cancel button and not '2' as I posted. Apart from that it works as described when I substitute my test template path for yours (and this error shouldn't have caused the process to crash, but would have completed the process whichever button you clicked.)

The buttons on the Userform only work as intended if you run the main macro and not the userform code.

[cdc99999]

Thanks again gmayor, appreciate you time and help.

I feel bad posting again, don't mean to be a bother and I understand if there's no further follow up, more than enough was done to help out.

I did try to run the form via the main macro in Outlook only but it still gives me gives me that "Run-time error ‘13”: Type mismatch".

When I click "Debug" in brings me to the code and the line added:
- Commas show the text that the debug highlights in yellow: "If .Tag = 0 Then" GoTo lbl_Exit

Cheers

[gmayor]

That shouldn't happen, but you have shown

"If .Tag = 0 Then" GoTo lbl_Exit

There should be no quotes in that line?

Check VBA > Tools > References and see if you have the following checked.
Obviously if you have a different Office version the office numbered references will be different.

[cdc99999]

Sorry, I wrote "commas" but should have wrote "quotes", the text in quotes is not in my code but what is highlighted in yellow from Debug. My code is as you provided, without quotes.

Will check References as suggested, thanks

[cdc99999]

Verified and added checkmark to "Microsoft Scripting Runtime" but still get the Run-time error ‘13”: Type mismatch.

Thanks again for your help, I will try it again from scratch, hopefully I missed something somewhere.

It's too bad, so damn close to it working... arghh!

[gmayor]

You can download the test files I used from http://www.gmayor.com/Forum/email_userform.zip

Extract the files from the zip. The template goes in your template folder. Import the userform and macro code to your VBA editor. It will create a new userform (frmExample) and add a module with code that works with the added userform. Delete your existing code and and it should work.

Reboot the PC before testing again.

[cdc99999]

ROCK ON!

Works like a charm, many thanks!