How to add text to e-mail body when checkbox is ticked on userform

F

flea80

New Member
Joined
Feb 15, 2023
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've created a userfrom to create an e-mail based on the values of text boxes, combo boxes and checkboxes.
Below is what I have written so far, and yes it is a mess. Just started with coding and learning but I couldn't find an answer on my "problem".
How can I simplify this?

Another thing: when I tick the checkbox "Partial" I want to have the text in the e-mail changed to "Please dispatch this order partially TODAY using ".

VBA Code: 
Private Sub Createbtn_Click()
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem

Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)

With olEmail
.BodyFormat = olFormatHTML
.Display
.To = "test@test.nl"
.CC = "test@test.nl"
.Subject = "SO " & TumbaSO.Value & " (PO " & Koopbrief.Value & ")"
    If cbETA.Value = True And cbForwarder.Value = True Then
.Body = "Dear Team," & vbNewLine & vbNewLine _
        & "Please dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value & vbNewLine _
        & vbNewLine & "Forwarder: " & txtForwarder.Value & vbNewLine & "Account: " & txtFWDAccount.Value _
        & vbNewLine & vbNewLine & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value _
        & vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & cbRequestedby.Value _
        & vbNewLine & vbNewLine & "Alfa Laval Nijmegen B.V."

    ElseIf cbETA.Value = True Then
.Body = "Dear Team," & vbNewLine & vbNewLine _
        & "Please dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value & vbNewLine _
        & vbNewLine & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value _
        & vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & cbRequestedby.Value _
        & vbNewLine & vbNewLine & "Alfa Laval Nijmegen B.V."

   ElseIf cbForwarder.Value = True Then
.Body = "Dear Team," & vbNewLine & vbNewLine _
        & "Please dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value & vbNewLine _
        & vbNewLine & "Forwarder: " & txtForwarder.Value & vbNewLine & "Account: " & txtFWDAccount.Value _
        & vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & cbRequestedby.Value _
        & vbNewLine & vbNewLine & "Alfa Laval Nijmegen B.V."
    
    Else
.Body = "Dear Team," & vbNewLine & vbNewLine _
        & "Please dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value & vbNewLine _
        & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & cbRequestedby.Value _
        & vbNewLine & "Alfa Laval Nijmegen B.V."

     End If

End With

End sub
 

Attachments

  • ticketform.png
    ticketform.png
    14.3 KB · Views: 11

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi @flea80 : Thanks for posting on the forum.

I made some tweaks to simplify the code and also, of course, included the word "partilly".

Please try the following code:

VBA Code: 
Private Sub Createbtn_Click()
  Dim olApp As Outlook.Application
  Dim olEmail As Outlook.MailItem
  Dim body1 As String, body2 As String, body3 As String
  Dim vbn As Variant
  
  Set olApp = New Outlook.Application
  Set olEmail = olApp.CreateItem(olMailItem)
  vbn = vbNewLine           'Just to make it shorter
  
  With olEmail
    .BodyFormat = olFormatHTML
    .Display
    .To = "test@test.nl"
    .CC = "test@test.nl"
    .Subject = "SO " & TumbaSO.Value & " (PO " & Koopbrief.Value & ")"
    
    body1 = "Dear Team," & vbn & vbn & "Please dispatch this order" & IIf(Partial, " partially ", " ") _
      & "TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" & vbn & vbn & Address.Value & vbn
    
    body3 = vbn & "Kind regards," & vbn & vbn & cbRequestedby.Value _
            & vbn & vbn & "Alfa Laval Nijmegen B.V."
    
    If cbETA.Value = True And cbForwarder.Value = True Then
      body2 = vbn & "Forwarder: " & txtForwarder.Value & vbn & "Account: " & txtFWDAccount.Value _
              & vbn & vbn & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value & vbn
    
    ElseIf cbETA.Value = True Then
      body2 = vbn & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value & vbn
    
    ElseIf cbForwarder.Value = True Then
      body2 = vbn & "Forwarder: " & txtForwarder.Value & vbn & "Account: " & txtFWDAccount.Value & vbn
    End If
    
    .Body = body1 & body2 & body3
  End With
End Sub


Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
Solution
DanteAmor said:
Hi @flea80 : Thanks for posting on the forum.

I made some tweaks to simplify the code and also, of course, included the word "partilly".

Please try the following code:

VBA Code: 
Private Sub Createbtn_Click()
  Dim olApp As Outlook.Application
  Dim olEmail As Outlook.MailItem
  Dim body1 As String, body2 As String, body3 As String
  Dim vbn As Variant
 
  Set olApp = New Outlook.Application
  Set olEmail = olApp.CreateItem(olMailItem)
  vbn = vbNewLine           'Just to make it shorter
 
  With olEmail
    .BodyFormat = olFormatHTML
    .Display
    .To = "test@test.nl"
    .CC = "test@test.nl"
    .Subject = "SO " & TumbaSO.Value & " (PO " & Koopbrief.Value & ")"
  
    body1 = "Dear Team," & vbn & vbn & "Please dispatch this order" & IIf(Partial, " partially ", " ") _
      & "TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" & vbn & vbn & Address.Value & vbn
  
    body3 = vbn & "Kind regards," & vbn & vbn & cbRequestedby.Value _
            & vbn & vbn & "Alfa Laval Nijmegen B.V."
  
    If cbETA.Value = True And cbForwarder.Value = True Then
      body2 = vbn & "Forwarder: " & txtForwarder.Value & vbn & "Account: " & txtFWDAccount.Value _
              & vbn & vbn & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value & vbn
  
    ElseIf cbETA.Value = True Then
      body2 = vbn & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value & vbn
  
    ElseIf cbForwarder.Value = True Then
      body2 = vbn & "Forwarder: " & txtForwarder.Value & vbn & "Account: " & txtFWDAccount.Value & vbn
    End If
  
    .Body = body1 & body2 & body3
  End With
End Sub


Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
Click to expand...
Hi Dante,

Thank you for your reply.
In the mean time I was digging on line to find a solution and my code now looks like this:

VBA Code: 
Private Sub CreateSIbtn_Click()
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Dim defaultBody As String
Dim endBody As String

defaultBody = "Dear Team," & vbNewLine & vbNewLine _
        & "Please dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value

partialBody = "Dear Team," & vbNewLine & vbNewLine _
        & "Please create a PARTIAL shipment and dispatch this order TODAY using " & Deliverymethod.Value & " " & Incoterm.Value & " to:" _
        & vbNewLine & vbNewLine & Address.Value

ETABody = vbNewLine & vbNewLine & "ETA: " & cbDay.Value & " " & cbMonth.Value & " " & cbYear.Value

FwdBody = vbNewLine & vbNewLine & "Forwarder: " & txtForwarder.Value & vbNewLine & "Account: " & txtFWDAccount.Value

endBody = vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & cbRequestedby.Value _
          & vbNewLine & vbNewLine & "Alfa Laval Nijmegen B.V."


Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)

If TumbaSO.Value = "" Then
 
    MsgBox "Tumba SO is required", vbCritical
    Exit Sub
 
End If
 
If Koopbrief.Value = "" Then
 
    MsgBox "PO (koopbrief) is required", vbCritical
    Exit Sub
 
End If

If Deliverymethod.Value = "" Then
 
    MsgBox "Delivery method is required", vbCritical
    Exit Sub
 
End If

If Incoterm.Value = "" Then
 
    MsgBox "Incoterm is required", vbCritical
    Exit Sub
 
End If

If Address.Value = "" Then
 
    MsgBox "Addressfield is required", vbCritical
    Exit Sub

End If
   
If cbForwarder.Value = True Then GoTo Forwarder Else GoTo ForwarderNotChecked
Forwarder:
If txtForwarder.Value = "" Or txtFWDAccount.Value = "" Then
 
    MsgBox "Forwarder name and account is required", vbCritical
Exit Sub
ForwarderNotChecked:
   
End If

If cbETA.Value = True Then GoTo ETA Else GoTo ETANotChecked
ETA:
If cbDay.Value = "" Or cbMonth.Value = "" Or cbYear.Value = "" Then
 
   MsgBox "ETA date is required", vbCritical
    Exit Sub
ETANotChecked:
End If

MsgBox "E-mail created. Please check.", vbInformation, "Email created"

With olEmail
.BodyFormat = olFormatHTML
.Display
.To = "test@test.nl"
.CC = "test@test.nl"
.Subject = "SO " & TumbaSO.Value & " (PO " & Koopbrief.Value & ")"
'All checkboxes TRUE
    If cbETA.Value = True And cbForwarder.Value = True And cbPartial = True Then
.Body = partialBody & FwdBody & ETABody & endBody

'ETA
    ElseIf cbETA.Value = True And cbPartial = True Then
.Body = partialBody & ETABody & endBody

    ElseIf cbETA.Value = True And cbForwarder.Value = True Then
.Body = defaultBody & FwdBody & ETABody & endBody
   
    ElseIf cbETA.Value = True Then
.Body = defaultBody & ETABody & endBody

'Forwarder
     ElseIf cbForwarder.Value = True And cbPartial = True Then
.Body = partialBody & FwdBody & endBody

    ElseIf cbForwarder.Value = True Then
.Body = defaultBody & FwdBody & endBody

'Partial
    ElseIf cbPartial = True Then
.Body = partialBody & endBody

    Else
.Body = defaultBody & endBody

     End If

End With

'MsgBox "E-mail created. Please check.", vbInformation, "Email created"

End Sub

Still too long I guess, but for now it's working. I'm going through your code again to find the things I could use to improve, but you've helped me great so far!

Thnx!
 
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top