Get values from Userform within other code

gds257

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
I have been using vba for a long time to do very simple things (e.g. input boxes and to automate formatting). I'm trying to do something slightly more complicated, and have googled for a long time but can't find the answer - I suspect the problem is that I don't even really know what to put in the search bar!

I'm trying to write a macro whereby you click a button, answer some questions, and this opens up a pre-written email in Outlook. This is what I've got so far, which works fine:

VBA Code:
Private Sub CommandButton1_Click()
'Brings up Welcome Email template

'get customisation details
Dim name As String
name = InputBox("Contact Person")

'initialise outlook
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

'set email as object
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(0)

'define message body
Dim mailbody As String
mailbody = "Thank you for contacting us. Your contact person is " & name & "." & vbNewLine & vbNewLine & _
              "They will be in touch shortly."

'define email details
With objEmail
    .to = "email address"
    .Subject = "Welcome"
    .Body = mailbody
    .Display
End With

'Reset
Set objEmail = Nothing
Set objOutlook = Nothing

End Sub

I want to add to this now, so that when the user clicks the button it brings up a userform with a list of checkbox items which the user can select. I want to set the body of the email to include a series of additional sentences based on the checkbox items. I've set my form up, and have got it to show with all the options available to tick. I just don't know how to get from that form back into the main body of my code and use the inputs - everything I can find on google just talks about using the userform to change the Excel spreadsheet itself, which isn't what I want.

I expect the code to be something like:

VBA Code:
Dim textoption1 As String
    If form.option1.Value = True Then textoption1 = "You have not provided your date of birth."
    else textoption1 = ""

And my mailbody would be:

VBA Code:
mailbody = "Thank you for contacting us. Your contact person is " & name & "." & vbNewLine & vbNewLine & _
              "They will be in touch shortly." & vbNewLine & vbNewLine & _
              textoption1

What I don't know is how I actually get my main code to read what the user has selected in the module, and change the body of the email accordingly. Can anyone advise? As said, I am very much new to anything more complicated than basic message boxes, so apologies if this is a very simple question!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

gds257

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
In case anyone else comes across this with the same problem, I was making (as I expected) a very basic error.

I hadn't set up an 'OK' button to hide the form after the boxes were ticked; I was just closing the form. It appears that once the form is closed, it disappears - selections aren't logged or saved. I set up an 'OK' button with the code:

VBA Code:
userform.hide

Problem solved.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,776
Messages
5,544,155
Members
410,595
Latest member
Tatum2020
Top