Userform Email Button!!

[MWhiteDesigns]

Hey Guys,
I have finally finished all the code for my userform shown below.

I've poked around and I found a thread which directed me to; http://www.rondebruin.nl/sendmail.htm.

But I am not quite sure what to do.

Basically what I want to accomplish is to generate an email AFTER the user clicks the submit button to input the data to the spreadsheet. I also just want the email to contain the range A,1 to P,30. But I want it done all in one click. So the user "clicks" submit, the data is transposed to the sheet and it generates and email.

Private Sub SubmitCommand_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Sheets("HVS Attrition")

irow = ws.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row

With ws

.Cells(5, 3).Value = NameTextBox.Value
.Cells(9, 3) = EIDTextBox.Value
.Cells(13, 3) = AVAYATextBox.Value
.Cells(17, 3) = DepartmentComboBox.Value
.Cells(19, 3) = EffectiveDateTextBox.Value
.Cells(21, 3) = ReasonComboBox.Value
.Cells(24, 3) = ReportedByTextBox.Value

If Me.YesOptionButton.Value = True Then
.Cells(27, 3) = "Yes"
End If
If Me.NoOptionButton.Value = True Then
.Cells(27, 3) = "No"
End If
If Me.SundayCheckBox.Value = True Then
.Cells(5, 7) = "X"
End If
If Me.MondayCheckBox.Value = True Then
.Cells(7, 7) = "X"
End If
If Me.TuesdayCheckBox.Value = True Then
.Cells(9, 7) = "X"
End If
If Me.WednesdayCheckBox.Value = True Then
.Cells(11, 7) = "X"
End If
If Me.ThursdayCheckBox.Value = True Then
.Cells(13, 7) = "X"
End If
If Me.FridayCheckBox.Value = True Then
.Cells(15, 7) = "X"
End If
If Me.SaturdayCheckBox.Value = True Then
.Cells(17, 7) = "X"
End If

.Cells(5, 11).Value = SundayStartTextBox.Value
.Cells(5, 14).Value = SundayEndTextBox.Value

.Cells(7, 11).Value = MondayStartTextBox.Value
.Cells(7, 14).Value = MondayEndTextBox.Value

.Cells(9, 11).Value = TuesdayStartTextBox.Value
.Cells(9, 14).Value = TuesdayEndTextBox.Value

.Cells(11, 11).Value = WednesdayStartTextBox.Value
.Cells(11, 14).Value = WednesdayEndTextBox.Value

.Cells(13, 11).Value = ThursdayStartTextBox.Value
.Cells(13, 14).Value = ThursdayEndTextBox.Value

.Cells(15, 11).Value = FridayStartTextBox.Value
.Cells(15, 14).Value = FridayEndTextBox.Value

.Cells(17, 11).Value = SaturdayStartTextBox.Value
.Cells(17, 14).Value = SaturdayEndTextBox.Value

.Cells(20, 5).Value = NotesTextBox.Value

End With
End Sub

 

[MWhiteDesigns]

Ok so one last condition of satisfaction.

I would like for the subject line to read " Attrition for (then the value of the Name Text Box)".

In addition, I tweaked my code below to populate a message if a user does not input information. However is there a way to show these one at a time? Say a user doesnt input a value in the Name, EID or AVAYA boxes. When you click submit it shows an error message for each, one after another. Is there a way to have it only show the messages one at a time.

So if a user leaves name and EID blank, and clicks submit. an error message states, "Please enter name". They click ok and then it takes them back to the form. Then if they click submit again and still havent filled out the eid field, it now generates the next message instead of showing error message one after another.

Does that make sense?

Private Sub SubmitCommand_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Sheets("HVS Attrition")

With ws

.Cells(5, 3).Value = NameTextBox.Value
If Len(Trim(NameTextBox)) = 0 Then
MsgBox "Please enter EID. "
End If

.Cells(9, 3) = EIDTextBox.Value
If Len(Trim(EIDTextBox)) = 0 Then
MsgBox "Please enter EID. "
End If

.Cells(13, 3) = AVAYATextBox.Value
If Len(Trim(AVAYATextBox)) = 0 Then
MsgBox "Please enter AVAYA Agent IP. "
End If

.Cells(17, 3) = DepartmentComboBox.Value
If Len(Trim(DepartmentTextBox)) = 0 Then
MsgBox "Please select Department. "
End If

.Cells(19, 3) = EffectiveDateTextBox.Value
If Len(Trim(EffectiveTextBox)) = 0 Then
MsgBox "Please enter effective date. "
End If

.Cells(21, 3) = ReasonComboBox.Value
If Len(Trim(EIDTextBox)) = 0 Then
MsgBox "Please select reason for Attrition. "
End If

.Cells(24, 3) = ReportedByTextBox.Value
If Len(Trim(ReportedByTextBox)) = 0 Then
MsgBox "Please specify who this is being reported by. "
End If


If Me.YesOptionButton.Value = True Then
.Cells(27, 3) = "Yes"
End If
If Me.NoOptionButton.Value = True Then
.Cells(27, 3) = "No"
End If
If Me.SundayCheckBox.Value = True Then
.Cells(5, 7) = "X"
End If
If Me.MondayCheckBox.Value = True Then
.Cells(7, 7) = "X"
End If
If Me.TuesdayCheckBox.Value = True Then
.Cells(9, 7) = "X"
End If
If Me.WednesdayCheckBox.Value = True Then
.Cells(11, 7) = "X"
End If
If Me.ThursdayCheckBox.Value = True Then
.Cells(13, 7) = "X"
End If
If Me.FridayCheckBox.Value = True Then
.Cells(15, 7) = "X"
End If
If Me.SaturdayCheckBox.Value = True Then
.Cells(17, 7) = "X"
End If

.Cells(5, 11).Value = SundayStartTextBox.Value
.Cells(5, 14).Value = SundayEndTextBox.Value

.Cells(7, 11).Value = MondayStartTextBox.Value
.Cells(7, 14).Value = MondayEndTextBox.Value

.Cells(9, 11).Value = TuesdayStartTextBox.Value
.Cells(9, 14).Value = TuesdayEndTextBox.Value

.Cells(11, 11).Value = WednesdayStartTextBox.Value
.Cells(11, 14).Value = WednesdayEndTextBox.Value

.Cells(13, 11).Value = ThursdayStartTextBox.Value
.Cells(13, 14).Value = ThursdayEndTextBox.Value

.Cells(15, 11).Value = FridayStartTextBox.Value
.Cells(15, 14).Value = FridayEndTextBox.Value

.Cells(17, 11).Value = SaturdayStartTextBox.Value
.Cells(17, 14).Value = SaturdayEndTextBox.Value

.Cells(20, 5).Value = NotesTextBox.Value

End With
End Sub

 

[Noz2k]

I'm no guru on here but try creating a module with a code similar to this in it

Sub Mail()
With Worksheets("Sheet1")
.Activate
End With
''Makes Sheet visible, active and unprotects
Dim Recipient As String
Dim TempFilePath As String
Dim TempFileName As String
Dim Dest As Workbook
Dim Source As Range
Recipient = Yourname@emailaddress.com
''Specifies where to find email address of the desired recipient
TempFilePath = Environ$("temp") & "\"
TempFileName = "Data " & Format(Now, "ddmmyyyy hhmm") & ".xlsx"
''Specifies where to save and what to name the file
Set Source = ActiveSheet.Range("A1:P30").Cells
Set Dest = Workbooks.Add(xlWBATWorksheet)
''Specifies the range to later be copied
''Specifies the destination to later be pasted to as a sheet in a new workbook
 
Source.Copy
 
''Copies the range
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
.SaveAs Filename:=TempFilePath & TempFileName, FileFormat:=51
End With
''Specifies where in the new workbook to paste and saves the new workbook to the path specified earlier
''Specifies the format of the new workbook (51 = xlsx)
 
Dest.Close
''Closes the new workbook
Dim objEmail
''Instantiated email object
intSpacing = 1
''Defines the spacing between cells
intPadding = 3
''Defines the spacing within a cell
borders_wanted = True
''Hides the cell edges and shows bordres
Set objEmail = CreateObject("CDO.Message")
objEmail.FROM = """YourName""Youremail@email.com"
''Specifies who the email will say it is from
objEmail.To = Recipient
''Specifies that the email should be sent to the email address defined as recipient
objEmail.Subject = "Email Subject"
''Specifies the subject of the email
 
objEmail.HTMLBody = "Email Body Line 1."
 
 
"Email body line 2"
''Specifies the text in the body of the email
objEmail.AddAttachment TempFilePath & TempFileName
''Adds an attachment to the email (the new workbook saved in the destination specified earlier)
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "INSERTMAILSERVERHERE"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
''Configures the email to send through the mail server
objEmail.Send
''Sends the email
Kill TempFilePath & TempFileName
''Deletes the new workbook from the destination specified earlier
 
End Sub

What email server do you use?

 

[MWhiteDesigns]

Outlook

 

[Noz2k]

ah ok, I'm not quite sure what the smtpserver would be using outlook.

I think using outlook should make things easier for you though.


If you look here you should be able to find how to do what you want

http://www.rondebruin.nl/mail/folder2/mail4.htm

The first part of the code should still be fine, it's just the make up of the email that should actually be simpler with outlook. Unfortunately I don't have outlook to test.

In response to your error messaging query then just seperate the errors with if thens

e.g.

if Eid = "" Then
msgbox "Eid cannot be blank"
Else
if Avaya = "" Then
Msgbox "Avaya cannot be blank"
Else
Your code when all mandatory fields are completed
End if
End if

 

[MWhiteDesigns]

Thanks but when using that code, the msgbox pops as soon as the form opens

 

[Noz2k]

Sorry, I assumed there was a button to send/complete the form?

If there is then put it on the click procedure of that button

 

[MWhiteDesigns]

There is. I closed the form and reopened it and it was fine.

However when i click submit. It still rattles out all the errors at once instead one at a time.

So say name, first, last are left blank. You click submit.
It states,
Error, name cannot be blank. You click ok
then it says error, first cannot be blank. then you click ok
then it says error, last cannot be blank. then you click ok

and it goes back to the form.

Anyway to make it only prompt one, then go back to the form and then if the user doesn't complete everything again, the next error message pops up.

 

[MWhiteDesigns]

Also is there a way to change the title window of the error? Right now it reads "Microsoft Excel". I've seen how to do it somewhere but I can't find it now.

 

[Noz2k]

That code should do that? I've just tested it and it works for me

are you putting it in the correct place

if Name = "" Then
Msgbox "Name cannot be blank
else
if First = "" Then
MsgBox "First Cannot be blank"
Else
If Last = "" Then
MsgBox "Last cannot be blank"
Else
Your Code
End If
End If
End If