[cowplopmorris]

Hi,
I've got an Access database consisting of a large number of records, only some of which have an email address stored.

I know next to nothing about VB scripting, but I had a look, and tried (and played with) this simple script:

Option Compare Database
Private Sub Command0_Click()
FollowHyperlink "mailto:myemail@mydomain.com?bcc=email1@domain.com,email2@domain.com,email3@domain.com"
End Sub

This works fine. It pops up a new email in the default email client with the to: and bcc: correctly.

But basically there are two things I want to do:

1. Have a button on a form which will open up a new email with the current record as the to: recipient.
2. Have a separate form/macro/etc. which will create a new email with myemail@mydomain.com in the to: and every email address in the database (which is not null) in the bcc.

Is there a way of doing this? Any help will be very much appreciated.

[Keebellah]

Hi, I do something similar, I read the list of addresses in a variable BCCAddres and use the separator (the semi-colon) in between them:
For x = 1 to many
BCCaddress = BCCaddress & ";" & nextaddress
next x
Maybe this will help you on the way.

[cowplopmorris]

Originally Posted by Keebellah

Hi, I do something similar, I read the list of addresses in a variable BCCAddres and use the separator (the semi-colon) in between them:
For x = 1 to many
BCCaddress = BCCaddress & ";" & nextaddress
next x
Maybe this will help you on the way.

Hi,
Thanks for the reply.
How do I set the variable nextaddress to use a certain field in a certain table?

[Keebellah]

I imagine that you open the table, prefrably aquery and read through it something like
If your address fieldname is is email
dim BCCAddres as string
do while not EOF() <-- this end-of-file
BCCAddress=BCCAddress & email & ";"
wend
You will have to remote the last ";" after it's finished
then you use the BCCaddress as address
I'll see if I can reduce my code to something you can tryout. But that won't be 'til this wwekend since it's now 23:25 here.

[cowplopmorris]

Originally Posted by Keebellah

I imagine that you open the table, prefrably aquery and read through it something like
If your address fieldname is is email
dim BCCAddres as string
do while not EOF() <-- this end-of-file
BCCAddress=BCCAddress & email & ";"
wend
You will have to remote the last ";" after it's finished
then you use the BCCaddress as address
I'll see if I can reduce my code to something you can tryout. But that won't be 'til this wwekend since it's now 23:25 here.

Hi,
Yes, I understand how to add the email addresses together, but what I don't get is how you have used the "email" variable. If earlier on in the script you put email=someone@somewhere.com, then it would make sense, but how do you make the variable "email" actually read from the table?
Thanks.

[Keebellah]

Hi, maybe I confused you, I imagined that you have the list of email addresses stored someplace, I imagine a table, so if you have a list you open the table or query and go through it until the end of the selection or entire fiel.

It just depends on what your source is for the list.
That's all I meant.

[cowplopmorris]

Sorry, I'm still really confused.
Yes, I have them stored in a table called Table1 and field called email.

you open the table or query and go through it until the end of the selection or entire fiel.

Do you literally mean open it and copy and paste the email addresses? This isn't really what I want, since the fields in the email addresses may change.

Thanks for your help so far.

Edit: How about if I take it back a bit? Say I have a table with email addresses in, and a form with a button which currently does nothing. What then?

Edit 2:In pseudocode:

open Table1 read only
for each record which the email field is not null{
bcc=bcc+Table1.currentrecord.email+";"
next record
}
bcc=bcc-last ";"
FollowHyperlink "mailto:myemail@mydomain.com?bcc=" & bcc

[Keebellah]

That's the idea I have been trying to put into words, in this manner when an address is removed or added the list will always be up todate.
When the list is completed the varaibel will read "email1; email2;email3;etc"
You set this in after the BCC part and voilá
I think you have it right there

This is the code I use after my data is complete

Public Function naarOutlook(strTo As Variant, strCC As Variant, strBCC as Variant, strSubject As String, strBody As Variant, Optional sReportname As Variant)
Dim objAppOutlook As Variant
Dim objMailItem As Variant
Dim xx As Variant
Set objAppOutlook = GetObject(, "Outlook.application")
Const olMailItem = 0
Set objMailItem = objAppOutlook.CreateItem(olMailItem)
On Error GoTo Err_StartOutlook
If Len(Trim(sReportname)) <> 0 Then
With objMailItem
.To = strTo
.CC = strCC
.BCC = strBCC
.Subject = strSubject
.Body = strBody
.Attachments.Add sReportname
.Send
End With
Else
With objMailItem
.To = strTo
.CC = strCC
.Subject = strSubject
.Body = strBody
.Send
End With
End If
On Error GoTo 0

Exit_NaarOutlook:
Exit Function

Err_StartOutlook:
MsgBox Err.Description, vbCritical
MsgBox "Outlook is currently not active!", vbOKOnly , "OUTLOOK NOT ACTIVE"
On Error GoTo 0
Resume Exit_NaarOutlook

End Function

I found this code on different Internet VBA sites and it works,
All you need is to have aprocedure run and fill the variables and then Call naarOutlook with the praameters and it runs.
The sReportname is optional in case you wish to add an attachment.

[cowplopmorris]

All you need is to have aprocedure run and fill the variables

How would I go about doing this?
Thanks!

[Keebellah]

Well, I'll have to simplify some of my code.
Could you send me part of the table from where you extract the email address, just the structure and field names and I'll try and put an example together for you.
You will have to put some VBA knowledge in ityourself but I think it'll work for you.

[cowplopmorris]

You can have the whole database. It's just a test one with sample data. I wanted to try it out before I did it in the real thing.

[Keebellah]

For soem reason zip files do not come accross right, can you mar an rar file out of it?

[Keebellah]

Well, I did my best for you.
I have attached the updated test.mdb.
What I did was add a field to the original table with a Yes/No option and a Query where onle the email and the new field is true
The list you get are only the adressees that will get the BCC.
Try the macro and see if it's OK.

[cowplopmorris]

Originally Posted by Keebellah

Well, I did my best for you.
I have attached the updated test.mdb.
What I did was add a field to the original table with a Yes/No option and a Query where onle the email and the new field is true
The list you get are only the adressees that will get the BCC.
Try the macro and see if it's OK.

Hi,
Thank you so much. It was almost exactly what I needed. I changed it a bit (eg. went back to using mailto: rather than creating an Outlook object directly) and stuck it all in the sub for the form button click.

A couple of things, though, if you don't mind:
- What do I need to change in order to use a field other than the first?
- How would I go about adapting this for a table of 38 fields and 1000+ records? Or doesn't this matter as long as the query produces <=10 fields and <=50 records?

Thanks again for your help.

[Keebellah]

HI,
Well you would have to look into my code and state which field it should be, RapRecord(x,1) means record x field 1 and this through 10, but you can change the array values to whatever you need.
I suggest anyway to create a querry which extracts the email address and the logical field which marks the record to be one of the selected. Like I did, I added a field ToMail with the Yes/No option (True/false) and use this to determine the contents of the query.
I hope you can get it working, or else send me your renewed copy.