Multiple Email Addresses in CC - April 30th deadline!

  • Thread starter Thread starter wkaupert
  • Start date Start date
W

wkaupert

I am trying to carbon copy multiple email addresses and the email addresses
are based on values in 4 fields located on the form. An account can have 1
to 4 email addresses. One issue I'm having is the "Invalid use of Null"
error I get when there isn't a second email address for an account. The
other issue I'm having is I can't carbon copy more than one email address. I
need to be able to copy Email2, Email3, and Email4 also understanding any of
these may have a null value. Please help! I'm suppose to launch the email
notification process on Friday!

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stTo As String
Dim stcc As String
Dim stcc2 As String
Dim stSubject As String
Dim stText As String
Dim errLoop As Error

stSubject = "Urgent: Information Regarding Past Due Premium"
stText = "Please review the attached file as it contains details..."

Recordset.MoveFirst

Do Until Recordset.EOF

Select Case Me!DeliveryMethod
Case "Y"
stTo = Me.Email1.Value
stcc = Me.Email2.Value
stcc2 = Me.Email3.Value
stcc3 = Me.Email4.Value

DoCmd.SendObject acSendReport, "PastDuePremiumNotification",
acFormatRTF, stTo,?-Not sure what to put here-?, , stSubject, stText, 0
End Select

Recordset.MoveNext

Loop

Exit Sub

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click
End Sub
 
Firstly, SendObject is known to be unreliable in loops.

As for your code, you can't set a Dim as String and then possibly pass Nulls
to it. You'd have to define the Dims as Variant in this case. As for your
code you'd need to do something more along the lines of

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stTo As String
Dim stcc As String
Dim sEmail2 As Variant
Dim sEmail3 As Variant
Dim sEmail4 As Variant
Dim stSubject As String
Dim stText As String
Dim errLoop As Error

stSubject = "Urgent: Information Regarding Past Due Premium"
stText = "Please review the attached file as it contains details..."

Recordset.MoveFirst
Do Until Recordset.EOF
Select Case Me!DeliveryMethod
Case "Y"
stTo = Me.Email1.Value
sEmail2 = Me.Email2.Value
sEmail3 = Me.Email3.Value
sEmail4 = Me.Email4.Value

If IsNull(sEmail2) = False Then
stcc = sEmail2 & ";"
End If
If IsNull(sEmail3) = False Then
stcc = stcc & sEmail3 & ";"
End If
If IsNull(sEmail4) = False Then
stcc = stcc & sEmail4 & ";"
End If

DoCmd.SendObject acSendReport, "PastDuePremiumNotification", _
acFormatRTF, stTo, stcc, , stSubject,
stText, 0
End Select

Recordset.MoveNext
Loop

Exit Sub

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click
End Sub


but what Recordset are you using? Why are you using a Case statement if
there is only one value you are using, just use an if statement instead.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
And here is my version....

Note that with a recordset, you cannot use "ME." to refer to the field in
the query (which is where the CC addresses would be coming from.... right??? )

And you only need one "Exit Sub"

This is untested.......

'-----------beg code-------------------------
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim d As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Dim stTo 'Variant
Dim stcc 'Variant
Dim stSubject 'Variant
Dim stText As String
Dim errLoop As Error
Dim i As Integer ' loop counter

stSubject = "Urgent: Information Regarding Past Due Premium"
stText = "Please review the attached file as it contains details..."

Set d = CurrentDb

'to open a recordset, you need to use a saved query
'or a SQL string... examples:

'Set rs = d.OpenRecordset("qryMyQuery")

'or

sSQL = "SELECT Mail1, Mail2, Mail3, Mail4 FROM tblMail;"
Set rs = d.OpenRecordset(sSQL)


If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

Do Until rs.EOF
'clear variables
stTo = Null
stcc = Null

Select Case Me!DeliveryMethod
Case "Y"
stTo = rs!Email1

'loop thru remaining email addresses
For i = 2 To 4
If Not IsNull(rs("Email" & i)) Then
stcc = rs("Email" & i) & ";"
End If
Next i

'remove last ";"
If Len(Trim(stcc)) > 0 Then
stcc = Left(stcc, Len(stcc) - 1)
End If

'send it
DoCmd.SendObject acSendReport, "PastDuePremiumNotification", _
acFormatRTF, stTo, stcc, , stSubject,
stText, 0

End Select

rs.MoveNext

Loop
Else
MsgBox "No records"
End If

'clean up
rs.Close
Set rs = Nothing
Set d = Nothing

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click
End Sub
'-----------end code-------------------------


HTH
 
I could be wrong but your code

For i = 2 To 4
If Not IsNull(rs("Email" & i)) Then
stcc = rs("Email" & i) & ";"
End If
Next i

Should it not be

For i = 2 To 4
If Not IsNull(rs("Email" & i)) Then
stcc = stcc & rs("Email" & i) & ";"
End If
Next i

Otherwise, it will only retain the value for i = 4

Also, from my experience, you do not need to remove the last ; It doesn't
hurt one way or another.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Yes, you are right. :)

Access had a melt down for a few minutes and I had to retype the code
several times. I did have it right at one point....... but I *did* say it was
untested :(


RE: removing the last ";". It is a habit from creating criteria strings on
the fly. I haven't tested it, but you are probably right.

Good catches. Thanks...
 
Back
Top