Macro That Sends an Email To Certain Addresses Based on Check/Unchecked Check Boxes

[jmkonie]

Ok, this is my first post. I have been able to use the forum to fix my issues with VB until this one.

Basically I would like a macro to decide which email, subject, and body content based on whether or not a check box is checked.

For instance:
Checkbox checked -> sends email to abc@abc.com with subject "abc has a new email" and body "abc test"
Checkbox unchecked -> send email to 123@abc.com with subject "123 has a new email" and body "123 test"

Here is what I have so far. I have the email function down, no issues, but when I introduce the If Then it throws an error and I guess I am not good at debugging yet so I cannot figure it out.

Sub Button()


Range("K41").Value = Environ("UserName")
' Inputs the person's username into cell K41.


Range("AE41").Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
' Inputs the month, date, year, and exact time the document was approved in cell 41


Dim Path As String
Dim filename As String
filename = Range("AE1")
Path = "F:"
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled


Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments

If Range("B37") = True Then
' So if I have the check box checked...


With OutLookMailItem
.To = "abc@abc.com"
' Who the email will be sent to


.Subject = "abc has a new email"
' What's written in the subject line


.Body = "abc test"
' What's written in the body of the email


myAttachments.Add "F:" & Sheets(1).Range("AE1").Value & ".xlsm"
' Automatically finds the file name based on what we named it earlier


Else


With OutLookMailItem
.To = "123@abc.com"
' Who the email will be sent to


.Subject = "123 has a new email"
' What's written in the subject line


.Body = "123 test"
' What's written in the body of the email


myAttachments.Add "F:" & Sheets(1).Range("AE1").Value & ".xlsm"
' Automatically finds the file name based on what we named it earlier


.Display
' This shows the email before sending it
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End If


End Sub

 

[Mark80]

Hi,

I've not tried your code, but just looking at it I think there are a couple of things which might help it to work:
(1) The first "With" statements does not have a corresponding "End With". You should add one in the line above "Else"
(2) The If section does not have a ".Display" statement, but the Else statement does
(3) The two lines of "Set OutLookMailItem = Nothing" and "Set OutLookApp = Nothing" should be below the "End If" statement.

 

[yky]

The first part of IF is missing "End With". Maybe you can put "With OutLookMailItem" and "End With" outside the IF statement.

 

[jmkonie]

Got it. I added the "end with" after the first "with" statement, added the .Display statement, and added the "Set OutLookMailItem = Nothing" and "Set OutLookApp = Nothing" to the first "With" statement, kept them before the End If and it works great.

Now I need to figure out how to add some code to lock and unlock certain cells in the protected sheet.