View Full Version : Outlook 2013 - if statement for more conditions
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
01-28-2015, 08:35 AM
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
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? :)
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
01-29-2015, 08:00 AM
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.
Hi,
are you saying that the if statements will be easier to create when I use outlook templates instead of html messages?
gmayor
01-29-2015, 10:56 PM
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.
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
01-30-2015, 05:55 AM
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
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
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
02-03-2015, 08:31 AM
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
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
How can I mark this as solved?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.