E-Mailing Information

[Jeff Garrison]

All -

I have a form that is used to select Sub-Contractors. The "Select" box is
not bound to any field on the form. When the user has finished making their
selections, they click a button to generate the email. What I've been using
is the EMailToPDF module, which works great. The question I have is...is
there a way to take the results of the report I'm emailing (or recordset)
and put that information in the email body...Let me make the stipulation
that I don't want to send an individual email to each record or I don't want
to attach the report. I only want to take the results and place them in the
body. I know that there may be a loop somehow involved, but I'm not sure
when it come to the granular VBA stuff.

Any help would be greatly appreciated.

Thanks in advance.

JG

 

[Arvin Meyer [MVP]]

Here's some code to use in Outlook. The values for strTo, strSubject, and
strMsg can be fed from a form like:

Email(Me.txtEmailAddress, Me.txtSubject, Me.txtMsgBody):

Public Function Email(strTo As String, Optional strSubject _
As String, Optional strMsg As String)
'Set reference to Outlook
On Error GoTo Errhandler
Dim objOutl As Outlook.Application
Dim objEml As Outlook.mailitem

Set objOutl = CreateObject("Outlook.application")
Set objEml = objOutl.createItem(olMailitem)

With objEml
.To = strTo
.Subject = strSubject
.Body = strMsg
.send
End With

ExitHere:
Set objOutl = Nothing
Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Function

 

[Jeff Garrison]

Will this work if the information is dynamic, meaning that the information
will change from record to record (which would be the email message body)?

 

[Arvin Meyer [MVP]]

If you are doing a mass mailing to different emails, you'll need to use a
loop. Have a look at:

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

which includes a loop. Change the form string variables to recordset
variables from the recordset being called.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jeff Garrison]

It's not a mass mailing.

Here's the info on the form:

Contractor Phone Number Mobile Number
Selected Submitted Date
John Smith (XXX) XXX-XXXX (XXX) XXX-XXXX Yes
Tom Jones (XXX) XXX-XXXX (XXX) XXX-XXXX Yes
Sally Thomas (XXX) XXX-XXXX (XXX) XXX-XXXX No Yes
10/1/07
Mary Doe (XXX) XXX-XXXX (XXX) XXX-XXXX Yes

What the end result is to have 1 e-mail to a blank (or predefined) e-mail
address with all of the individuals that have the selected flag as Yes
information in the body of a SINGLE e-mail


John Smith
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Tom Jones
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Mary Doe
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Again, there will not be 3 e-mails generated, only 1 with the above info in
the body.

 

[Arvin Meyer [MVP]]

Build a recordset of all those with the flag = Yes:

Function Email()
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("Select Email from MyTable Where Flag = -1"

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)

Open "C:\SomeFolder\Mailing.txt" For Append As #1

Print #1, strBCC

Close #1

End Function

Now copy the file created into your email client's BCC field and send the
email, or use the code at:

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

with some slight modification to send the email. You may have to divide the
text file up into several emails because it would be hard on any mail server
to send that many at once, and many would be rejected as spam.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jeff Garrison]

Arvin -

We're getting closer...

What I want to do is to send a recordset as the body of an email - I don't
want to send 1 message to multiple addresses! The example you gave is
sending 1 message to multiple addresses (BCC). The file isn't an option
since the recordset will change based on of the Selected flag is 0 or -1.

The body (message) of the email would contain the recordset listed below,
based on the flag = -1:

John Smith
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Tom Jones
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Mary Doe
(XXX) XXX-XXXX
(XXX) XXX-XXXX

Thanks.

Jeff

 

[Arvin Meyer [MVP]]

Then the first one was what you wanted:

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

What you need is a nested loop (a loop inside the loop which does the email)
so that different messages can be sent based on the recordset. It may even
be simpler than that if your example only changes the name variable. Just do
something like:

With objEml
.To = strTo

.Subject = strSubject

If Not IsNull(varMsg) Then
.Body = rst!FullName & vbCrLf & varMsg
End If
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jeff Garrison]

What References need to be included to make this work?

 

[Jeff Garrison]

And...

What would the syntax of the function call be?
EMail ...

 

[Arvin Meyer [MVP]]

What References need to be included to make this work?

 

[Arvin Meyer [MVP]]

Just the one to the Outlook Object Library. Or change the function to Dim as
an Object. Instead of:

Dim objOutl As Outlook.Application
use
Dim objOutl As Object
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com