Email TO: Handling Blank Fields

Take into account this is off the cuff but should work. Although I don't know how you intend on implementing this the Dlookup function sure can slow things down I usually try to avoid using it whenever possible. Maybe you can open a query then check it to see what the values are and if they exist. that would be much quicker but similar for when you want to check a field. Let us know how it goes.

I still get the message "Unknown message recipient(s) the message was not sent.

Thanks though.

You may be storing an empty string rather than null. Try this ...



Also you need to double check that the separator your system uses is ',' and not ';'.

That worked and you were correct the field is empty. This works perfectly now.

Sorry spoke to soon. The one I tested had all the field with an email address in it. I tested another one that had the first two but the third was blank and I received the error message again. Can you help?

At the end of the code put

Debug.Print stEmailTo

and open the immediate window to see the result.

This will print out exactly what is in the string.

Look for empty white spaces, etc.

What Mary stated was correct you need to verify that using a comma verses a semicolon between email addresses is appropriate. Then as she also stated at the very end of the code before the "end sub"


Just to point this out from the help file:

Quote: Originally Posted by Help If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null

Quote: Originally Posted by Denburt What Mary stated was correct you need to verify that using a comma verses a semicolon between email addresses is appropriate. Then as she also stated at the very end of the code before the "end sub" Code: ( text ) stEmailTo = left(stEmailTo,len(stEmailTo )-2) debug.print  stEmailTo Just to point this out from the help file:

My thinking is that the empty records are not empty. If they contain white space then the DLookup won't return a null.

You always make such valid points Mary especially since the record he is looking for "Loc No" may be there and as such a record would be returned but the field may or may not contain any results, which could be as you said white space or returned as an empty field or a Null.

So clloyd what type of field are you storing the email addresses in?

Keep in mind I am not an expert and am a little confused. I did the Print Debug that was mentioned but do not know what was meant by open immediate window.

I store the information as follows: Each location has up to five contact people each of which have Name, Title, Phone and Email address. It is a simple text field. For some locations we only have one contact and for others we could have five. All are stored in the same record. If I run this code on a location that has all the email fields in a record presenting an email address the code runs fine but any record that has one field empty it gives me the unknown recipient message. It is also rare and a keying error but there have been cases where fields 1 and 2 have an email address 3 does not but 4 does. Thanks for all your help.

I found the immediate window. Sorry about that.

I added the Code Debug.Print stEmailTo and opened the Immediate Window but nothing appeared in it.

Quote: Originally Posted by clloyd I added the Code Debug.Print stEmailTo and opened the Immediate Window but nothing appeared in it.

Did you put it after this line

stEmailTo = Left(stEmailTo, Len(stEmailTo) - 2)

O.K. I just noticed Me![Loc No] is that a number or string? In your Dlookup you have it designated as a string but if the datatype in the table is a number then you need a simple correction to the DLookup function.

For a Number leave out the quotes.
Debug.print DLookup("[Contact Email]", "[Company]", "[Loc No] = " & Me![Loc No] )


O.K. try debugging from the top down. First make sure you have an email address in the first field at least and at least one of the other fields will be empty.

Try the following and if there is an email address in that field then it should show up in the immediate window:
Debug.print DLookup("[Contact Email]", "[Company]", "[Loc No] = '" & Me![Loc No] & "'")

If there is an email in this field and it doesn't print out in the immediate window then check some of the other items in that string to be sure they are valid.

(For example)
Debug.print Me![Loc No]

If this produces the desired result then check all fields and table names in your Dlookup function.

Ok I am getting somewhere. The record I chose had a entry in the first two email fields the other three did not have email addresses. The immediate window listed the first email address only not the second. As I mentioned before the code is fine if all five email fields have data it does not like finding a blank field. Something is missing in the code that will strip the remaining empty fields out of the TO section. I just can't see it. Thanks again.

Quote: Originally Posted by clloyd Ok I am getting somewhere. The record I chose had a entry in the first two email fields the other three did not have email addresses. The immediate window listed the first email address only not the second. As I mentioned before the code is fine if all five email fields have data it does not like finding a blank field. Something is missing in the code that will strip the remaining empty fields out of the TO section. I just can't see it. Thanks again.

You must be storing empty white space in the field even though there isn't an email address. Try adjusting the code as follows:

Did not work. I went into the record and made sure that there was nothing in the fields that are blank and it still will not ignore the blank fields. Still getting unknown recipient message was not sent.

Quote: Originally Posted by clloyd Did not work. I went into the record and made sure that there was nothing in the fields that are blank and it still will not ignore the blank fields. Still getting unknown recipient message was not sent.

OK first put some debug statements in the code as follows:



This will show in the immediate window the value being found in each lookup.

Let me know what happens.

It came back in the immediate window like this:

Contact Email 1: someone1@somecompany.com
Contact Email 2: someone2@somecompany.com
Contact Email 3:

I changed the email addresses for confidentiality purposes. Notice the Contact Email 3 is a blank record.

We are really having to work for this one.

Quote: Originally Posted by clloyd It came back in the immediate window like this: Contact Email 1: someone1@somecompany.com Contact Email 2: someone2@somecompany.com Contact Email 3: I changed the email addresses for confidentiality purposes. Notice the Contact Email 3 is a blank record. We are really having to work for this one.

Can I just confirm that you are getting in stmailto ...

someone1@somecompany.com

instead of ...

someone1@somecompany.com,someone2@somecompany.com

Yes they appear to have run together.

Quote: Originally Posted by clloyd Yes they appear to have run together.

What do you mean they appear to have run together ?

The record has an email address in the first two fields and the third was blank (empty). In the immediate window it showed as follows:

Contact Email 1: someone1@somecompany.com
Contact Email 2: someone2@somecompany.com
Contact Email 3:

Is that what you were asking?

Quote: Originally Posted by clloyd The record has an email address in the first two fields and the third was blank (empty). In the immediate window it showed as follows: Contact Email 1: someone1@somecompany.com Contact Email 2: someone2@somecompany.com Contact Email 3: Is that what you were asking?

No what I want to know is exactly what shows in the immediate window if you run

Debug.Print stmailto

That is what shows up except I had to change the names because of confidentiality for the names. It is Contact 1 email hard return Contact 2 hard return and a blank record. The empty bland record is what is causing the problem.

Quote: Originally Posted by clloyd That is what shows up except I had to change the names because of confidentiality for the names. It is Contact 1 email hard return Contact 2 hard return and a blank record. The empty bland record is what is causing the problem.

What you should be getting is

contact1@email.com,contact2@email.com

You shouldn't be getting any hard returns for stmailto. It should be just one continuous string.

My example is exactly what I received. It did have hard returns.

it sounds like there is a problem with the data entered in the database. Take a sample record and delete the contents of the contact email fields. Then manually retype the email addresses. It sounds like you have hard return characters being stored in these fields. If I'm right then when you type in the email addresses after deleting the content the record should output correctly.

Mary

I did as you asked. There are no hard returns.

There needs to be something else in the code to handle an empty (null) record. As I have mentioned before the code works beautifully if there is data in all five fields. I only doesn't work if I hit a blank (null) field. It has something to do with a null records.

A null record shouldn't put in a hard return. OK I'm going to play around with the code a bit and get back to you.

Mary

Thanks so much for all your help.

OK this shouldn't work any differently to the last one but its worth trying another way of doing it.

Make sure to add the Microsoft DAO library in the reference list and test the following code.

It game me a Compile Error-Expected End Sub and the cursor appeared before the Sub tem()

I am using this before other code I need to fill in the CC and Body text could that be the problem?

What I was suggesting was putting this on the form module as a separate sub procedure for testing purposes. We can work out how to incorporate it into your code if it works.

I must be doing something wrong because it locked up my computer. I put the code on my module for that form but must not be calling it on click correctly. Any suggestions.

In the editor window. Highlight the code from sub temp to end sub and then just click the run button. It will run the highlighted procedure