Linking Access '97 with MS Outlook!!!!

[Cricker]

I have this database created in Access '97. I have a field called "Email". I would like to design a query to search and see who has an email address - i have this done.

Then I would like to take all those email addresses and send them an email to each one through BCC. I'm getting alot of people telling me different ways to do it and none of it has worked so far.

Could someone HELP ME!!!!!!!!

Chris

 

[thornmastr]

I am assuming you are using MS Outlook.

Your primary concern is that the email gets to the recipients in the BCC block. The send object method has a string available for BCC recipients.

Define a string variable of your recipients separated by a “;” and give the string as the BCC variable name. that should do it for you.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Cricker]

Thanks... but the code for send object would be???

Chris

 

[thornmastr]

Brought to you directly from Access help under send object replete with an example:

SendObject Method
See Also Applies To Example Specifics
The SendObject method carries out the SendObject action in Visual Basic.

expression.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

expression Required. An expression that returns one of the objects in the Applies To list.

ObjectType Optional AcSendObjectType.

AcSendObjectType can be one of these AcSendObjectType constants.
acSendDataAccessPage
acSendForm
acSendModule
acSendNoObject default
acSendQuery
acSendReport
acSendTable

ObjectName Optional Variant. A string expression that's the valid name of an object of the type selected by the objecttype argument. If you want to include the active object in the mail message, specify the object's type with the objecttype argument and leave this argument blank. If you leave both the objecttype and objectname arguments blank (the default constant, acSendNoObject, is assumed for the objecttype argument), Microsoft Access sends a message to the electronic mail application without an included database object. If you run Visual Basic code containing the SendObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.

OutputFormat Optional Variant.

OutputFormat Optional AcFormatType.

XlSendObjectOutputFormat can be one of these XlSendObjectOutputFormat constants.
acFormatDAP
acFormatHTML
acFormatRTF

acFormatTXT

acFormatXLS

If you leave this argument blank, Microsoft Access prompts you for the output format.


To Optional Variant. A string expression that lists the recipients whose names you want to put on the To line in the mail message. Separate the recipient names you specify in this argument and in the cc and bcc arguments with a semicolon ( or with the list separator set on the Number tab of the Regional Settings Properties dialog box in Windows Control Panel. If the recipient names aren't recognized by the mail application, the message isn't sent and an error occurs. If you leave this argument blank, Microsoft Access prompts you for the recipients.

Cc Optional Variant. A string expression that lists the recipients whose names you want to put on the Cc line in the mail message. If you leave this argument blank, the Cc line in the mail message is blank.

Bcc Optional Variant. A string expression that lists the recipients whose names you want to put on the Bcc line in the mail message. If you leave this argument blank, the Bcc line in the mail message is blank.

Subject Optional Variant. A string expression containing the text you want to put on the Subject line in the mail message. If you leave this argument blank, the Subject line in the mail message is blank.

MessageText Optional Variant. A string expression containing the text you want to include in the body of the mail message, after the object. If you leave this argument blank, the object is all that's included in the body of the mail message.

EditMessage Optional Variant. Use True (–1) to open the electronic mail application immediately with the message loaded, so the message can be edited. Use False (0) to send the message without editing it. If you leave this argument blank, the default (True) is assumed.

TemplateFile Optional Variant. A string expression that's the full name, including the path, of the file you want to use as a template for an HTML file.

Remarks
For more information on how the action and its arguments work, see the action topic.

Modules can be sent only in MS-DOS Text format, so if you specify acSendModule for the objecttype argument, you must specify acFormatTXT for the outputformat argument.

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Example
The following example includes the Employees table in a mail message in Microsoft Excel format and specifies To, Cc, and Subject lines in the mail message. The mail message is sent immediately, without editing.

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
"Nancy Davolio; Andrew Fuller", "Joan Weber", , _
"Current Spreadsheet of Employees", , False
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Cricker]

Thank you very much.... Now I'm getting...

Run-time error "2287"
Microsoft Access can't open the mail session.
Check your mail application to make sure that it's working properly.


What would I do to see if its connected????

cw

 

[grnzbra]

My understanding is that if you put a bunch of addresses in the bcc line on outlook, none of the recipients will see the other recipients. If this is the case and this is the reason you want to put it into bcc, I would suggest creating a loop that reads through the e-mail addresses and sends an individual e-mail to each one; I once tried to send and e-mail to a bunch of people (several hundred) and found that the whole thing blew up when it hit an invalid address (and it didn't tell me which address it was). If you send them as individual e-mails, they will be sent and the bad ones will be bounced back.

In any event the command to send it your way would be
DoCmd.SendObject ,,,,,StringOfEmailAddresses,SubjectLine,MessageText,0
for sending using BCC

or

DoCmd.SendObject,,,eMailAddress,,,SubjectLine,MessageText,0
to send individual e-mails.

The 0 allows the messages to be just sent. if it were set to -1 (True) it would, i think, be displayed for editing.

Do you need help extracting the addresses and either creating the bcc line or the regular to line?

 

[Cricker]

grnzbra:

I need all the help I can... The field I have for the email addresses is "Email1" and "EMail2" on a form.... I got the Query set up to fine all the email addresses called "QryEmail". I have a push button called "Email".

Now I don't have a clue what to do? I went to the OnClick with the pushbutton and put the code in and i says....

Run-time error "2287"
Microsoft Access can't open the mail session.
Check your mail application to make sure that it's working properly.


Now I"m stuck again.......

cw

 

[grnzbra]

What is the SQL for the query?
Does it blow up when you try to open the query directly or only when you try to use the button?
What is the purpose of the form?

The error sounds like you are trying to open outlook from access. What command are you using for that? Is it possible to just open Outlook manually before beginning the e-mail process?

 

[Cricker]

The SQL is:

SELECT ConstituentTable.ConstTableID, ConstituentTable.email1, ConstituentTable.email2
FROM ConstituentTable
WHERE (((ConstituentTable.email1)>"1&quot);

The Query works fine.... it shows all emails and addresses...

This is for a form I have set up for someone who would like all info on a person living in a community. Such as name, address, phone number and so on..

I really have no command to open the form. So I guess that must be the problem. But I have MS Outlook open for my own use and still won't work....

cw

 

[grnzbra]

I assume that the first e-mail address is always filled first and that one is not home and the other office such that e-mail2 could have something and e-mail1 not.

Are you familiar with the use of recordsets using VBA?

 

[Cricker]

Yup... thats right... the first one is either home or work and the second is another one (work or home).. I'm kinda familar with it but when i used this code....

Function ColumnToLine(QryEmail, email1)
On Error GoTo ErrInFunction
Dim myRs As DAO.Recordset
Dim ResultString As String
Set myRs = CurrentDb.OpenRecordset("QryEmail&quot
If myRs.RecordCount > 0 Then
Do Until myRs.EOF
ColumnToLine = ColumnToLine & IIf(Len(ColumnToLine) = 0, "", "; &quot & myRs("ColumnName&quot
myRs.MoveNext
Loop
End If
FinishPoint:
On Error Resume Next
'MsgBox ColumnToLine
myRs.Close
Exit Function
ErrInFunction:
ColumnToLine = "Error: " & Err.Description
Resume FinishPoint
Exit Function
End Function


The DAO.Recordset came up as a problem and the first line was highlighted...

cw

 

[grnzbra]

If the person has 2 email addresses, do you want to send to both? If so, change query to union query.

SELECT ConstituentTable.ConstTableID, ConstituentTable.email1 AS EMail,
FROM ConstituentTable
WHERE (((ConstituentTable.email1)>"1&quot)
UNION
SELECT ConstituentTable.ConstTableID, ConstituentTable.email2 AS EMail
FROM ConstituentTable
WHERE (((ConstituentTable.email2)>"1&quot);
;
This will stack the two e-mails in one column making life simpler. Then loop through this with and set up the address line with:
ColumnToLine = ColumnToLine & rs!EMail & ";"
Then, when you've added the last address, get rid of the last ; with
ColumnToLine = Left(ColumnToLine,Len(ColumnToLine)-1)

 

[grnzbra]

OOps.

Unless you need the ConstituentTable.ConstTableID field, don't include it in the union query. Otherwise you'll get blank e-mail address fields if they don't have 2 addresses.