Emailing to multiple people (1 Viewer)

[LHolden]

Hi all,

I have a report which users of the database need to be able to email. I have the SendObject all set up, minus one little problem I'm having. The e-mails that the report has to be sent to are entered into the database, and there are up to 4 e-mails that the report may have to go to at one time.

There are 4 fields labeled Email, Email2, Email3, and Email4. I've been trying to work a Switch() function into the TO: area in the VBA, but I can't seem to get it to work. Currently, I have the function set up like this:

Switch([Email]=Not Null And [Email2]=Null, [Email], [Email2]=Not Null And [Email3]=Null, [Email] & "; " & [Email2]...)

It goes on to also include Email3 and Email4, but when I run it I either get a blank to field, or the debugger comes up and tells me that the Sub or Function [Email4] is not defined.

Any help is greatly appreciated, thanks.

Edit: No longer getting the undefined sub or function popup, but I am still getting a blank To:. I tried changing it to simply

[Email] & "; " & [Email2] & "; " & [Email3] & "; " & [Email4]

but Outlook refuses to accept an e-mail that is blank, so that doesn't work either.

 

[DavidAtWork]

Can you post your actual email code and details of any variables that you might pass to the emailing function
David

 

[LHolden]

Sure, I'm using the SendObject command, so my code currently reads like this:

DoCmd.SendObject acReport, "", "PDFFormat(*.pdf)", [Email] & "; " & [Email2] & "; " & [Email3] & "; " & [Email4], "", "", "First Quarter Invoice", "", True, ""

There are no variables other than the fact that there aren't ALWAYS 4 emails. Sometimes there is only 1, but if there are 4, the e-mail has to be sent to all 4.

 

[DavidAtWork]

I think it will be failing if one or more of the recipients is empty, try declaring a string variable ie Dim emailRecips As String
then before you send the mail, test for the existance of each of the 4 possible recipients like this:
emailRecips = "" to initialise it
If Len()>1 Then: emailRecips = emailRecips & [Email]
If Len([Email2])>1 Then: emailRecips = emailRecips & ";" & [Email2]
If Len([Email3])>1 Then: emailRecips = emailRecips & ";" &[Email3]
If Len([Email4])>1 Then: emailRecips = emailRecips & ";" [Email4]
Now you can send the mail:
If Len(emailRecips) > 1 Then
DoCmd.SendObject acReport, "reportName here", "PDFFormat(*.pdf)", emailRecips, , , "First Quarter Invoice", , True)
Else: Msgbox "There are no recipients for this email",vbOKonly
End If

You need to specify the name of the report, also not necessary to include the trailing arguments when they are not used and any other unused arguments leave blank, don't use ""
David

 

[spikepl]

( + "; ") & ([Email2] + "; ") & ([Email3] + "; ") & [Email4]

should do the trick

BTW:

[CODE]Switch([Email]=Not Null And [Email2]=Null, [Email], [Email2]=Not Null And [Email3]=Null, [Email] & "; " & [Email2]...)[/CODE]

Null is NOT a value and therefore you cannot use = as a comparison operator

In VBA the test for whether a variable is null or not is:

IsNull(myVariable), whcih yields either True or False

in SQL, the corresponding test is

myField IS NULL or myField NOT IS NULL

 

[LHolden]

David, You sir are a life saver. Thanks for the knowledge spike, I'm new to VBA so that's nice to know

 

[pr2-eugin]

Hello LHolden, David has provided the code you need to go around.. However, I just have a very small suggestion.. Hope David doesn't mind.. The Len() function will return Null not a Boolean if any of the values is a Null.. So to catch Null and Empty Strings I would suggest

Dim emailRecips As String
emailRecips = "" ' to initialise it
If Len( [COLOR=Red]& vbNullString[/COLOR])>1 Then: emailRecips = emailRecips & [Email]
If Len([Email2] [COLOR=Red]& vbNullString[/COLOR])>1 Then: emailRecips = emailRecips & ";" & [Email2]
If Len([Email3] [COLOR=Red]& vbNullString[/COLOR])>1 Then: emailRecips = emailRecips & ";" &[Email3]
If Len([Email4] [COLOR=Red]& vbNullString[/COLOR])>1 Then: emailRecips = emailRecips & ";" [Email4]
[COLOR=Green]' Now you can send the mail:[/COLOR]
If Len(emailRecips) > 1 Then
DoCmd.SendObject acReport, "reportName here", "PDFFormat(*.pdf)", emailRecips, , , "First Quarter Invoice", , True)
Else: Msgbox "There are no recipients for this email",vbOKonly
End If
[/QUOTE]