Avatar of fcoit
fcoitFlag for United States of America asked on

VBA - Dealing with Null when calling a subroutine

Hello experts,

I have a subroutine that I need to call assining parameters from a table.  The problem is that when the fields are null I got an error message how can I deal with this.  When I call the subroutine from the Immediate window I got no error.  The error comes when dealing with Nulls.

Here is the code that calls the subroutine, as well as the subroutine.  Thank you.
SendEmail rs!Recipient, rs!Sender, rs!Body, rs!Subject, rs!CC, rs!BCC
Private Sub SendEmail(Optional strEmailAdd As String, Optional strFrom As String, _
                      Optional strBody As String, Optional strSubject As String, _
                      Optional strCC As String, Optional strBCC As String)
On Error GoTo errHandler
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookFrom As Outlook.MailItem
Dim objOutlookInbox As Outlook.MAPIFolder
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. This can be a variable or pulled form a table if desired.
If Len(strEmailAdd) > 0 Then
    Set objOutlookRecip = .Recipients.Add(strEmailAdd)
    objOutlookRecip.Type = olTo
End If
' Set sender
.SentOnBehalfOfName = strFrom
' Set CC
If Len(strCC) > 0 Then
    .CC = strCC
End If
' Set BCC
If Len(strBCC) > 0 Then
    .BCC = strBCC
End If
' Set the Subject, Body, and Importance of the message.
.Subject = strSubject
.HTMLBody = strBody
.Importance = olImportanceHigh  'High importance
' Add attachments to the message.
'Set objOutlookAttach = .Attachments.Add(strAttach)
' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
        If Not .Recipients.ResolveAll Then
            For Each objOutlookRecip In .Recipients
                If Not objOutlookRecip.Resolved Then
                    MsgBox "Error on e-mail name " & objOutlookRecip & ". I will open the e-mail so you can correct it before you send it."
                End If
        End If
    End With
    Set objOutlook = Nothing
    Exit Sub
    Select Case Err
        Case Else
            MsgBox "Error Number: " & Err.Number & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"
        GoTo exitHere
    End Select
End Sub

Open in new window

Microsoft AccessOutlook

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

Change your call to:

SendEmail Nz(rs!Recipient,""), Nz(rs!Sender,""), Nz(rs!Body,""), Nz(rs!Subject,""), Nz(rs!CC,""), Nz(rs!BCC,"")

Then make sure that SendMail knows what to do when any of those arguments come in as zero-length strings...
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck