CC doesn't work when email = null (1 Viewer)

[luvsmel]

I have a VBA code that will send an email to managers and CC "students" email, but when the Student's email is null there is an error.

This is the code for the CC line.
.CC = Me.Student_Email

How can I write the VBA code where if there is a Student_Email it will insert it, otherwise it won't?

Thanks,
Mel M

This is my entire Event Procedure:
Private Sub cmdEmail_Click()

Call SendEmail

End Sub
Sub SendEmail()

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strmsg As String
Dim sqlString As String
Dim i As Integer
Dim rowColor As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim iStuName As String
Dim cMgrAdd As String

iStuName = Me.Student_Name

'DoCmd.SetWarnings False
cMgrAdd = DLookup("[Mgr_EmailAddress]", "[tblManagers]", _
"[Mgr_Name]='" & [Mgr_Name] & "'")

sqlString = "SELECT * FROM qryEmailClasses " & _
"WHERE qryEmailClasses.Reg_ID = " & Reg_ID & ";"
rs.Open sqlString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

strmsg = "<table border='1' cellpadding='3' cellspacing='3' style='border-collapse: collapse' bordercolor='#111111' width='800'>" & _
"<tr>" & _
"<td bgcolor='#7EA7CC'> <b>Epic Class</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>Class Date</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>Start Time</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>End Time</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>Building</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>Room</b></td>" & _
"</tr>"

i = 0

Do While Not rs.EOF
If (i Mod 2 = 0) Then
rowColor = "<td bgcolor='#FFFFFF'> "
Else
rowColor = "<td bgcolor='#E1DFDF'> "
End If

strmsg = strmsg & "<tr>" & _
rowColor & rs.Fields("Epic_Class") & "</td>" & _
rowColor & rs.Fields("Class_Date") & "</td>" & _
rowColor & rs.Fields("StartTime") & "</td>" & _
rowColor & rs.Fields("EndTime") & "</td>" & _
rowColor & rs.Fields("Building") & "</td>" & _
rowColor & rs.Fields("Room") & "</td>" & _
"</tr>"

rs.MoveNext
i = i + 1
Loop

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = "Managers need to provide the system login and temporary passwords to students before their last day of class to ensure they can login." & "<br>" _
& "<br>" _
& "It is recommended the student change their temporary password before training to reduce problems logging in." & "<br>" & "<br>" _
& "Attached: Parking Passes if requested." & "<br>" & "<br>" _
& strmsg

.To = cMgrAdd
.CC = Me.Student_Email
.Subject = "Training registration for " & iStuName
'.Send if you want to send it directly without displaying on screen
.Display
End With

Set olApp = Nothing
Set objMail = Nothing

End Sub

 

[Grumm]

You can replace the line

.CC = Me.Student_Email

With an IF to check if the student_email is not null.

I don't know if most people here will just tell you what to do or give the correct answer directly.

But this should work for you :

If Not IsNull(Me.Student_email) Then
.CC = Me.Student_Email
End If

 

[Cronk]

As can be seen, the CC property is a string and doesn't like Null.

An even simpler method to avoid the Null error is
.CC = Me.Student_Email & ""