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.

​
19197



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 :yes. 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.