New Outlook Email with BCC field populated with Emails from Query

[micarl]

I'm trying to create a new email message from Outlook based on a query
called Q_Email with the BCC field populated with the email address
field in the query.

When i run this code, the query ressults pop up with a message saying
Object Required.

Thoughts?

Private Sub BCCMail_Click()
On Error GoTo Err_BCCMail_Click

Dim MyOutlook As New Outlook.Application
Dim MyMail As Outlook.MailItem
Dim StrBCC As String
Dim stDocName As String

stDocName = "Q_Email"
DoCmd.OpenQuery stDocName, acNormal, acEdit
With rst
Do Until .EOF
StrBCC = StrBCC & ![EmailAddress] & ";"
Loop
..Close
End With
StrBCC = Left(StrBCC, Len(StrBCC) - 1)

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BCC = StrBCC
MyMail.Display

Set MyOutlook = Nothing
Set rst = Nothing

Exit_BCCMail_Click:
Exit Sub

Err_BCCMail_Click:
MsgBox Err.Description
Resume Exit_BCCMail_Click

End Sub

[simon.kingston]

Hi,

You never create a recordset object for your loop. Rather than open a
query, you need to open a recordset based on the query. Make sure you
have a reference to the DAO object library (Tools References at the
top of a VBA code window) and add the following declaration at the top
of your code:

Dim rst as DAO.Recordset

Then replace your line "DoCmd.OpenQuery stDocName, acNormal, acEdit"
with the following:

Set rst = CurrentDb.OpenRecordset(stDocName,dbOpenForwardOnl y)

This fills the recordset with the records from your query and allows
you to step through them from front to back.

Inside your loop, you need to advance from one record to the next, so
add the following line of code before your Loop line:

..MoveNext

I think it should work after that.

Cheers,
Simon

On Nov 17, 9:04*am, [e-mail] wrote:

I'm trying to create a new email message from Outlook based on a query
called Q_Email with the BCC field populated with the email address
field in the query.

When i run this code, the query ressults pop up with a message saying
Object Required.

Thoughts?

Private Sub BCCMail_Click()
On Error GoTo Err_BCCMail_Click

Dim MyOutlook As New Outlook.Application
Dim MyMail As Outlook.MailItem
Dim StrBCC As String
Dim stDocName As String

* *stDocName = "Q_Email"
DoCmd.OpenQuery stDocName, acNormal, acEdit
With rst
Do Until .EOF
StrBCC = StrBCC & ![EmailAddress] & ";"
Loop
.Close
End With
StrBCC = Left(StrBCC, Len(StrBCC) - 1)

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BCC = StrBCC
MyMail.Display

Set MyOutlook = Nothing
Set rst = Nothing

Exit_BCCMail_Click:
* * Exit Sub

Err_BCCMail_Click:
* * MsgBox Err.Description
* * Resume Exit_BCCMail_Click

End Sub

[micarl]

Thanks for your help!

Is there any way to write code to make sure users
have a reference to the DAO object library?

Having to adjust the references on users access probably isn't ideal?

Also, if i have this running based on selections from a multiselect
picklist on a form, would i be best to copy this code to a module and
have the vba code run through the selections, then the module? or
would i be best to combine the code for the selections then run this?

[micarl]

where abouts in that code would i place WhereCondition:=strWhere,
OpenArgs:=strDescrip

[simon.kingston]

On Nov 18, 7:25*am, [e-mail] wrote:

where abouts in that code would i place WhereCondition:=strWhere,
OpenArgs:=strDescrip

Regarding the reference to the DAO object library, if you are
distributing the db as a file, then it will be set when you send it to
folks. References are on a per Access file basis, not for the whole
of MS Access. That said, you get some default references that depend
on your version of Access.

It's not clear from your post what you are selecting in your
multiselect picklist. If you can elaborate on that, I might be able
to help.

Also, where are you getting the WhereCondition and OpenArgs from? The
code you presented appears to be from a button click.

Simon

[Tony Toews [MVP]]

mi****@gmail.com wrote:

>Thanks for your help!

Is there any way to write code to make sure users
have a reference to the DAO object library?

Just add the DAO reference. Note though that if you're using ADO you can also create
a ADO recordset.

If You're using Access 2000 or newer and you have references to both DAO (Microsoft
DAO 3.6 Object Library or newer) and ADO (Microsoft ActiveX Data Objects 2.x Library)

When you have both references, you'll find that you'll need to "disambiguate" certain
declarations, because objects with the same names exist in the 2 models. For example,
to ensure that you get a DAO recordset, you'll need to use Dim rsCurr as
DAO.Recordset (to guarantee an ADO recordset, you'd use Dim rsCurr As
ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

>Having to adjust the references on users access probably isn't ideal?

DAO is fine as it's on everyones machine who has Access installed.

>Also, if i have this running based on selections from a multiselect
picklist on a form, would i be best to copy this code to a module and
have the vba code run through the selections, then the module? or
would i be best to combine the code for the selections then run this?

You might as well leave the code behind the form if it's only going to be used on
that form and is form specfiic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[micarl]

Yes, I've got users selecting a button on a form and i want an email
to be created to all users who fall in to the selected Category and
Countries selected. I have copied code i have that works for the
report and pasted it to this code, but obviously i need to set the
strWhere and strDescrib for the email to be based on.

Private Sub BCCEmail_Click()
On Error GoTo Err_BCCMail_Click

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strCategorySelect As String
Dim strCountrySelect As String
Dim strWhere As String
Dim stDocName As String
Dim MyOutlook As New Outlook.Application
Dim MyMail As Outlook.MailItem
Dim StrBCC As String
Dim rst As DAO.Recordset

stDocName = "Q_Email"

With Me.CategorySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySelect = strCategorySelect &
strCategorySelect & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategorySelect) - 1
If lngLen 0 Then
strCategorySelect = "[CategoryID] IN (" & Left$
(strCategorySelect, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelect = strCountrySelect & strCountrySelect
& strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountrySelect) - 1
If lngLen 0 Then
strCountrySelect = "[CountryID] IN (" & Left$
(strCountrySelect, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
If strCountrySelect "" Then strWhere = strWhere &
strCountrySelect & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)

Set rst = CurrentDb.OpenRecordset(stDocName, dbOpenForwardOnly)
With rst
Do Until .EOF
StrBCC = StrBCC & ![EmailAddress] & ";"
..MoveNext
Loop
..Close
End With
StrBCC = Left(StrBCC, Len(StrBCC) - 1)

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BCC = StrBCC
MyMail.Display

Set MyOutlook = Nothing
Set rst = Nothing

Exit_BCCMail_Click:
Exit Sub

Err_BCCMail_Click:
MsgBox Err.Description
Resume Exit_BCCMail_Click

End Sub

[micarl]

Any clues?

Can you set a record set to a Where condition?