Email Report using Send Object and recordset

[kellyfirl1533]

I have a command button on a form that opens an (outlook) email with a correlated report attached. All works well, except for the cc address. In most instances there is no cc designate to send to, so most of the records in the record set are empty - which causes an error. I have tried so many, many different things to try to get my code to ignore empty fields. Any suggestions. The code follows:
Private Sub btnSendEmailReport_click()
On Error GoTo Err_btnSendEmailReport_Click

Dim MyBody As TextStream
Dim Rst As DAO.Recordset
Dim AppOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim StrDocname As String
Dim StrEmail As String

Set AppOutlook = CreateObject("Outlook.Application")
Set MailOutlook = AppOutlook.CreateItem(olMailItem)

Set Rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBudgetsFaculty.BudgetsFacultyEmployee_Number, tblBudgetsFaculty.BudgetsFacultyMonthlyEmail, tblBudgetsFaculty.BudgetsFacultyMonthlycc, tblBudgetsFaculty.BudgetsFacultyPreferredName, tblBudgetsFaculty.BudgetsFacultyAccountName, tblBudgetsFaculty.BudgetsFacultyMonthlyRep, tblBudgetsFaculty.BudgetsFacultyFY From tblBudgetsFaculty WHERE (((tblBudgetsFaculty.BudgetsFacultyFY)= '17') OR ((tblBudgetsFaculty.BudgetsFacultyFY) = '00')) AND ((tblBudgetsFaculty.BudgetsFacultyMonthlyRep)='yes ')")

StrDocname = "rptFacultyOrderHistoryForEmail"

Rst.MoveFirst
Do While Rst.EOF = False

If (Rst!BudgetsFacultyMonthlyRep) <> "Yes" Then
GoTo skip_email
End If

DoEvents
DoCmd.OpenReport StrDocname, acViewPreview, , "BudgetsFacultyEmployee_Number = " & Rst!BudgetsFacultyEmployee_Number, acHidden
DoCmd.SendObject acSendReport, StrDocname, acFormatPDF, Rst!BudgetsFacultyMonthlyEmail, Rst!BudgetsFacultyMonthlycc, , _
"Budget Report for: " & Rst!BudgetsFacultyAccountName & " as of " & Date, _
"Dear " & Rst!BudgetsFacultyPreferredName & ":" & vbNewLine & vbNewLine & _
"Attached is a budget report for the account above." & vbNewLine & vbNewLine & _
"If you have any questions, please let me know." & vbNewLine & vbNewLine & _
"Best-" & vbNewLine & _
"Colleen", True
DoCmd.Close acReport, StrDocname, acSaveNo

skip_email:
Rst.MoveNext

Loop
Rst.Close
Set Rst = Nothing

Exit_btnSendEmailReport_click:
Exit Sub

Err_btnSendEmailReport_Click:
MsgBox Err.Description
Resume Exit_btnSendEmailReport_click
End Sub

[jforbes]

You might try using Nz() around the BudgetsFacultyMonthlycc field, kind of like this:

DoCmd.SendObject acSendReport, StrDocname, acFormatPDF, Rst!BudgetsFacultyMonthlyEmail, Nz(Rst!BudgetsFacultyMonthlycc, ""), , _
 "Budget Report for: " & Rst!BudgetsFacultyAccountName & " as of " & Date, _
 "Dear " & Rst!BudgetsFacultyPreferredName & ":" & vbNewLine & vbNewLine & _
 "Attached is a budget report for the account above." & vbNewLine & vbNewLine & 

When using OpenRecordset, if a field is stored as a Null it will resolve to a Null when using Rst!BudgetsFacultyMonthlycc all by itself. Most functions will not take a Null as a parameter. By using an Nz(), you can pass an Empty String instead of a Null. This post explains it better: What is the difference between "" and Null?

This is just a guess of what is going on. If this doesn't fix it, then let us know the error you are experiencing and on what line of code you are experiencing it on.

[kellyfirl1533]

Oh my ... I swear I did exactly what you suggested using the Nz function! I just tested it and it works just dandy. I am totally exasperated ... I've been working on this for a week now. <sigh>

Sorry for the intrusion. Have a great weekend!

[jforbes]

Getting a good conceptual grasp on Nulls and Empty Strings can be exasperating. Glad you got things working.