Email Mail Merge

I have fields FirstName, LastName,title, emailaddress etc. in a query. Name
of query is "QryContactsQuery" .

I would like to do two things:

1) Click a command button and have Outlook open in edit mode, with all of
the Email Addresses populated in the BCC. Then I can just type an email and
click send.

2) A command button for Mail Merge to have Outlook open in edit mode with
an attachment containing prepopulated fields on a Word Document, such as
First Name, Title, etc. I want to then be able to open the attachment, type
a message on the Word Document. Then when I click send, Outlook sends email
as a mail merge to everyone on the query.

Or if anyone has existing code and could tell me how to reformat it with my
fields and query?

Here's some code I wrote a few years ago that creates a BCC file that I cut
and pasted into an email. I did it this way because of spam filters so I
wouldn't put more than 50 email addresses at a time in the BCC 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("qryActiveMembersWithEmail", 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

Open "C:\Mailing" & Format(Date, "mmddyy") & ".txt" For Append As #1

Print #1, strBCC

Close #1

End Function

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


"Confused" wrote in message
...
I have fields FirstName, LastName,title, emailaddress etc. in a query.
Name
of query is "QryContactsQuery" .

I would like to do two things:

1) Click a command button and have Outlook open in edit mode, with all
of
the Email Addresses populated in the BCC. Then I can just type an email
and
click send.

2) A command button for Mail Merge to have Outlook open in edit mode with
an attachment containing prepopulated fields on a Word Document, such as
First Name, Title, etc. I want to then be able to open the attachment,
type
a message on the Word Document. Then when I click send, Outlook sends
email
as a mail merge to everyone on the query.

Or if anyone has existing code and could tell me how to reformat it with
my
fields and query?

I clicked new procedure, function and inserted the code, changing the Query
name and fields. I then put a command button on the form and on the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or where should
I put the code?

"Arvin Meyer MVP" wrote:

Here's some code I wrote a few years ago that creates a BCC file that I cut
and pasted into an email. I did it this way because of spam filters so I
wouldn't put more than 50 email addresses at a time in the BCC 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("qryActiveMembersWithEmail", 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

Open "C:\Mailing" & Format(Date, "mmddyy") & ".txt" For Append As #1

Print #1, strBCC

Close #1

End Function

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


"Confused" wrote in message
...
I have fields FirstName, LastName,title, emailaddress etc. in a query.
Name
of query is "QryContactsQuery" .

I would like to do two things:

1) Click a command button and have Outlook open in edit mode, with all
of
the Email Addresses populated in the BCC. Then I can just type an email
and
click send.

2) A command button for Mail Merge to have Outlook open in edit mode with
an attachment containing prepopulated fields on a Word Document, such as
First Name, Title, etc. I want to then be able to open the attachment,
type
a message on the Word Document. Then when I click send, Outlook sends
email
as a mail merge to everyone on the query.

Or if anyone has existing code and could tell me how to reformat it with
my
fields and query?

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing the Query
name and fields. I then put a command button on the form and on the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or where should
I put the code?

Hi,

Tried that and got Compile Error/ Syntax Error.

"Tom Wickerath" wrote:

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing the Query
name and fields. I then put a command button on the form and on the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or where should
I put the code?

Tried that and got Compile Error/ Syntax Error.

What error message did you receive?

If you typed "=Foo()" (without the quotes) into the Properties listing for
the On Click event, this would not even get picked up as a compile error even
if it was incorrect. So, I'm a bit confused as to what is causing your error.

Or, did you create an OnClick procedure like this?

Private Sub CommandButtonName_Click()
Foo
End Sub

So, did you enter this on the Property Sheet, or in a new procedure?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

Hi,

Tried that and got Compile Error/ Syntax Error.

"Tom Wickerath" wrote:

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing the Query
name and fields. I then put a command button on the form and on the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or where should
I put the code?

I'm logged off from work, but here is what I recall. I clicked on the Code
buttton and selected new procedure. I then selected the radio button for
Function. I pasted the code.

Then I put a command button on the form for it's on click event. I typed
=FOO()

I'm sure I messed up something. It's been a roller coaster ride... Thanks!

"Tom Wickerath" wrote:

Tried that and got Compile Error/ Syntax Error.

What error message did you receive?

If you typed "=Foo()" (without the quotes) into the Properties listing for
the On Click event, this would not even get picked up as a compile error even
if it was incorrect. So, I'm a bit confused as to what is causing your error.

Or, did you create an OnClick procedure like this?

Private Sub CommandButtonName_Click()
Foo
End Sub

So, did you enter this on the Property Sheet, or in a new procedure?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

Hi,

Tried that and got Compile Error/ Syntax Error.

"Tom Wickerath" wrote:

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing the Query
name and fields. I then put a command button on the form and on the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or where should
I put the code?

Okay. If you created a new procedure, as you just indicated, then you would
not include the = sign unless you were assigning a return value from the
function to some variable. However, as written, Arvin's function does not
return a value. So, try the form I indicated in my last post:

Private Sub CommandButtonName_Click()
Foo
End Sub

where CommandButtonName is the name of your command button.

The = sign is only used when:
1.) assigning a return value from the function to a variable or
2.) calling the function directly from the property sheet or
3.) calling the function from a macro


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I'm logged off from work, but here is what I recall. I clicked on the Code
buttton and selected new procedure. I then selected the radio button for
Function. I pasted the code.

Then I put a command button on the form for it's on click event. I typed
=FOO()

I'm sure I messed up something. It's been a roller coaster ride... Thanks!

By any chance did you name the module the same as the function? If so change
the name of either the function or better still the module.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Confused" wrote in message
...
I'm logged off from work, but here is what I recall. I clicked on the
Code
buttton and selected new procedure. I then selected the radio button for
Function. I pasted the code.

Then I put a command button on the form for it's on click event. I typed
=FOO()

I'm sure I messed up something. It's been a roller coaster ride...
Thanks!

"Tom Wickerath" wrote:

Tried that and got Compile Error/ Syntax Error.

What error message did you receive?

If you typed "=Foo()" (without the quotes) into the Properties listing
for
the On Click event, this would not even get picked up as a compile error
even
if it was incorrect. So, I'm a bit confused as to what is causing your
error.

Or, did you create an OnClick procedure like this?

Private Sub CommandButtonName_Click()
Foo
End Sub

So, did you enter this on the Property Sheet, or in a new procedure?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

Hi,

Tried that and got Compile Error/ Syntax Error.

"Tom Wickerath" wrote:

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing
the Query
name and fields. I then put a command button on the form and on
the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or
where should
I put the code?

All I did was create a function by clicking on Insert and selecting the radio
button for function. I named it FOO pasted it and it ended up looking like
yours. I don't think I created a module.

I couldn't get it to work, so I deleted the function and inserted the code
on a command button click. For some reason it doesn't seem to do anything.
How do you think I should proceed? Much Thanks!

Here is my code:


Private Sub Command45_Click()

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("qryContactsQuery", dbOpenSnapshot)

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

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

'Debug.Print strBCC

Open "C:\Mailing" & Format(Date, "mmddyy") & ".txt" For Append As #1

Print #1, strBCC

Close #1






End Sub


"Arvin Meyer MVP" wrote:

By any chance did you name the module the same as the function? If so change
the name of either the function or better still the module.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Confused" wrote in message
...
I'm logged off from work, but here is what I recall. I clicked on the
Code
buttton and selected new procedure. I then selected the radio button for
Function. I pasted the code.

Then I put a command button on the form for it's on click event. I typed
=FOO()

I'm sure I messed up something. It's been a roller coaster ride...
Thanks!

"Tom Wickerath" wrote:

Tried that and got Compile Error/ Syntax Error.

What error message did you receive?

If you typed "=Foo()" (without the quotes) into the Properties listing
for
the On Click event, this would not even get picked up as a compile error
even
if it was incorrect. So, I'm a bit confused as to what is causing your
error.

Or, did you create an OnClick procedure like this?

Private Sub CommandButtonName_Click()
Foo
End Sub

So, did you enter this on the Property Sheet, or in a new procedure?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

Hi,

Tried that and got Compile Error/ Syntax Error.

"Tom Wickerath" wrote:

Hi Confused,

Try this: =Foo()


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Confused" wrote:

I clicked new procedure, function and inserted the code, changing
the Query
name and fields. I then put a command button on the form and on
the On
Click Event I typed Foo.

It's not doing anything. So how do I reference the Function or
where should
I put the code?