Userform vba

[Natsha]

Hi all

I have to create an userform for productivity purpose... I have added form and labels and text box... Whatever I know but I dnt know how to code it..I referred videos but the code used were different and doesn't work for me... I have attached the excel sheet..

In sheet two j have mentioned the fields format.. after referring it you can delete it.

I also added an commons button for email..
After entering data In the form..I will click the email and it should send an mail in Outlook with the below format.

To: " I can add it
Cc: I can add it
From: my mail

Hi name,

Please find the productivity status for the day


" Productivity status should be below here with a table or an snap

Signature..

Please help..I been struggling to do this.

Excel sheet
Also posted here Need help on userform creating

 

[Logit]

Here is sample code for a simple database. It will work perfectly for your needs. Of course it is not already setup for your specific
layout. You will need to edit the code so it complies with the many fields for your project. Play with the database ... see how it functions
and learn how to edit the code for your project.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download sample database : Simple Database.xlsm



Here is sample email code :

Option Explicit

Sub Mail_workbook_Outlook()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim sndEmail As String
    Dim toEmail As String
    Dim ccEmail As String
    Dim bccEmail As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toEmail = Sheets("EMAIL").Range("D2").Value
    ccEmail = Sheets("EMAIL").Range("D3").Value
    bccEmail = Sheets("EMAIL").Range("D4").Value

    On Error Resume Next
    With OutMail
        .To = toEmail
        .CC = ccEmail
        .BCC = bccEmail
        .Subject = "Spare Parts Maintenance"
        .Body = "The parts have been placed on today's load sheet and will be processed by EOB today.  The parts have also been transferred to the repository file."
        .Attachments.Add (Application.ActiveWorkbook.FullName) 'attaches this workbook to email
        
        .Send                              '<-- .Send will auto send email without review
        '.Display                            '<-- .Display will show the email first for review
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Download sample database : WORKS Email w Attachment.xlsm

 

[Natsha]

Here is sample code for a simple database. It will work perfectly for your needs. Of course it is not already setup for your specific
layout. You will need to edit the code so it complies with the many fields for your project. Play with the database ... see how it functions
and learn how to edit the code for your project.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
   
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
   
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
   
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
   
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download sample database : Simple Database.xlsm



Here is sample email code :

Option Explicit

Sub Mail_workbook_Outlook()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim sndEmail As String
    Dim toEmail As String
    Dim ccEmail As String
    Dim bccEmail As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toEmail = Sheets("EMAIL").Range("D2").Value
    ccEmail = Sheets("EMAIL").Range("D3").Value
    bccEmail = Sheets("EMAIL").Range("D4").Value

    On Error Resume Next
    With OutMail
        .To = toEmail
        .CC = ccEmail
        .BCC = bccEmail
        .Subject = "Spare Parts Maintenance"
        .Body = "The parts have been placed on today's load sheet and will be processed by EOB today.  The parts have also been transferred to the repository file."
        .Attachments.Add (Application.ActiveWorkbook.FullName) 'attaches this workbook to email
       
        .Send                              '<-- .Send will auto send email without review
        '.Display                            '<-- .Display will show the email first for review
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Download sample database : WORKS Email w Attachment.xlsm

Hi logit... Thanks for replying but I could not adjust the code to my needs... I tired to edit it but mine is different it doesn't work for me.. I will attach my excel sheet link.. kindly help me If you have spare time ...tq

Excel sheet

 

[Natsha]

Hi all,
I got the solution.I will attach the excel...thank you for helping me

Solution

 

[smozgur]

@Natsha - glad to hear you got the solution.

The attachment link that you posted will be eventually a dead link, so I switched the marked solution with @Logit's generic implementation which demonstrates the method in a perfect way. So it will help future readers since it will be intact on this thread page.

If you prefer to post your implementation (code and description if necessary) then you can do so, and then you can mark your own post as the solution in case it has a different approach other than posted in #2 above.

 

[Logit]

Glad you have an answer. Cheers !

 

[Natsha]

?....Also thank you for replying and assisting me when I needed it

Glad you have an answer. Cheers

 

[Natsha]

Glad you have an answer. Cheers !

strbody = "Hi," & "<br>" & _

"Please find the productivity status for the day" & "<br/><br>" & RangetoHTML(rng) & "<br/><br>" & "Kind regards," & "<br/>" & L_01.Caption


I have an doubt, how to change the font size of body content msg....in macro when I click mail button...it will go to outlook with these body msg...but the font size is so small...

 

[Logit]

See if these help :

Change HTML email body font type and size in VBA

VBA to change font and size in email

Outlook Mail body font size problem - OzGrid Free Excel/VBA Help Forum

 

[smozgur]

Since you are using HTML elements (<br>), then you should be using the HTMLBody property. The following should do the trick:

strbody = "<div style=""font-size:16px"">Hi," & "<br>" & _
"Please find the productivity status for the day" & "<br/><br>" & RangetoHTML(rng) & "<br/><br>" & "Kind regards," & "<br/>" & L_01.Caption & "</div>"

Note: I prefer using <p> instead - mainly for readability - unless you need single line break:

strbody = "<div style=""font-size:16px"">" & _
"<p>Hi," & "</p>" & _
"<p>Please find the productivity status for the day</p>" & _
"<p>" & RangetoHTML(rng) & "</p>" & _
"<p>Kind regards,</p>" & _
"<p>" & L_01.Caption & "</p>" & _
"</div>"