User Form
 

Hello all

I am new to VB macros in XL.

I am writing a macro to achieve the following.

If any changes take place to this document, I want to ask user €œDoes he wants
to send a mail?€�

I am using the following command to ask this question.

Res = MsgBox("This file is modified today" & vbCrLf & "Do you want to send a
mail ??? ", vbYesNo + vbQuestion, "Legal Document")

If Res = 6 Then (means, user clicked YES button)

I want to provide user a message box with a list of mail ids and check boxes
against each mail id to tick the mail ids he wants to send mail and then
click €œSend€� button. Is it possible with the MsgBox command I used as above???
What are the other possible ways???

End If

Thanks
Sri

User Form
 

Hi Sri

I think your stuck using a Userform as i don't think you can add
controls to a Msgbox. You can add a Userform like so:

Open the Visual Basic Editor (VBE) from the "Excel Window Tools Menu
Macro Visual Basic Editor" or "Alt & F11" keyboard shortcut.

Insert a Userform from the "VBE Window Insert Menu Userform"

Add your Controls from the Toolbox (if you can't see the Toolbox "VBE
Window View Menu Toolbox")

Click the control type to add a control or double click if you want to
add multiple controls of that type (each control has a tool tip so if
you are unsure hover over the control to find out what it is)

When your happy with your form you can get Excel to show it if your
user clicks yes to the first Msgbox like so:

If Res = 6 Then

Userform1.show vbmodal

end if

This will load your form, i guess adding a button to your form will
allow the user to select the options he wants and then click the
button to send the email. Sending the mail is a different story i
would have a look at the link below to see what your options are with
emailing from within Excel. This is a lot of really useful example
regarding emailing from Excel by Ron de Bruin

http://www.rondebruin.nl/sendmail.htm

You will of course need to attach some code to your button for it to
work, a quick way to do that is to double click the button while in
VBE this will add the click event to the form and with in the event
you add your code.

Private Sub CommandButton1_Click()

'your code for your button goes in here

End Sub

The code below is a basic loop through each of the CheckBoxes on the
Userform (mine had 5 but you can have as many as you want) to see if
they are set to True (checked) if so add the email addess in the
caption beside that checkbox to a string ( i added an email address to
the caption of each of the CheckBoxes so the user can see who they are
picking). Once you have checked all your CheckBoxes it calls the code
to send a small email message from the site listed above but instead
of sending to the default .To = " we swap that out for
your list of email addresses.

Option Explicit
Dim i As Integer
Dim Ctrl As Control
Dim sendEmailTo As String
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Private Sub CommandButton1_Click()

For i = 1 To 5 '5 is the number of checkboxes on the form

Set Ctrl = UserForm1.Controls("CheckBox" & i)

If Ctrl.Value = True Then 'Box was checked

sendEmailTo = sendEmailTo & Ctrl.Caption & "; "

End If

Set Ctrl = Nothing

Next

sendEmailTo = Left(sendEmailTo, Len(sendEmailTo) - 2)

Mail_small_Text_Outlook

End Sub


Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = " 'Change to this : .To = sendEmailTo
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I hope this helps you out, i would go check out Ron's site it is very
helpful and may help you understand better how the process works.

Later

Steve

User Form
 

Hello Steve

Thank you very much for your reply. I tried it out and working excellent.

Thanks again.

Sri

Incidental wrote:
Hi Sri

I think your stuck using a Userform as i don't think you can add
controls to a Msgbox. You can add a Userform like so:

Open the Visual Basic Editor (VBE) from the "Excel Window Tools Menu
Macro Visual Basic Editor" or "Alt & F11" keyboard shortcut.

Insert a Userform from the "VBE Window Insert Menu Userform"

Add your Controls from the Toolbox (if you can't see the Toolbox "VBE
Window View Menu Toolbox")

Click the control type to add a control or double click if you want to
add multiple controls of that type (each control has a tool tip so if
you are unsure hover over the control to find out what it is)

When your happy with your form you can get Excel to show it if your
user clicks yes to the first Msgbox like so:

If Res = 6 Then

Userform1.show vbmodal

end if

This will load your form, i guess adding a button to your form will
allow the user to select the options he wants and then click the
button to send the email. Sending the mail is a different story i
would have a look at the link below to see what your options are with
emailing from within Excel. This is a lot of really useful example
regarding emailing from Excel by Ron de Bruin

http://www.rondebruin.nl/sendmail.htm

You will of course need to attach some code to your button for it to
work, a quick way to do that is to double click the button while in
VBE this will add the click event to the form and with in the event
you add your code.

Private Sub CommandButton1_Click()

'your code for your button goes in here

End Sub

The code below is a basic loop through each of the CheckBoxes on the
Userform (mine had 5 but you can have as many as you want) to see if
they are set to True (checked) if so add the email addess in the
caption beside that checkbox to a string ( i added an email address to
the caption of each of the CheckBoxes so the user can see who they are
picking). Once you have checked all your CheckBoxes it calls the code
to send a small email message from the site listed above but instead
of sending to the default .To = " we swap that out for
your list of email addresses.

Option Explicit
Dim i As Integer
Dim Ctrl As Control
Dim sendEmailTo As String
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Private Sub CommandButton1_Click()

For i = 1 To 5 '5 is the number of checkboxes on the form

Set Ctrl = UserForm1.Controls("CheckBox" & i)

If Ctrl.Value = True Then 'Box was checked

sendEmailTo = sendEmailTo & Ctrl.Caption & "; "

End If

Set Ctrl = Nothing

Next

sendEmailTo = Left(sendEmailTo, Len(sendEmailTo) - 2)

Mail_small_Text_Outlook

End Sub

Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = " 'Change to this : .To = sendEmailTo
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I hope this helps you out, i would go check out Ron's site it is very
helpful and may help you understand better how the process works.

Later

Steve