Outlook 2013 - if statement for more conditions

[bmdo]

Hi,

I am a vba newbie

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

[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 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]

option-and-checkbox.zip

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?