email attachment(s) from query results

[Rookie]

Through a series of forms and queries, my users select a list of pdf files
that should be sent via email. This list might contain anywhere from 1 to 20
pdf's, and the actual files and number of files vary with each use. At this
point, this list is emailed to someone who manually creates an email with the
appropriate attachments. This is not a terribly efficient workflow.

Already existing in the database, I have a hyperlink field containing the
full path to each of the pdf files. What I'm looking for is some way for
access to loop through the final query results, retrieve the actual file from
the hyperlink field, and then attach these as pdfs to an email. Is this
possible? If so, any assistance would be greatly appreciated. Thank you!

 

[Tom van Stiphout]

On Thu, 24 Jan 2008 16:16:35 -0800, Rookie

You'll need to read up on Automation with Outlook. You can
programmatically create an email, and attach several files.
-Tom.

 

[Rookie]

Thank you for the response. I have done quite a bit of reading on Outlook
Automation, and I have found mountains of information. I am able to create
and send an email with automation-I am also able to add a static attachment
or a single variable attachment. What I have not been able to find is any
information about attaching a variable number of variable attachments-where
the number and name/path of the attachments is the query recordset.

There is quite a bit of info on looping through a query recordset to get the
email "to" address, and I attempted to adapt such coding to loop through and
retrieve the file path from the query recordset. This attempt was terribly
unsuccessful-but I am by no means a VBA expert (I am much closer to the
novice end of the spectrum).

Any direction would be appreciated. Thank you!

 

[Rookie]

Here is the code I am using. If I comment the attachments loop-it all goes
swimmingly well. When I uncomment the attachment loop, I get a runtime
error-not the same error number, but always the same message-"Operation is
not supported for this type of object." on the MyMail.Attachments.Add
MailList("Link") line. But the screentip indicates that MailList("Link") is
pointing to the correct file.

Option Compare Database

Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim Bodyfile As String
Dim fso As FileSystemObject
Dim myBody As TextStream
Dim myBodyText As String
Dim Recip As String

Set fso = New FileSystemObject

Subjectline$ = "Testing new Document List Email"
Recip$ = "(e-mail address removed)"

Bodyfile$ = "\\wldfs035\Landata_chc\INNOVATION LAB\Sarah\Other
Stuff\Library\Testing body of email.doc"
Set myBody = fso.OpenTextFile(Bodyfile, ForReading, False, TristateUseDefault)
myBodyText = myBody.ReadAll
myBody.Close

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()
Set qdf = db.QueryDefs("Select")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set MailList = qdf.OpenRecordset





Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = Recip$
MyMail.Subject = Subjectline$
MyMail.Body = myBodyText


Do Until MailList.EOF
MyMail.Attachments.Add MailList("Link")
MailList.MoveNext
Loop

MyMail.Send

Set MyMail = Nothing

Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing


End Function

 

[Rookie]

Nevermind. I figured out a solution-it's working fine now.

 

[Tom van Stiphout]

On Fri, 25 Jan 2008 12:58:01 -0800, Rookie

Sweet. Share it with the group so you may be able to help another user
down the road. Pay it forward.

-Tom.

Nevermind. I figured out a solution-it's working fine now.

<clip>

 

[Rookie]

Here's the code to loop through the query to determine which files will be
the email attachments-although I'm not having much luck with the external doc
as the email body-that part I can code in VB (but if you copy this
code-realize that the external doc for email body part is not quite working):


Private Sub Command21_Click()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim Bodyfile As String
Dim fso As FileSystemObject
Dim myBody As TextStream
Dim myBodyText As String
Dim Recip As String
Dim Att As String
Dim mlAdd As String

mlAdd$ = Me.mlAdd.Value



Set fso = New FileSystemObject

Subjectline$ = "Testing new Document List Email"
Recip$ = "(e-mail address removed)"


Bodyfile$ = "\\wldfs035\Landata_chc\INNOVATION LAB\Sarah\Other
Stuff\Library\Testing body of email.doc"
Set myBody = fso.OpenTextFile(Bodyfile, ForReading, False, TristateUseDefault)
myBodyText = "Send these documents to: " & mlAdd$
myBody.Close

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()
Set qdf = db.QueryDefs("Select")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set MailList = qdf.OpenRecordset





Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = Recip$
MyMail.Subject = Subjectline$





Do Until MailList.EOF
Att$ = MailList("Link")

MyMail.Attachments.Add Att$

MailList.MoveNext
Loop

MyMail.Body = myBodyText

MyMail.Send

Set MyMail = Nothing

Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing







End Sub