Userform to send email to selected people

[matts]

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.

 

[matts]

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]

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]

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]

if the checkboxes are called, let's say cbx1 and cbx2, and the ok button is called btn1, write the following 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]

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]

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