Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / January 2008

Tip: Looking for answers? Try searching our database.

email attachment(s) from query results

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rookie - 25 Jan 2008 00:16 GMT
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 - 25 Jan 2008 04:08 GMT
You'll need to read up on Automation with Outlook. You can
programmatically create an email, and attach several files.
-Tom.

>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
[quoted text clipped - 7 lines]
>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!
Rookie - 25 Jan 2008 15:07 GMT
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!

> You'll need to read up on Automation with Outlook. You can
> programmatically create an email, and attach several files.
[quoted text clipped - 11 lines]
> >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!
Rookie - 25 Jan 2008 17:57 GMT
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$ = "Sarah.Sigler@CIGNA.com"

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

> 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
[quoted text clipped - 26 lines]
> > >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!
Rookie - 25 Jan 2008 20:58 GMT
Nevermind.  I figured out a solution-it's working fine now.

> 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
[quoted text clipped - 94 lines]
> > > >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 - 26 Jan 2008 01:57 GMT
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 - 28 Jan 2008 16:10 GMT
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$ = "recip@domain.com"

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

> Sweet. Share it with the group so you may be able to help another user
> down the road.  Pay it forward.
[quoted text clipped - 4 lines]
> >
> <clip>
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.