how to generate a mailing??

[martin]

Hello,

suppose I have a simple adress-database, with just one table "people"
In this table are all regular contactpersons-data like name, address, city,
email, etc

Now I want to make a button wich generates a mail-field (from Outlook) with
ALL my contactpersons emailadresses in the BCC-field.

Does there excist a kind of standard procedure/function for this?
Or how could I accomplish this?

Kind regards,


martin

 

[Arvin Meyer [MVP]]

Here's some code which will email individual emails to a list:

http://www.datastrat.com/Code/MultipleEmail.txt

 

[Arvin Meyer [MVP]]

Here's some code which will print the email addresses to the debug
(immediate) window, from which you can copy and paste into your BBC field:

Function Foo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strBCC As String
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Your Table or Query Name", dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!Email) > 0 Then
strBCC = strBCC & rst!Email & ";"
End If
rst.MoveNext
Next i
strBCC = Left$(strBCC, Len(strBCC) - 1)

Debug.Print strBCC

End Function

 

[martin]

thanx Arvin!!

Here's some code which will email individual emails to a list:

http://www.datastrat.com/Code/MultipleEmail.txt
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[James A. Fortune]

Here's some code which will print the email addresses to the debug
(immediate) window, from which you can copy and paste into your BBC field:

Function Foo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strBCC As String
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Your Table or Query Name", dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!Email) > 0 Then
strBCC = strBCC & rst!Email & ";"
End If
rst.MoveNext
Next i
strBCC = Left$(strBCC, Len(strBCC) - 1)

Debug.Print strBCC

End Function

Arvin,

I think the code you posted will bomb if the query doesn't have any
records or if the 'Email' fields are Null. Perhaps you edited your
example too quickly or considered those situations too unlikely. I
quickly adapted your posted code with the goal of changing it as little
as possible:

Function Foo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strBCC As String
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Your Table or Query Name", dbOpenSnapshot)

strBCC = ""

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
For i = 1 To rst.RecordCount
If Len(rst!Email) > 0 Then
strBCC = strBCC & rst!Email & ";"
End If
rst.MoveNext
Next i
If Len(strBCC) > 0 Then
strBCC = Left$(strBCC, Len(strBCC) - 1)
End If
End If
End With

Debug.Print strBCC

End Function

I didn't test this out, but it seems to have a better chance of working
properly if those rare situations arise. I usually avoid putting the
semicolon in for the case where it's going to be taken out, but in some
situations such as For'ing through tabledef fields it's needed since
it's better not to assume that a particular field is the last one.

James A. Fortune
(e-mail address removed)

 

[martin]

Hello Arvin,

Thank you for the suggestion,

now, I'm quite a newbe, so: What exactly should I do with such a function,
where should I paste it?
I mean: I have a button already, so how can I make the button execute the
function?

and, just o be sure: in that function you sent me the link of:
Suppose that my table with contact persons is aaa
then the part "qryContacts" should be replaced by "aaa"?

thanks a lot

kind regards,
martin

 

[Arvin Meyer [MVP]]

I think the code you posted will bomb if the query doesn't have any
records or if the 'Email' fields are Null. Perhaps you edited your
example too quickly or considered those situations too unlikely. I
quickly adapted your posted code with the goal of changing it as little as
possible:

You're correct, I wrote it quickly and tested it with a query where there
were no nulls. In that case it would only bomb if there were no email
addresses at all (unlikely, but possible) Error handling (you notice that it
was still without any) would have handled that. Moving the End If to the end
is a better solution. Also, the semicolon, if not removed, is immaterial
with every email client that I've used. I also use a similar code
construction to build up In clauses in SQL statements where a ending comma
must be removed.

 

[CDMAPoster]

You're correct, I wrote it quickly and tested it with a query where there
were no nulls. In that case it would only bomb if there were no email
addresses at all (unlikely, but possible) Error handling (you notice that it
was still without any) would have handled that. Moving the End If to the end
is a better solution. Also, the semicolon, if not removed, is immaterial
with every email client that I've used. I also use a similar code
construction to build up In clauses in SQL statements where a ending comma
must be removed.

Fair enough Arvin. BTW, I'm trying something different with
attachments. I noticed that a variant variable can be set to, say,
Array("0") and then ReDim'ed so I'm going to try to send a variant
containing the names of all the attachments to my SendEmail subroutine
in an optional argument.

James A. Fortune
(e-mail address removed)

 

[Arvin Meyer [MVP]]

Yes, replace qryContacts with your query name. Put the function in a
standard module and call it from your command button:

Here's some code that uses the code from a form that supplies all the
fields. Remember strTo is generally yourself or an archive email address. It
is the strBCC in the body of the code that contains the string of email
addresses:

Private Sub cmdEmail_Click()
On Error GoTo Err_Handler

If Len(Me.txtTo & vbNullString) = 0 Then
MsgBox "You can't send without your email address here"
Me.txtTo.SetFocus
Exit Sub
End If

If Len(Me.txtSubject & vbNullString) = 0 Then
MsgBox "You can't send without a subject"
Me.txtSubject.SetFocus
Exit Sub
End If

If Len(Me.txtBody & vbNullString) > 0 Then
Call Email(Me.txtTo, Me.txtSubject, Me.txtBody, Me.txtPath)
Else
Call Email(Me.txtTo, Me.txtSubject, , Me.txtPath)
End If

Me.txtTo.SetFocus
Me.cmdEmail.Enabled = False

Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com