How do I create an e-mail distribution list?

[Guest]

How do i create a report that will be cut-and-paste-able into outlook from a
contacts database? Or is there a way to do this automatically (i.e.
ommitting the cut-and-paste step)?

The standard access report format doesn't seem to work.

Thanks!

 

[Larry Linson]

How do i create a report that will be cut-and-paste-able into outlook from
a
contacts database? Or is there a way to do this automatically (i.e.
ommitting the cut-and-paste step)?

The standard access report format doesn't seem to work.

I wrote a little VBA code to extract the e-mail addresses from a small
database, and create a comma-delimited text file, which can then be copied
and pasted. Here's the working code, copied unchanged.

It's used so infrequently that I just executed it from the Immediate
Window... and pass it the file name I want to use. It uses the path to the
CurrentDB for the path, so the file is stored in the same folder as the
CurrentDB. You'll need to create your own qryExtractEMail to use your table,
and extract whatever you want, but include the e-mail address in a Field
named EMail.

It's a function because I prefer that when working from the Immediate
Window -- if you look closely you'll see I don't even set the return value
for the Function... it always returns "False".

Here's how I'd execute it from the Immediate Window:

? EMailText ("MyEMailAddresses")

And here's the actual code:

'---------------------------------------------------------------------------------------
' Procedure : EMailText
' DateTime : 11/10/2006 22:17
' Author : LARRY LINSON
' Purpose : Write E-Mail Addresses to a Text File
'---------------------------------------------------------------------------------------
'
Function EMailText(pstrFN As String) As Boolean
On Error GoTo Proc_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDBName As String
Dim strMailAddr As String
Dim strFPN As String
Dim intFNo As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryExtractEMail", dbOpenDynaset)
strDBName = CurrentDb().Name 'Fully-qualified Path and File
' Extract through final \ prior to filename.ext for Path
strFPN = Left(strDBName, InStrRev(strDBName, "\"))
intFNo = FreeFile
If Len(pstrFN) > 0 Then 'If a FileName was provided
strFPN = strFPN & pstrFN & ".txt" 'Concatenate Path with supplied
filename
Open strFPN For Output As intFNo 'Open Text File
rs.MoveFirst 'Move to first record
Do Until rs.EOF 'Do for entire Recordset
If Len(strMailAddr) = 0 Then 'If there's no saved e-mail
strMailAddr = rs("EMail") 'Begin with first one
Else 'Some saved e-mail, so append
strMailAddr = strMailAddr & ", " & rs("EMail")
End If
rs.MoveNext
Loop
Print #intFNo, strMailAddr 'Write Comma-delimited list to file
Close intFNo 'Close Text File
Else 'No FileName was provided, MsgBox
MsgBox "Requires a Name for the TextFile", vbOK, "Name Needed"
End If
rs.Close
Set rs = Nothing
Set db = Nothing

Proc_Exit:
On Error GoTo 0
Exit Function

Proc_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
EMailText of Module Module1"
Resume Proc_Exit

End Function

 

[Chuck]

How do i create a report that will be cut-and-paste-able into outlook from a
contacts database? Or is there a way to do this automatically (i.e.
ommitting the cut-and-paste step)?

The standard access report format doesn't seem to work.

Thanks!

If you don't know any more about writing code than I do, you can let Access do
all the work.

This assumes that a table exists that has fields for: LastName, FirstName,
EmailAddress.

Make a query that has those fields. Do any filtering and sorting here.

Make a report from that query that has a single line in the detail section:

="<" & [EmailAddress] & ", " & " " " " [LastName] & ", "_
& [FirstName] & " " " "

The format of this line of text is for Outlook. Other Email programs may
require a different format. Pegasus requires that the names come first and the
EmailAddress comes last.

Make the detail section just barely tall enough for that single line.

Then make a macro:
Action
OpenReport
Report Name: {your choice}
View: Print Preview
Output To
Object Type: Report
Output Format: MS-DOS TEXT
Output file: {complete path}
"E:\folder 1\folder 2\ {report name}" &_
(Format(Date(),"yymmdd")) & ".txt"
Auto Start: NO
Close

The date is unnecessary, it's just to keep track of when the file was
generated.

The resulting txt file will look like:
<[email protected]> "Doe, John"
<[email protected]> "Doe, Marry"

It can be copied and pasted into Outlook.

Just a wizard prodder
Chuck
--