Userform to send email to selected people

matts

New Member
Joined
Sep 11, 2002
Messages
41
I am learning fairly quickly but have just got stuck, and a bit confused.

I have setup on a spreadsheet some VBA to check the day of the week each day the workbook is closed, and if it is Friday then to bring up userform1. The userform contains a short message saying that it is friday and this file needs to be forwarded to selected people. Below this message there are two check boxes (which are pre checked), each with a different persons name. Below that is then a send and cancel button.

How and where do I write the code for the send and cancel button. The send button when clicked should check to which check boxes are marked and send an email to each of the people checked. The cancel button should exit the form and allow the workbook to close with no action.

I hope I have made my question clear.Any help or comments anyone can supply is greatly appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

matts

New Member
Joined
Sep 11, 2002
Messages
41
OK....I worked out that just putting an 'END' in the cancel button will do what I need to with it.

I still however cannot work out how to get the SEND button to check the tick boxes and send mail to those ticked.
 

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
Sorry Matt,
not actually able to answer your question, but I am just keeping it rolling....hoping someone out there will have an answer, as I have come across a similar problem.

I am looking for a bit of code that selects the file I am working on, eg. Template.xls and then opens the email program, attaches it to a new email, and sends it to another user.
The email program I am using is Lotus Notes. Is this going to cause problems when I go to send the file??

Matt, also, I really liked the concept you are using - where by the Message will appear each Friday. Do you mind sharing that bit of code?

Regards,
Greg
 

matts

New Member
Joined
Sep 11, 2002
Messages
41
Thanks for the comments ElectricSkywalker. Happy to show, especially as most of my knowledge has come from others sharing.
In VBA ThisWorkbook put these items. The second one actually calls the userform1.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call AutoEmailFriday
End Sub

Sub AutoEmailFriday()
Dim DDAY As Date
DDAY = WeekDay(Now())
If DDAY = 5 Then Exit Sub
UserForm1.Show
End Sub
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

if the checkboxes are called, let's say cbx1 and cbx2, and the ok button is called btn1, write the following code:

Code:
sub btn1_click()

if cbx1.value = true then
  stringmailaddresses = stringmailaddresses + "person1@person1smailserver.com"
end if

if cbx2.value = true then
  stringmailaddresses = stringmailaddresses + "person2@person2smailserver.com"
end if

'send the email (dont exactly know the function for that)

end sub
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
matts -

I have not tested this completely, however, I know the Lotus Notes portion works very well. I use it everyday. Post back if you have problems.

Thanks,

Kurt

Several thoughts for your project.

Add another sheet to your project named E-Mail Addresses. This sheet can be visible or not. On userform initialize delete range A2:A25 from sheet E-Mail Addresses.

ActiveWorkbook.sheets("E-Mail Addresses").Select
Range("A2:A25").ClearContents

On a userform you have 2 checkboxes, 2 textboxes, & 2 command buttons.

Checkbox1 = Bob Jones (or whom ever)
Checkbox2 = Sam Smith (or whom ever)
TextBox1 = (is empty)
Textbox2 = (is empty)
CommandButton1 = Send
CommandButton2 = Cancel

Hardcode the checkbox's if specific folks will potentially get the E-mail all the time.
Use the textbox's for folks you would like an option to send to when needed.

Do something like this:

Private Sub CheckBox1_Click()
If UserForm1.CheckBox1.Enabled = True then
sheets("E-Mail Addresses").Range("A2") = bjones@whatever.com
End if
End Sub

Private Sub CheckBox2_Click()
If UserForm1.CheckBox2.Enabled = True then
sheets("E-Mail Addresses").Range("A3") = ssmith@whatever.com
End if
End Sub

Private Sub TextBox1_Exit()
sheets("E-Mail Addresses").Range("A4") = Userform1.Textbox1.text
End Sub


Private Sub TextBox2_Exit()
sheets("E-Mail Addresses").Range("A5") = Userform1.Textbox2.text
End Sub


Private Sub Commandbutton1_Click()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDataBase("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
' Create New Mail and Address Title Handlers
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
' Select range of e-mail addresses
Recipient = Sheets("E-Mail Addresses").Range("A2:A25").Value
MailDoc.SendTo = Recipient
ans = MsgBox("Would you like to Copy (cc) anyone on this message?" _
, vbQuestion & vbYesNo, "Send Copy")
If ans = vbYes Then
ccRecipient = InputBox("Please enter the additional recipient's e-mail address" _
, "Input e-mail address")
MailDoc.CopyTo = ccRecipient
End If
MailDoc.Subject = "Updated blah,blah,blah" ' Your subject
MailDoc.Body = _
"The blah,blah,blah Has Been Update. Please Review For Changes"
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True
'attachment1 = "C:\Blah.xls" ' Required File Path & Name

If attachment1 <> "" Then
On Error Resume Next
' Set AttachME = MailDoc.
' Set EmbedObj1 = AttachME.embedobject(1454, "attachment1",
On Error Resume Next
End If
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.Send 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

CommandButton2_Click "It appears you already have what you are looking for."
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
matts -

change the Private Sub TextBox1_Exit()

to Private Sub TextBox1_AfterUpdate() on both Textboxes


You are also going to have problems with the notes code I sent. The e-mail will be sent out without the attachment. I need to figure out what I need to change in the code below to do that. I will post back

Thanks,

Kurt


If attachment1 <> "" Then
On Error Resume Next
' Set AttachME = MailDoc.
' Set EmbedObj1 = AttachME.embedobject(1454, "attachment1",
On Error Resume Next
End If
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

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