A bit more than Access > Outlook Emailing

?

- -

Hi gang
I am having some problems with Access > Outlook emailing. I've read many
documents about Recordsets and the such, but still cannot overcome this
issue:

I have a table named Contacts which holds a field called EmailAddress . I
have worked out how to email the current record in the table by passing the
address through to Outlook. From time to time, I wish to email the entire
table (a mass e-mailout if you like), and have accomplished this to a
certain extent, whereby Access works through the recordset and generates a
seperate email for each contact. However, I'd like Access to pass through
all the address to the BCC field of ONE email.

Could anyone help me with this, as I'm tearing my hair out on this one!!!

NB, not all records have an email address filled in, so some sort of NULL
rule would be required I guess.

Thankyou in advance
Matt
 
P

Paul Overway

The question is related more to Outlook programming than it is to Access.
Nevertheless, you'd need to add each recipient to the Recipients collection
of the mail item...and set the Type property for each recipient to olBCC.

Dim m As Object 'mailitem object
Dim x As Object 'recipient object

'create Outlook app object, namespace, and mailitem...then

Set x = m.Recipients.Add("(e-mail address removed)")
x.Type = 3 'olBCC constant
 
?

- -

Hi Paul
Thanks for the indicators there, -I'm new to all this, so I'm still trying
to get my head around it!
This is my code so far, could you please give me some pointers on how to
implement your suggested changes into it?
NB [EmailAdd] is the field containing the addresses needed.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olTo

Set objOutlookRecip = .Recipients.Add([EmailAdd])
objOutlookRecip.Type = olBCC

.Subject = "Magazine"
.Body = "This is the body of the message here" & vbCrLf &
vbCrLf
.Importance = olImportanceHigh

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display
End With
Set objOutlook = Nothing

Thanks again
Matt
 
?

- -

Hi Arvin
There appears to be an error whereby if you select a senator whos email
address is null along with a senator with an email address filled in, the
email is not generated.
Matt
 
P

Paul Overway

Get rid of your Outlook reference...you'll have problems if the database is
opened on other PCs without Outlook or with a different version of Outlook.
You should also implement an error handler. You need to modify revised code
below to point to the correct table/field with the email address.

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim db As Database
Dim rst As Recordset

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(0)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = 1

.Subject = "Magazine"
.Body = "This is the body of the message here" & vbCrLf & vbCrLf
.Importance = 2

'Open recordset and loop add recipients
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT EmailAddField FROM SomeTable
WHERE EmailAdd Is Not Null")

If Not rst.EOF Then
rst.MoveFirst

Do Until rst.EOF

Set objOutlookRecip =
..Recipients.Add(rst.Fields("EmailAddField"))
objOutlookRecip.Type = 3

rst.MoveNext
Loop
End If

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display
.Send '? Assuming you want it sent
End With

Set objOutlook = Nothing
 
T

Tom Wickerath

To add just a little bit to Paul's suggested code, I would use

Dim rst As DAO.Recordset instead of just
Dim rst As Recordset

Here's why: http://www.access.qbuilt.com/html/gem_tips1.html

Also, I think it is a good idea to close the recordset and database, and set
these to nothing at the end of your procedure:
:
:
rst.close
set rst = Nothing
db.close
set db = Nothing

Here's why: http://support.microsoft.com/?id=289562


Tom
___________________________________________

:

Get rid of your Outlook reference...you'll have problems if the database is
opened on other PCs without Outlook or with a different version of Outlook.
You should also implement an error handler. You need to modify revised code
below to point to the correct table/field with the email address.

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim db As Database
Dim rst As Recordset

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(0)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = 1

.Subject = "Magazine"
.Body = "This is the body of the message here" & vbCrLf & vbCrLf
.Importance = 2

'Open recordset and loop add recipients
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT EmailAddField FROM SomeTable
WHERE EmailAdd Is Not Null")

If Not rst.EOF Then
rst.MoveFirst

Do Until rst.EOF

Set objOutlookRecip =
...Recipients.Add(rst.Fields("EmailAddField"))
objOutlookRecip.Type = 3

rst.MoveNext
Loop
End If

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display
.Send '? Assuming you want it sent
End With

Set objOutlook = Nothing
 
A

Arvin Meyer [MVP]

Thanks. I just fixed this by not allowing null email addresses in the list
box. It doesn't make sense to try and email someone without an email
address. BTW, please don't send email as most of these addresses are old now
anyway and they'll just bounce.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 

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

Top