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

filarap

New Member
Joined
May 15, 2015
Messages
33
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

New Member
Joined
May 15, 2015
Messages
33
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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

New Member
Joined
May 15, 2015
Messages
33
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

New Member
Joined
May 15, 2015
Messages
33
I see,

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

Filarap
 

Watch MrExcel Video

Forum statistics

Threads
1,123,124
Messages
5,599,839
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top