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:
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:
And my mailbody would be:
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!
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!