Email Mail Merge

[Confused]

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?

 

[Arvin Meyer MVP]

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

 

[Confused]

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?

 

[Tom Wickerath]

Hi Confused,

Try this: =Foo()


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

 

[Confused]

Hi,

Tried that and got Compile Error/ Syntax Error.

 

[Tom Wickerath]

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]

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]

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/
__________________________________________

 

[Arvin Meyer MVP]

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.

 

[Confused]

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]

If you notice, there's no error handling. Add an error handling procedure
that enumerates the error.

Did you look for the file in C:\Mailing, plus the date? Are you using a
computer with permissions to write to the root of the C: drive?

 

[Tom Wickerath]

Also, when you state "For some reason it doesn't seem to do anything", are
you saying that the code is not running at all, or that it runs okay but
isn't producing the desired result? To verify that the code is running, add
a temporary line of code just prior to the Exit label (after inserting
error-handling code), which will generate a message (Hello) if the function
runs to completion:


Option Compare Database
Option Explicit

Private Sub Command45_Click()
On Error GoTo ProcError

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

MsgBox "Hello!" '<---------Temporary Message Box

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Command45_Click..."
Resume ExitProc

End Sub


By the way, I recommend giving your controls a more reasonable name before
adding any code behind the control. "Command45" is not exactly an intuitive
name.


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