Check to see if email address exists

[adrianscotter]

Hi guys

I'm still learning and I'm stuck! (Office Professional 2010, Windows 7 x64)

I can already send emails out as PDF files when an email address exists, however, I'm trying to validate whether or not an email address does actually exist. My problem is, there are four possible locations where an email address could be: MainEmail or Contact1Email or Contact2Email or Email (the last one - Email - is in a separate table). Four text boxes exist on the form that is being actioned (the form is based on a query to extract the relevant information) and are available to test for an email address being present. What I'm struggling with is ensuring that the 'To' box in Outlook always gets populated or Outlook chucks a hissy fit. I have been banging my head on my desk for more than 2 hours trying various IF Then Else and Do Loop plus a myriad of other ways but none are reliable, especially if no email address is present which can be the case.

Anyone? I would be most grateful for a prod in the right direction.

Thank you in advance.:banghead:

 

[vbaInet]

Use this to validate:

Len([[COLOR="Blue"]Field[/COLOR]] & vbNullString) > 0

 

[pbaldy]

In general

If Len(Me.TextboxName & vbNullString) > 0 Then

will test a control for both Null and a zero length string.

 

[adrianscotter]

Sorry guys, I can test for them being present, I can't ensure one of them goes into the 'To' field of Outlook, I didn't explain myself very well and English is my first language!!!

 

[pbaldy]

You haven't shown your code, but why not simply put any good address in the To field?

 

[vbaInet]

Just noticed your post Paul

If ... check first email... Then

    strEmail = Me.FirstEmail

ElseIf ...check second email... Then

   strEmail = Me.SecondEmail

ElseIf ... check third email... Then

   strEmail = Me.ThirdEmail

Else
    ... there's no e-mail address ...
End If

 

[adrianscotter]

You haven't shown your code, but why not simply put any good address in the To field?

Argggg! Because I'm too stupid for my own good! You know what it's like when you go round in circles, I should have stepped away, had a smoke and gone back to it with fresh eyes. Thanks for the prod, much appreciated.

 

[adrianscotter]

Just noticed your post Paul

If ... check first email... Then

    strEmail = Me.FirstEmail

ElseIf ...check second email... Then

   strEmail = Me.SecondEmail

ElseIf ... check third email... Then

   strEmail = Me.ThirdEmail

Else
    ... there's no e-mail address ...
End If

Thanks to you too vbaInet, I'd completely forgotten about ElseIf, I need beer!

Thanks to you both!

 

[pbaldy]

That code will only put a single address in the to field, so hopefully that's what you want. I'd probably build a string with all of them in it, but of course I don't know your requirements.

 

[adrianscotter]

That code will only put a single address in the to field, so hopefully that's what you want. I'd probably build a string with all of them in it, but of course I don't know your requirements.

It's just a little quoting database for fascia, soffits and guttering (in the UK) with original quotes and duplicated quotes if similar items are required by the next house in the street. There are Councils (similar to your Boro's in the US) that have streets and streets of houses, usually of four different types of house that need the work done. There are also private homeowners in those same streets that may or may not want their own home to have the new FSG installed at the same time so hence the possibility of up to four different email addresses or none at all. Have I confused you yet? Hell, I'm confused and I'm sitting in front of it.

 

[pbaldy]

My point is that if more than one of the four fields has an email address, that code will only send to the first it finds. If you want it sent to all of the addresses that are filled out, you'll need to concatenate them together:

strEmail = strEmail & Me.TextBoxName & ";"

In my experience Outlook ignores extraneous semi-colons so I don't worry about them.

 

[adrianscotter]

My point is that if more than one of the four fields has an email address, that code will only send to the first it finds. If you want it sent to all of the addresses that are filled out, you'll need to concatenate them together:

strEmail = strEmail & Me.TextBoxName & ";"

In my experience Outlook ignores extraneous semi-colons so I don't worry about them.

I'll give it all a go and let you know how I get on (or come back and ask more questions). Thanks again for all of your help.

 

[adrianscotter]

I'll give it all a go and let you know how I get on (or come back and ask more questions). Thanks again for all of your help.

Resultant code, all working.

If Me.MainEmail <> "" Then
stToName = Me.MainEmail
ElseIf Me.Contact1Email <> "" Then
stToName = stToName & ";" & Me.Contact1Email
ElseIf Me.Contact2Email <> "" Then
stToName = stToName & ";" & Me.Contact2Email
Else
stToName = "No_Email_Address"
End If

If stToName <> "No_Email_Address" Then blah, blah, blah...

 

[pbaldy]

All working? Does it send an email to multiple people if the email addresses are there? I suspect not.

 

[adrianscotter]

All working? Does it send an email to multiple people if the email addresses are there? I suspect not.

Hi Paul, sorry for the delay, been out all yesterday...

You're absolutely right, it doesn't. I'm not building the string of addresses correctly so I'll get onto that now and let you know.

And 5 minutes later, here it is...

If Me.MainEmail <> "" Then
stToName = Me.MainEmail
End If

If Me.Contact1Email <> "" Then
stToName = stToName & ";" & Me.Contact1Email
End If

If Me.Contact2Email <> "" Then
stToName = stToName & ";" & Me.Contact2Email
End If

If stToName = "" Then
stToName = "No_Email_Address"
End If

If stToName <> "No_Email_Address" Then

BLAH, BLAH , BLAH...

Thanks again Paul, much appreciated!

 

[pbaldy]

Happy to help!