VBA - Using if statement within a string for outlook email from Excel

[filarap]

Hi all,

I am creating a user form within Excel file that is used to input data into the sheet, but also it is sending an email from outlook with specifics from that user form.

I have come to a standstill when trying to use option buttons from the user form.
I have tried to simply continue the text with:
if type1 = true then
"text text text"
else
"text2 text2 text2"
end if

However this clearly does not work. I am guessing i can do it if i use the cell from the sheet, but my goal is to get this done directly from the form. Below is the code i have so far:

Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String


On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Dear colleagues " & vbNewLine & vbNewLine & firstname & " " & surname & " called in sick with their first day of absence being " & _
startday & "." & vbNewLine & "Reason for the absence is " & reason & "(" & code & ")"
'This is where is need to continue if option buttons




On Error Resume Next
With xOutMail
.To = "filarap@yahoo.com"
.CC = ""
.BCC = ""
.Subject = "text of subject " & firstname & " " & surname
.Body = xMailBody
.send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing


Regards
Filarap

 

[Leith Ross]

Hello Filarap,

Are the Options Buttons on a worksheet or a VBA UserForm?

If the buttons are on a worksheet, what is the worksheet's name?

Do the buttons form a single functional group?

 

[filarap]

Hi Ross,

Buttons are on the userform. They are creating one group (for example options for answer to a question are "Yes" and "No"). Names for the option buttons for the code above are type1 and type2.

Kind rgeards
Filarap

 

[Norie]

Perhaps something like this.

Dim strReason As String

    ' other code

    If OptionButton1.Value = True Then
        strReason = " reason 1"
    ElseIf OptionButton2.Value = True Then
        strReason = " reason 2"
    End If

    xMailBody = "Dear colleagues " & vbNewLine & vbNewLine & firstname & " " & surname & " called in sick with their first day of absence being " & _
                startday & "." & vbNewLine & "Reason for the absence is " & strReason

 

[Leith Ross]

Hello filarap,

Here is code you can add to button on the UserForm to display the option button selected. Let me know if you need help adding this to your current macro.

Private Sub CommandButton1_Click()


    Dim Msg As String
    
        Msg = "(none)"
        
        If Type1.Value = True Then Msg = Type1.Caption
        If Type2.Value = True Then Msg = Type2.Caption
        
        MsgBox "You selected option " & Msg
        
End Sub

 

[filarap]

Thank you very much Ross,

This works great

Can you please advise why did you use msg = "none part". I understand the rest of the code, but this part is confusing me?

Kind regards
Filarap

 

[Leith Ross]

Hello filarap,

This was added in case the user clicks the command button before selecting an option button. This can be used to easily validate the user has chosen an option before the userform is closed.

 

[filarap]

I see,

Thank you very much for your assistance Ross, it is much appreciated.

Filarap