When Checkbox clicked > Send mail

ING Hammer

New Member
Joined
Apr 12, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am very new to VBA and trying to find out how to make this work.

So basically I have a Checkmark from D2 to unlimited.
When Checkmark is clicked > Display Message

I have the following code written so far but I don't know how to display my message without having to run the simulation.. I don't know how to pass the function to open my message windows with an if command.
This is my code so far:

Sub CheckBox_Date_stamp() is just when Checkbox gets clicked, Date is going to be passed in the next cell and in the second cell a "yes" or "no" text.
I am sorry if this looks like a mess for you, but like I mentioned I am very new to this (started yesterday). Please be patient with me >.<

Excel Formula:
Sub CheckBox_Date_stamp()

Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

With xChk.TopLeftCell.Offset(, 1)
    If xChk.Value = xlOff Then
        .Value = ""
    Else
       .Value = Date
    End If
    
    With xChk.TopLeftCell.Offset(, 2)
    If xChk.Value = xlOff Then
        .Value = "Nein"
    Else
       .Value = "Ja"
    End If
    
End With
End With
End Sub

Sub Email_From_Excel_Basic()

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

emailItem.to = "Test@test,com"
emailItem.Subject = "Test"
emailItem.Body = "Test Text"

'emailItem.Send
emailItem.Display

Set emailItem = Nothing
Set emailApplication = Nothing

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Maybe the subject is not correct.
It should not send the Mail, it should open the message box
 
Upvote 0
Hello, i solved it myself. This was really easy, I thought it would be wrong because the debuger told me something is wrong.
Actually there wasn't anything wrong, the makro was just not assigned.

VBA Code:
Sub CheckBox_Date_stamp()

Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

With xChk.TopLeftCell.Offset(, 1)
    If xChk.Value = xlOff Then
        .Value = ""
    Else
       .Value = Date
    End If
    
    With xChk.TopLeftCell.Offset(, 2)
    If xChk.Value = xlOff Then
        .Value = "Nein"
    Else
       .Value = "Ja"
        emailItem.Display
        emailItem.to = "Test@test.com"
        emailItem.Subject = "Test"
        emailItem.Body = "Test Text 1"
        emailItem.Body = "Test Text 2"
    End If
    
End With
End With
End Sub
 
Upvote 0
Okay, I am now struggling with new lines

So I tried different ways which I found on google while researching.

Can somebody tell me how I can make multiple lines in my email body??
VBA Code:
Sub CheckBox_Date_stamp()

Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

With xChk.TopLeftCell.Offset(, 1)
    If xChk.Value = xlOff Then
        .Value = ""
    Else
       .Value = Date
    End If
    
    With xChk.TopLeftCell.Offset(, 2)
    If xChk.Value = xlOff Then
        .Value = "Nein"
    Else
       .Value = "Ja"
        emailItem.Display
        emailItem.to = "Test@test.com"
        emailItem.Subject = "Test"
        emailItem.Body = "Test Text 1" & "<br>" & "Next line"
    End If
    
End With
End With
End Sub

I found out that <br> is only used by html .. I also tried the following thing:

Code:
        emailItem.Display
        emailItem.to = "Test@test.com"
        emailItem.Subject = "Test"
        emailItem.Body = "Test Text 1" & vbLf  & "Next line"

This worked out for me :)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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