Code to send email to group of contacts

K

KLR

I am using the following code (from Ron Hughes code to send email to
all database at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='CodeSendEMailToAll.mdb'.
When clicking the command button, I click on Yes when prompted to
create a new table but at the end the error message "Invalid use of
Null" is displayed. Can anyone help?

Private Sub SendEmail_Click()

Dim strDocName As String
strDocName = "qmt_ucas_emails"
DoCmd.OpenQuery strDocName

On Error GoTo ErrHandle

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubj As String
Dim strText As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("t_Account Management data",
dbOpenSnapshot)

If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no
EMail Addresses listed!"
GoTo ErrExit
End If

With rst

.MoveFirst

strAddress = .Fields("UCASEmail").Value
strBCC = strAddress

.MoveNext

Do While .EOF = False
strAddress = .Fields("UCASEmail").Value
strBCC = strBCC & "; " & strAddress
.MoveNext
Loop
End With

strTo = ""
strCC = ""
strBCC = strBCC
strSubj = ""

strText = Chr$(13) & Chr$(13) & "" & Chr$(13) & ""
'Chr$(13) will insert a blank line in the subject of your EMail.
'Anything between the quotes will be inserted. You can edit, as
required before sending
'If you want the subject to be blank, just put the quotes, with
nothing between them

DoCmd.SendObject acSendNoObject, , , strTo, , strBCC, strSubj,
strText, True

ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.description
Resume ErrExit
Resume

End Sub
 
K

KLR

Thanks - I've made the amendments requested but now get "Unknown
message recipient(s). Message not sent".

I don't want it to send a message though - I want to see an Outlook
message dialog box, with To field completed with all the contacts from
my Make Table, rest of the message box blank.

Any ideas why this new error is occurring?
 
K

KLR

Well my code now reads as below but still getting the 'Unknown message
recipients' message popping up! You are correct in that I was not
intending to populate the Bcc field.

Any further help would be greatly appreciated!

KLR

Private Sub SendEmail_Click()

Dim strDocName As String
strDocName = "qmt_ucas_emails"
DoCmd.OpenQuery strDocName

On Error GoTo ErrHandle

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String 'this creates the Email addresses
Dim strTo As String 'this is needed to populate the TO block
on the EMail form
Dim strCC As String 'this is needed to populate the CC block
on the EMail form
Dim strBCC As String 'this is needed to populate the BCC block
on the EMail form
Dim strSubj As String 'this is needed to populate the SUBJ
block on the EMail form
Dim strText As String 'this is needed to populate a portion of
the message text block

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("t_Account Management data",
dbOpenSnapshot)

If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no
EMail Addresses listed!"
GoTo ErrExit
End If

With rst
.MoveFirst
Do While Not .EOF
strAddress = Nz(.Fields("UCASEmail").Value, "")
If strAddress <> "" Then strBCC = strAddress & "; "
.MoveNext
Loop
End With

strTo = ""
strCC = ""
strBCC = strBCC
strSubj = ""
strText = ""

DoCmd.SendObject acSendNoObject, , , , , strBCC, , , True

ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.description
Resume ErrExit
Resume

End Sub
KLR said:
Thanks - I've made the amendments requested but now get "Unknown
message recipient(s). Message not sent".
Click to expand...

I'm not sure if Outlook is freaking out about empty addresses you might
be populating ("; ; ;"), or if you actually have bad addresses in your
data. We can rule out the former. Also, the loop can be tidied up
considerably.

Replace your _With rst_ block as follows:

With rst
.MoveFirst
Do While Not .EOF
strAddress = Nz(.Fields("UCASEmail").Value,"")
If strAddress <> "" Then strBCC = strAddress & "; "
.MoveNext
Loop
End With
I don't want it to send a message though - I want to see an Outlook
message dialog box, with To field completed with all the contacts from
my Make Table, rest of the message box blank.
Click to expand...

Well, you're not loading the To field at all. All that stuff you're
building above winds up in the BCC field. Did you actually mean to
populate the To field?

This line:
should ensure the message is not automatically sent because you have
"True" in the [EditMessage] parameter. Not sure why Outlook would try to
send it.
Any ideas why this new error is occurring?
Click to expand...

As long as we're picking this apart, this looks false to me:
1. Windows uses carriage return/line feed pairs for line breaks.
Everywhere you see Chr$(13) I would substitute VbCrLf
2. As coded the variable holds the _body_ text, not the subject text.

HTH
Click to expand...
 
K

KLR

I've made the changes and checked (and double checked) the data - all
email addresses are fine. Will have a nervous breakdown if I carry on
trying to figure this out so might just start from the beginning!

KLR
I will place code changes inline below to populate the "to" field
instead of "bcc". If that doesn't work my best guess is you have bad
email addresses in your data.

Hope this helps!
Well my code now reads as below but still getting the 'Unknown message
recipients' message popping up! You are correct in that I was not
intending to populate the Bcc field.

Any further help would be greatly appreciated!

KLR

Private Sub SendEmail_Click()

Dim strDocName As String
strDocName = "qmt_ucas_emails"
DoCmd.OpenQuery strDocName

On Error GoTo ErrHandle

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String 'this creates the Email addresses
Dim strTo As String 'this is needed to populate the TO block
on the EMail form
Dim strCC As String 'this is needed to populate the CC block
on the EMail form
Dim strBCC As String 'this is needed to populate the BCC block
on the EMail form
Dim strSubj As String 'this is needed to populate the SUBJ
block on the EMail form
Dim strText As String 'this is needed to populate a portion of
the message text block

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("t_Account Management data",
dbOpenSnapshot)

If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no
EMail Addresses listed!"
GoTo ErrExit
End If

With rst
.MoveFirst
Do While Not .EOF
strAddress = Nz(.Fields("UCASEmail").Value, "")
Click to expand...

' REMOVE THIS If strAddress <> "" Then strBCC = strAddress & "; "
If strAddress said:
.MoveNext
Loop
End With
Click to expand...

' REMOVE THIS BLOCK
strTo = ""
strCC = ""
strBCC = strBCC
strSubj = ""
strText = ""
Click to expand...
' END REMOVE
' REMOVE THIS DoCmd.SendObject acSendNoObject, , , , , strBCC, , , True
' note different placement of parameters in replacement line below
DoCmd.SendObject acSendNoObject, , , strTo , , , , , True
ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.description
Resume ErrExit
Resume

End Sub
KLR wrote:
Thanks - I've made the amendments requested but now get "Unknown
message recipient(s). Message not sent".
I'm not sure if Outlook is freaking out about empty addresses you might
be populating ("; ; ;"), or if you actually have bad addresses in your
data. We can rule out the former. Also, the loop can be tidied up
considerably.

Replace your _With rst_ block as follows:

With rst
.MoveFirst
Do While Not .EOF
strAddress = Nz(.Fields("UCASEmail").Value,"")
If strAddress <> "" Then strBCC = strAddress & "; "
.MoveNext
Loop
End With

I don't want it to send a message though - I want to see an Outlook
message dialog box, with To field completed with all the contacts from
my Make Table, rest of the message box blank.
Well, you're not loading the To field at all. All that stuff you're
building above winds up in the BCC field. Did you actually mean to
populate the To field?

This line:
DoCmd.SendObject acSendNoObject, , , strTo, , strBCC, strSubj,
strText, True

should ensure the message is not automatically sent because you have
"True" in the [EditMessage] parameter. Not sure why Outlook would try to
send it.

Any ideas why this new error is occurring?
As long as we're picking this apart, this looks false to me:
strText = Chr$(13) & Chr$(13) & "" & Chr$(13) & ""
'Chr$(13) will insert a blank line in the subject of your EMail.

1. Windows uses carriage return/line feed pairs for line breaks.
Everywhere you see Chr$(13) I would substitute VbCrLf
2. As coded the variable holds the _body_ text, not the subject text.

HTH

Smartin wrote:
KLR wrote:
I am using the following code (from Ron Hughes code to send email to
all database at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='CodeSendEMailToAll.mdb'.
When clicking the command button, I click on Yes when prompted to
create a new table but at the end the error message "Invalid use of
Null" is displayed. Can anyone help?

Private Sub SendEmail_Click()

Dim strDocName As String
strDocName = "qmt_ucas_emails"
DoCmd.OpenQuery strDocName

On Error GoTo ErrHandle

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubj As String
Dim strText As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("t_Account Management data",
dbOpenSnapshot)

If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no
EMail Addresses listed!"
GoTo ErrExit
End If

With rst

.MoveFirst

strAddress = .Fields("UCASEmail").Value
strBCC = strAddress

.MoveNext

Do While .EOF = False
strAddress = .Fields("UCASEmail").Value
strBCC = strBCC & "; " & strAddress
.MoveNext
Loop
End With

strTo = ""
strCC = ""
strBCC = strBCC
strSubj = ""

strText = Chr$(13) & Chr$(13) & "" & Chr$(13) & ""
'Chr$(13) will insert a blank line in the subject of your EMail.
'Anything between the quotes will be inserted. You can edit, as
required before sending
'If you want the subject to be blank, just put the quotes, with
nothing between them

DoCmd.SendObject acSendNoObject, , , strTo, , strBCC, strSubj,
strText, True

ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.description
Resume ErrExit
Resume

End Sub

I am guessing one of the lines that looks like
strAddress = .Fields("UCASEmail").Value
is throwing the error because
.Fields("UCASEmail").Value
is Null.

To confirm this temporarily edit the ErrHandle routine as follows:

ErrHandle:
MsgBox Err.description
debug.print "UCASEmail is... " & .Fields("UCASEmail").Value
stop
Resume ErrExit
Resume

Save and Run. When it crashes, switch to the Immediate window (Ctrl+G)
and look at the result there. Does it say "UCASEmail is... Null"? If so,
then modify both lines that look like
strAddress = .Fields("UCASEmail").Value
to look like
strAddress = Nz(.Fields("UCASEmail").Value,"")
This will convert the Null values to empty strings on the fly.

Oh, and remove the debug.print and stop statements from the ErrHandle.

HTH
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send email to group of contacts 0
Delete a table using VBA 1
Putting e-mail addresses into code 0
Sending email to many recipients 2
Error number: 3326 0
Edit Body Text in Outlook 1
Using name from address book in lieu of actual email address 2
Email attachments 3

Top