You'll need to read up on Automation with Outlook. You can
programmatically create an email, and attach several files.
-Tom.
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>