VBA outlook userform applies changes to email template

[folkstorm]

I want to create a macro in outlook that allow select specific values from userform and based on selection reformat 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.) So far I have done userform.

​
Screenshot from 2017-05-17 20-19-56.jpg

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 "Version1"
     .AddItem "Version2"
 
   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

And 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

When I run sub template itself works fine but when I try to merge it with userform after pressing button it gives me an error no line myItem.Display .

What I have done wrong?
I'm thinking maybe I should create an empty email instead opening template and reformat htmlbody of each selection?
Any ideas?

[gmayor]

Where to start? Based on your message, the code for the userform should be something like

Option Explicit

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "Version1"
        .AddItem "Version2"
    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

Private Sub CommandButton1_Click()
    If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or ComboBox1.Value = "" Or _
    ComboBox2.Value = "" Or ComboBox3.Value = "" Then
        MsgBox ("Fill in all Boxes")
        GoTo lbl_Exit
    End If
    Tag = 1
    Hide
lbl_Exit:
    Exit Sub
End Sub

It would be worth adding a second button to cancel the process e.g.

Private Sub CommandButton2_Click()
    Tag = 0
    Hide
End Sub

The userform would be called from a macro e.g.

Option Explicit

Sub template()

Dim myItem As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object

Dim sType As String
Dim sTitle As String
Dim sName As String
Dim sSurname As String
Dim sExpirydate As String
Dim sGender As String

    With UserForm1
        .Show
        If Not .Tag = 1 Then Exit Sub
        sType = .ComboBox1.Value
        sTitle = .ComboBox2.Value
        sName = .TextBox1.Text
        sSurname = .TextBox2.Text
        sExpirydate = .TextBox3.Text
        sGender = .ComboBox3.Value

        Set myItem = CreateItem(olMailItem)
        With myItem
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
            oRng.Text = sTitle & Chr(32) & sName & Chr(32) & sSurname & vbCr & vbCr
            oRng.collapse 0
            oRng.Text = "The expiry date for " & sType & " is " & sExpirydate
            .Display
        End With
    End With
    Unload UserForm1

lbl_Exit:
    Set myItem = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

However we know nothing about your template, so cannot advise on how to fill it in. The above example creates a new message and writes some values and text to the start of the message, which will almost certainly not be what you require, however if you know about Word VBA ranges, you should be able to work it out.

If you are intended letting your clients have access to your userform, may I suggest that you address the spelling of the labels.

[folkstorm]

Thank you for your suggestions. I will modify my code.
Template is quite simple. It looks like:

Dear (ComboBox2) (title) (TextBox2_Change.Value) (surname)

I write to inform you that [ your Company] on date (TextBox3_Change.Value)..................

email signature

Just want to link userform answers to text in template and replace it.
It's quite annoying if you have to send same email 30 times a day and manually replace all details.


I might use template or just create specific text for specific answers.
example

if selected miss - "send keys/text" Dear Miss
if selected ms = "send keys/text" Dear Ms etc.

I'm sure you will get the idea.

[gmayor]

In the main code the following will give you the result you appear to require. Replace

oRng.Text = sTitle & Chr(32) & sName & Chr(32) & sSurname & vbCr & vbCr 
oRng.collapse 0 
oRng.Text = "The expiry date for " & sType & " is " & sExpirydate

with

oRng.Text = "Dear " & sTitle & Chr(32) & sSurname & vbCr & vbCr
            oRng.collapse 0
            oRng.Text = "I Write To inform you that " & sName & " on date " & sExpirydate
            oRng.collapse 0
            oRng.Select

[folkstorm]

I have decided to exclude template completely and hard code everything. I would like to use HTML formatting.
User from works fine it opens na empty email . I just need to know how can I implement IF function to the code and add TextBoxes to HTML body.
I have tried but always goes to Else.

Sub template()
Dim myItem As Outlook.MailItem
Dim strContact As String
Dim strHTML As String
 
    Dim sType As String
    Dim sTitle As String
    Dim sName As String
    Dim sSurname As String
    Dim sExpirydate As String
    Dim sGender As String
    
With UserForm1
sType = .ComboBox1.Value
sTitle = .ComboBox2.Value
sName = .TextBox1.Value
sSurename = .TextBox2.Value
sExpierydate = .TextBox3.Value
sGender = .ComboBox3.Value
Set myItem = CreateItem(olMailItem)
strHTML = myItem.HTMLBody
End With


    With myItem
    .BodyFormat = olFormatHTML
    
    If sType = "New" Then
       
    
       .HTMLBody = "<HTML><BODY><b>Test</b> 111r the message text here. sType  </BODY></HTML>"
       .Subject = "New case"
       
      Else
            
    
    .HTMLBody = "<HTML><BODY><b>Test</b> Renewal the message text here. sType  </BODY></HTML>"
       .Subject = "Renewal case"
       
       End If
       
       
       
 
End With

myItem.Display
End Sub

[gmayor]

The code I posted demonstrates how to write to the body of a new message and retains the signature.
If your original code for filling the combo boxes is a clue, sType will never be "New" as that is not an option.