Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

VBA - Dealing with Null when calling a subroutine

Avatar of fcoit
fcoitFlag for United States of America asked on
Microsoft AccessOutlook
2 Comments1 Solution410 ViewsLast Modified:
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
Avatar of Patrick Matthews
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers