DoCmd.SendObject or Case error

[eholtman]

Ok all done with all the enhancements for now and coming to one issue that I need to come to the great wizard...

Tables:
Open Text fields: Contact1 Contact2 Contact3.

Issues: Generating email, I am recieving error 2295 (Unknown message recipient(s). When Contact1, Contact2, Contact3 is null because of my code list that the following need to be included. There will be times that only 2 of the 3 are needed. I do not want it to cancel sending the email but simply move on if the field is null. What is hte easiest way... If statement, Case statement? Thanks and I am done for a couple of months at least.

Private Sub Check173_Click()
On Error GoTo ErrorHandler

    DoCmd.SendObject acSendNoObject, , , _
    To:=Me.Contact1 & ": " & Me.Contact2 "; " & Me.Contact3, bcc:="My.Email@123.com", _
    Subject:="Request for Service Receipt", _
    MessageText:="This email serves as an acknowledgement of service requested blah blah blah"
       
ForceExit:
Exit Sub

ErrorHandler:
Select Case Err.Number

Case 2295 'Trape Error 2295
MsgBox "Missing Contacts", , "Cancelled"
Resume ForceExit

Case Else
MsgBox Err.Description 'Will show every other error
Resume ForceExit

End Select

End Sub

 

[pbaldy]

If would be more appropriate than Case, but this is simpler:

To:=Me.Contact1 & ("; " + Me.Contact2) & ("; " + Me.Contact3)

 

[eholtman]

Thanks - That seems to be closer to what I need the only down side is that is requiring Me.Contact1 not to be null. I cant say that is always true. Ohh I feel like I am almost there.

 

[pbaldy]

Sounds like a design issue. Will there be no "To" in that case?

 

[grnzbra]

How about if you create the TO string separately so that it only shows the contacts that are there?

 

[johncallaghan]

Try something like this

to: iif(nz(Me.contact1),me.contact1 & ":","") & iif(nz(me.contact2),me.contact1 & ";","") ' etc

you may need to look up iif and nz

Cheers

John

 

[Bat17]

strTo = Me.Contact1 & ("; " + Me.Contact2) & ("; " + Me.Contact3) &""
if strTo = "" then exit sub ' no contacts!
if left(strTo, 1 )= ";" then strto = Replace(strTo, "; ","",,1) ' strip spare ; if no Contact1

Some thing like this should do it