Check Client has an Email address (1 Viewer)

[easygoingdave]

I know there is a simple answer, but i am a novice to Access and what will take me hours to find a solution will probably take this forum a couple of minutes to solve.
I currently have details of my clients in a table called tblSites, this tble includes a field with an email address. I currently produce a invoice which is automatically saved as a pdf and an email generated with the invoice attached. However, a few clients do not have email addresses (these are sent by post). My code (see below) will successfully produce the email with the attachment, but if the client does NOT have an email address in the tblSites it comes up with the "Invalid use of Null" error. What I want it to do is continue the process of just saving the invoice with a Msgbox stating "This Client does not have an email address etc etc.
I would be grateful if someone could spend a couple of minutes and provide the appropriate coding

thank you in advance

'Save Invoice as PDF and annotate Invoice Number, Date and site name

Dim StrFile As String
Dim slSQLString As String
Dim rsEmailAddress As Recordset
Dim slEmailAddress As String

StrFile = "C:\Redwatch Invoices\" & Forms!frminvoice!txtInvoiceNr.Value _
& "-" & Format(Date, "dd-mmm-yy") & "-" & Forms!frminvoice!cboSiteName & ".pdf"
DoCmd.OutputTo acOutputReport, "rptinvoice", acFormatPDF, StrFile

slSQLString = "SELECT * FROM tblSites WHERE SiteName = '" & Forms!frminvoice!cboSiteName & "'"
Set rsEmailAddress = CurrentDb.OpenRecordset(slSQLString, dbOpenDynaset, dbSeeChanges)

slEmailAddress = rsEmailAddress.Fields("emailAddress")


'Show user the Outlook message before sending
'Call the function SendEmailDisplayOutlook

Call SendEmailDisplayOutlook(slEmailAddress, "Redwatch Invoice", "I would be grateful if the attached invoice is settled within 60 days", StrFile)

End Sub

Public Function SendEmailDisplayOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
ReportPath As String)

'Get Outlook using the "New" Keyboard
Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem

'Creat a new email object
Set olMailItem = olApp.CreateItem(0)
'Add the To/Subject/Body/Attachments to he message then display the message
With olMailItem
.To = MsgTo
.Subject = MsgSubject
.Body = MsgBody
.Attachments.Add ReportPath
.Display
End With

Set olMailItem = Nothing
Set olApp = Nothing

End Function

 

[David R]

[COLOR="Red"]If IsNull(rsEmailAddress.Fields("emailAddress")) Then
   msgbox "This Client does not have an email address etc etc."
Else[/COLOR]
   slEmailAddress = rsEmailAddress.Fields("emailAddress")
   'Call the function SendEmailDisplayOutlook

   Call SendEmailDisplayOutlook(slEmailAddress, "Redwatch Invoice", "I would be grateful if the attached invoice is settled within 60 days", StrFile)
[COLOR="Red"]End If[/COLOR]

 

[easygoingdave]

David
Many thanks for your prompt reply. I will add the lines tomorrow and get back to you.

 

[easygoingdave]

David, worked a treat many thanks for your time. Happy New Year