CODEWITHSUNDEEP
vbatemplatesuserform
folkstorm
folkstorm

Reputation: 15

Apply Outlook userform values to email template

I want to select specific values in a userform and based on selection reformat an email template and replace text or just open an empty email and send keys/text to email title, body etc. (values like name, surname etc.)

The userform.

enter image description here

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub TextBox3_Change()

End Sub

Private Sub UserForm_Initialize()
With ComboBox1
 .AddItem "Form1"
 .AddItem "Form2"

End With

With ComboBox2
 .AddItem "Mr"
 .AddItem "Miss"
 .AddItem "Mrs"
 .AddItem "Ms"

End With

  With ComboBox3
 .AddItem "You"
 .AddItem "He"
 .AddItem "She"

End With

End Sub

Execution button code.

Private Sub CommandButton1_Click()

If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or _
  ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Then
    MsgBox ("Fill in all Boxes")

End If
End Sub

Sub template()

Dim myItem As Outlook.MailItem
Dim strContact As String
Dim strHTML As String

Dim typeofapplication As String
Dim title As String
Dim name As String
Dim surename As String
Dim expierydate As String
Dim gender As String

typeofapplication = ComboBox1.Value
title = ComBox2.Value
name = TextBox1_Change.Value
surename = TextBox2_Change.Value
expierydate = TextBox3_Change.Value
gender = ComBox3.Value

Set myItem = Application.CreateItemFromTemplate("C:\test.oft")
strHTML = myItem.HTMLBody

myItem.Display
End Sub

Code works till "Fill in all Boxes". After pressing the OK button it stops on myItem.Display.

Opening the template works if I do not use the userform.

Upvotes: 0

Views: 2109

Answers (1)

Tehscript
Tehscript

Reputation: 2556

As I stated in my comment, you should call template sub in your CommandButton1_Click Sub. And also you should exit sub when your your condition is true because you wouldn't want to run your code when any of your textbox or combobox is empty. It should look like this:

Private Sub CommandButton1_Click()
If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Then
    MsgBox ("Fill in all Boxes")
    Exit Sub
End If
    template
End Sub

Secondly, when something goes wrong, you will be informed about it with an error message, usually with an error number and some description. I had to try your code to get what this error is.

The error says: "Outlook can't do this because a dialog box is open. Please close it and try again." If you had indicated this in your question, you would have an instant answer that says "Unload Me". Next time when you post a question, you should indicate the error as well.

You cannot make it run because you first need to close the userform, in order to open the template email. So insert Unload Me in your code like this:

Set myItem = Application.CreateItemFromTemplate("C:\test.oft")
strHTML = myItem.HTMLBody

Unload Me
myItem.Display
End Sub

Upvotes: 1

Related Questions