Outlook 2013 - if statement for more conditions

[bmdo]

Hi,

I am a vba newbie :hi::hi:

I have an Outlook form with 2 Textboxes (for entering contact name and a date)
Then I have 4 Option Buttons and 1 Checkbox - and of course Submit and Cancel.

The thing is that when a Option button is checked a specific html template opens.
When the Checkbox is checked a different html template should open.

I have tried this - but it doesn't work correctly :(
Maybe obvious for you ;) I hope someone can help me with the correct code and/or syntax

If OBtn1.Value = True Then
templateFile = "Template0"
End If

If OBtn2.Value = True And CheckBox1.Value = True Then
templateFile = "Atemplate_2"
Else
templateFile = "Atemplate_1"
End If

If OBtn3.Value = True And DHLCheckBox.Value = True Then
templateFile = "Dtemplate_2"
Else
templateFile = "Dtemplate_1"
End If

If OBtn4.Value = True Then
templateFile = "Template1"
End If

[gmayor]

You haven't indicated the purpose of the form but you probably want something like the following (here to create a mail item based on a template), which makes a few assumptions based on what you have posted, but it covers the main point about checking the selected options:



Option Explicit
Private Sub UserForm_Initialize()
With Me
.OBtn1.Value = True
.CheckBox1.Enabled = False
.DHLCheckBox.Enabled = False
End With
End Sub

Private Sub BtnSubmit_Click()
Dim templateFile As String
Dim oItem As MailItem
Dim TemplatePath As String
TemplatePath = "C:\Path\"
With Me
Select Case True
Case .OBtn1.Value: templateFile = "Template0"
Case .OBtn2.Value = True
If .CheckBox1.Value = True Then
templateFile = "Atemplate_2"
Else
templateFile = "Atemplate_1"
End If
Case .OBtn3.Value
If .DHLCheckBox.Value = True Then
templateFile = "Dtemplate_2"
Else
templateFile = "Dtemplate_1"
End If
Case .OBtn4.Value
templateFile = "Template1"
End Select
.Hide
End With
Set oItem = Application.CreateItemFromTemplate(TemplatePath & templateFile & ".oft")
Unload Me
End Sub

Private Sub OBtn1_Change()
With Me
If .OBtn1.Value = True Then
.CheckBox1.Enabled = False
.DHLCheckBox.Enabled = False
End If
End With
End Sub
Private Sub OBtn2_Change()
With Me
If .OBtn2.Value = True Then
.CheckBox1.Enabled = True
.DHLCheckBox.Enabled = False
End If
End With
End Sub
Private Sub OBtn3_Change()
With Me
If .OBtn3.Value = True Then
.CheckBox1.Enabled = False
.DHLCheckBox.Enabled = True
End If
End With
End Sub
Private Sub OBtn4_Change()
With Me
If .OBtn4.Value = True Then
.CheckBox1.Enabled = False
.DHLCheckBox.Enabled = False
End If
End With
End Sub

[bmdo]

hi gmayor,

sorry :) Is it easier if you get the complete code?
I could also try to look through your example as see if I can get it to work, and get back with everything if it doesn't? :)

[bmdo]

hi gmayor,

sorry :) Is it easier if you get the complete code?
I could also try to look through your example as see if I can get it to work, and get back with everything if it doesn't? :)

Here is my complete script

Private Sub btnCancel_Click()
'Close UserForm
Unload Me

End Sub

Private Sub btnSubmit_Click()
Dim oFSO As Object
Dim oFS As Object
Dim MailMsg As Outlook.MailItem
Dim rootPath As String
Dim templateFile As String
Dim sText As String
Dim sbody As String

Set MailMsg = GetCurrentItem

Set oFSO = CreateObject("Scripting.FileSystemObject")

sbody = MailMsg.HTMLBody

rootPath = "\\servername\networkshare\Outlook app VBscript\Prealert template\"
templateFile = ""
'path = rootPath + templateFile + ".html"

If ArrivalOBtn.Value = True Then
templateFile = "Arrival_Confirmation"
End If

If DAPOBtn.Value = True Then
templateFile = "Arrival_Confirmation_DAP"
End If

If DelieveryOBtn.Value = True Then
templateFile = "Delivery_Confirmation"
End If

If BrokerOBtn.Value = True Then
templateFile = "Broker_requested"
End If


Set oFS = oFSO.OpenTextFile(rootPath + templateFile + ".html")
sText = oFS.ReadAll()

If ArrivalOBtn = True Then
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If

If DAPOBtn = True Then
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If

If DelieveryOBtn = True Then
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If

If BrokerOBtn = True Then
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If

sText = Replace(sText, "<contact>", tbContact.Value, , vbTextCompare)
sText = Replace(sText, "<ata>", tbATA.Value, , vbTextCompare)

MailMsg.BodyFormat = olFormatHTML

MailMsg.HTMLBody = "<body font-siz=11pt>" & sText & sbody & "</body>"

If Dir(rootPath + templateFile + ".gif") <> "" Then
MailMsg.Attachments.Add rootPath + templateFile + ".gif", olByValue, 0, "test"

End If

Call removerecipi(MailMsg, "Danoffice")

Set oFS = Nothing
Set oFSO = Nothing
Set MailItem = Nothing

Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

[gmayor]

You appear to be adding html to a message rather than creating a set of message templates from which to create new messages, which would be the more straightforward method I would use.
Create the message body for each message type in the Outlook Editor and save each as an Outlook Template (OFT format). Delete the signature before saving as creating a message from a template will add back the default signature.

You can then create the correct message for your userform selection as in my previopus example.

Your message only provides scant clues as to what you are trying to achieve, and when, so it is difficult to be more specific.

[bmdo]

Hi,

are you saying that the if statements will be easier to create when I use outlook templates instead of html messages?

[gmayor]

No, I am saying that the process would be simpler when using templates, rather than (as appears to be the case) a set of functions to amend a document. My first reply showed how to use the results from a variety of radiobuttons and check boxes.

[bmdo]

Ok, I will try with outlook templates and your code example instead. I am afraid I do not have the time today, but will let you know asap :thumb

[Paul_Hossler]

1. if you click the [#] icon you get [ CODE ] ......... [ /CODE ] tags you can paste your code between

2. "Doesn't work correctly" is a little bit fuzzy, but I tried to re-arrange the statements a little to try to understand

After you click the [Submit] button the OB's and CB status is used to identify your template file and the rest

This is not tested of course but give it a try if you want




Option Explicit


Private Sub btnCancel_Click()
Unload Me
End Sub


Private Sub btnSubmit_Click()
Dim oFSO As Object
Dim oFS As Object
Dim MailMsg As Outlook.MailItem
Dim rootPath As String
Dim templateFile As String
Dim sText As String
Dim sbody As String

Set MailMsg = GetCurrentItem

Set oFSO = CreateObject("Scripting.FileSystemObject")

sbody = MailMsg.HTMLBody

rootPath = "\\servername\networkshare\Outlook (file://\\servername\networkshare\Outlook) app VBscript\Prealert template\"
templateFile = ""
'path = rootPath + templateFile + ".html"

If ArrivalOBtn.Value Then
templateFile = "Arrival_Confirmation"
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf DAPOBtn.Value Then
templateFile = IIf(CheckBox1, "Arrival_Confirmation_DAP", "Arrival_Confirmation_DAP_2")
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf DelieveryOBtn.Value Then
templateFile = IIf(CheckBox1, "Delivery_Confirmation", "Delivery_Confirmation_2")
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf BrokerOBtn.Value Then
templateFile = "Broker_requested"
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If

Set oFS = oFSO.OpenTextFile(rootPath + templateFile + ".html")

sText = oFS.ReadAll()
sText = Replace(sText, "<contact>", tbContact.Value, , vbTextCompare)
sText = Replace(sText, "<ata>", tbATA.Value, , vbTextCompare)

MailMsg.BodyFormat = olFormatHTML
MailMsg.HTMLBody = "<body font-siz=11pt>" & sText & sbody & "</body>"
If Dir(rootPath + templateFile + ".gif") <> "" Then
MailMsg.Attachments.Add rootPath + templateFile + ".gif", olByValue, 0, "test"
End If
Call removerecipi(MailMsg, "Danoffice")
Set oFS = Nothing
Set oFSO = Nothing
Set MailItem = Nothing
Unload Me
End Sub

[bmdo]

Ok, I will try to explain a little better - everything works except this. If the checkbox is clicked, it does take a different template. Think my And code things are not correct.


If OBtn1.Value = True Then
templateFile = "Template0"
End If

If OBtn2.Value = True And CheckBox1.Value = True Then
templateFile = "Atemplate_2"
Else
templateFile = "Atemplate_1"
End If

If OBtn3.Value = True And DHLCheckBox.Value = True Then
templateFile = "Dtemplate_2"
Else
templateFile = "Dtemplate_1"
End If

If OBtn4.Value = True Then
templateFile = "Template1"
End If

[bmdo]

I tried this now, whether the CheckBox1 is clicked or not it takes the Arrival_Confirmation_DAP_2 or Delivery_Confirmation_2 template.


If ArrivalOBtn.Value Then
templateFile = "Arrival_Confirmation"
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf DAPOBtn.Value Then
templateFile = IIf(CheckBox1, "Arrival_Confirmation_DAP", "Arrival_Confirmation_DAP_2")
MailMsg.Subject = Replace(MailMsg.Subject, "Pre alert", "Arrival confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf DelieveryOBtn.Value Then
templateFile = IIf(CheckBox1, "Delivery_Confirmation", "Delivery_Confirmation_2")
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)

ElseIf BrokerOBtn.Value Then
templateFile = "Broker_requested"
MailMsg.Subject = Replace(MailMsg.Subject, "Arrival confirmation", "Delivery confirmation", , , vbTextCompare)
MailMsg.Subject = Replace(MailMsg.Subject, "RE:", "", , , vbTextCompare)
End If
[/CODE]

[Charlize]

12796

Little example of form with 3 optionbuttons and a checkbox. Shows how to process the optionbuttons with or without a checkbox value.

Import the form into outlook and run the module that shows the form.


Public theoptions As String

Private Sub CommandButton1_Click()
MsgBox "Option selected = " & theoptions & vbCrLf & _
"Checkbox checked : " & Me.CheckBox1.Value
Select Case theoptions
Case "option1"
If Me.CheckBox1.Value = False Then
'code to do when not checked
Else
'code to do when it's checked
End If
Case "option2"
If Me.CheckBox1.Value = False Then
'code to do when not checked
Else
'code to do when it's checked
End If
Case "option3"
If Me.CheckBox1.Value = False Then
'code to do when not checked
Else
'code to do when it's checked
End If
End Select
Unload Me
End Sub


Private Sub OptionButton1_Click()
theoptions = "option1"
End Sub


Private Sub OptionButton2_Click()
theoptions = "option2"
End Sub


Private Sub OptionButton3_Click()
theoptions = "option3"
End Sub


Private Sub UserForm_Initialize()
Me.CommandButton1.Tag = "OK"
End Sub
Charlize

[bmdo]

Here is what did the trick, now it picks the correct template both with and without the checkbox clicked :)


If ArrivalOBtn.Value = True Then
templateFile = "Arrival_Confirmation"
End If

If DAPOBtn.Value = True Then
If DHLCheckBox.Value = True Then
templateFile = "Arrival_Confirmation_DAP_DHL"
Else
templateFile = "Arrival_Confirmation_DAP"
End If
End If

If DelieveryOBtn.Value = True Then
If DHLCheckBox.Value = True Then
templateFile = "Delivery_Confirmation_DHL"
Else
templateFile = "Delivery_Confirmation"
End If
End If

If BrokerOBtn.Value = True Then
templateFile = "Broker_requested"
End If

[bmdo]

How can I mark this as solved?