Bcc messages from Access 03 to Outlook using query

[Ultraviolet47]

Hi all

Someone on the general board pointed me in this direction.

I have the following code to use the emails returned from a query, open
outlook and bcc them.

It gives a run time error sayinig it doesn't support the property or
method for

Set objRecip = MyMail.Recipients.AddMailList("email")
objRecip.Type = olBCC

but I've checked all the object libraries I can find that are
applicable to this-anyone have any idea which ones I might be missing?
I have the offfice and outlook ones.

I also need to set a To address, so some mailboxes won't reject
them-does any have any idea how to do that?

Any help greatly appeciated.


Public Function SendEmailUpdates()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject
'Enter the subject line

Subjectline$ = InputBox$("Please enter the subject line for this
mailing.", _
"Subject Line")

' If there's no subject, quit.

If Subjectline$ = "" Then
MsgBox "Email composition cancelled" & vbNewLine & vbNewLine & _
"", vbCritical, "Outlook Email "
Exit Function
End If

' Enter body text

BodyFile$ = InputBox$("Please enter the location of the filename of the
body of the message.", _
"Enter location of .txt file")

' If there's no body, quit.

If BodyFile$ = "" Then
MsgBox "Email composition cancelled" & vbNewLine & vbNewLine & _
"", vbCritical, "Outlook Email"
Exit Function
End If

' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file cannot be found, please check the location. "
& vbNewLine & vbNewLine & _
"Email composition cancelled", vbCritical, "No file found."
Exit Function
End If

' If file exists, open it.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False,
TristateUseDefault)

' and read it into a variable.
MyBodyText = MyBody.ReadAll

' and close the file.
MyBody.Close

' Open Outlook
Set MyOutlook = New Outlook.Application


' Set up the database and query connections

Set db = CurrentDb()

Set MailList = db.OpenRecordset("Qry_UpdatesOE")

' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.

Do Until MailList.EOF

' This creates the e-mail
' Need to move it BEFORE the loop is started, as we don't want
to make separate emails, just one.


Set MyMail = MyOutlook.CreateItem(olMailItem)

'Loop through list of addresses,
' and add them to the RECIPIENTS list

Do Until MailList.EOF

' This adds the address to the list of recipients

Set objRecip = MyMail.Recipients.AddMailList("email")
objRecip.Type = olBCC



'And next ones
MailList.MoveNext

Loop


' Finish composing the rest of the fields.

'This gives it a subject

MyMail.Subject = Subjectline$

'This gives it the body
MyMail.Body = MyBodyText




' This sends it

'MyMail.Send

'Show email
MyMail.Display


Loop

'Cleanup

Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[Sue Mosher [MVP-Outlook]]

If you looked in the object browser at the Outlook object library, you'd see that the method is Recipients.Add, not AddMailList.

The default Type for a recipient added with Recipients.Add is olTo.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Ultraviolet47]

Hi

I need to call up the "email" field from a query and insert these into
Bcc field. if I use that code, it just inserts the word "email" a few
dozen times (but it does go in the bcc fied at least!) I think because
it's a query, it needs the maillist?

Before, MyMail.Recipients.AddMailList("email") worked fine, but I just
needed it in the Bcc field, not the To field.


Any advice?

 

[Ultraviolet47]

Sorry, that's

MyMail.Recipients.Add MailList("email")

worked before, but put it in the To field.

 

[Sue Mosher [MVP-Outlook]]

You already answered your own question in your original post. Look at your olBcc statement.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Ultraviolet47]

Yes, but if I use the space between Add and MailList

Set objRecip = MyMail.Recipients.Add MailList("email")
objRecip.Type = olBCC
says expected end of statement and syntax error

and

MyMail.Recipients.Add MailList("email")
objRecip.Type = olBCC
says invalid use of Null

What I am overlooking?

 

[Sue Mosher [MVP-Outlook]]

This is the statement that instantiates MailList:

Set MailList = db.OpenRecordset("Qry_UpdatesOE")

Check the value that MailList("email") returns and see http://support.microsoft.com/Default.aspx?kbid=290658 for an example of DAO database syntax.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Ultraviolet47]

Good grief, this is keeping me up at night!
I know the mail list query works, it's just dumping the darn thing
where I want it.
This is totally lost on me-I shall just settle for it being in the To
field and copy and paste it into the bcc field I think! My brain admits
defeat- lol

Thanks for everyone's help anyway