Solved Sendobject PDF, cc field sometimes empty gives error

[andymartin3186]

Hi,
I have a button on my job form which emails a delivery note to the particular customer contact email address on the job.
I also have in my customers table a PODEmail field where I can add other people in as the CC section of the delivery note email. These are entered and seperated by a ;

The trouble is when a particular customer does not have any PODEmail email addresses stored, and only the main contact email is in the to field. Access brings an error because its trying to look for a string to place into the cc part of the operation, and the string is null I'm assuming.

I get a 94 invalid use of null error

Is there anything I can do so that when the string is null, it inserts something like "" into the cc field rather than null?

I tried

If IsNull(Me.PODEmail) Then PODEmailCC = ""

but it still errors.

 

[andymartin3186]

Ive tried using an Nz function and splitting the results into 2 different actions, one with a CC argument and one without. Still fails. I'm doing something stupid what is it?

Private Sub EmailQuotationBtn_Click()

    Dim CustomerName As String
    Dim CustomerEmail As String
    Dim QuotationEmailCC As String
    Dim QuotationRef As String
    Dim arr() As String
    
    Me.Quoted = 1
    Me.DateQuoted = Date
    Me.StatusID = 3
    
    Me.Refresh
    
    On Error GoTo errhandle
    
    arr = Split(Me.ContactName, " ")
    CustomerName = Me.ContactName
    CustomerEmail = Me.Email
    QuotationEmailCC = Me.QuotationCCEmail
    QuotationRef = "M" & Format(Me.JobID, "00000")
    
    If Nz(Me.QuotationCCEmail, "") = "" Then
  
    DoCmd.SendObject acSendReport, "Quotation", acFormatPDF, CustomerEmail, , , "Quotation " & QuotationRef, "Hi" & " " & arr(0) & "," & vbNewLine & "Your quotation " & QuotationRef & " is attached." & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Martronic Industrial Electronic Repair Ltd", True
    DoCmd.OpenQuery "TimeLogSentQuoteQ"
    
    Else
    
    DoCmd.SendObject acSendReport, "Quotation", acFormatPDF, CustomerEmail, QuotationEmailCC, , "Quotation " & QuotationRef, "Hi" & " " & arr(0) & "," & vbNewLine & "Your quotation " & QuotationRef & " is attached." & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Martronic Industrial Electronic Repair Ltd", True
    DoCmd.OpenQuery "TimeLogSentQuoteQ"
    
    End If
  
exitErr:
    Exit Sub
    
errhandle:
    If Err.Number = 2501 Then
        MsgBox "Sending Email Cancelled", vbCritical, "Email Quotation"
        
    Else
        MsgBox "Error (" & Err.Number & ") - " & Err.Description & " Occurred."
    End If
    Resume exitErr

 

[andymartin3186]

Update,
Changed the QuotationEmailCC variable type to Variant and it works.

 

[Minty]

I would have thought a simple

QuotationEmailCC = Nz(Me.QuotationCCEmail,"")

Would have solved the problem?

 

[andymartin3186]

Thank you for helping