select and e-mail multiple attachments via Outlook

[Silvio]

Hello, I need help in writing a code that will send selected attachments via
e-mail using Outlook 2007. Currently, I am using a List Box to generate a
list of files located on the server. One of the hidden columns in from the
List Box, has the full path of each listed file (e.g. C:\Attachemnts\1.doc
etc). I would like to allow the user to select whatever file from the List
(multi selection) and then with a click of a button create an e-mail with all
the selected files attached to it.

I am somewhat familiar with creating an e-mail in Access using vba, however
I don’t know how perform this extra step.

Thank you,
Silvio

 

[Arvin Meyer [MVP]]

Try this code:

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

 

[Silvio]

Thanks Arvin for the hint, but I need extra help

for the section .Attachments.Add "Path"
since all selected attachments from the list Box need to attach.

I gues it need to be something like:

for each item selected
.Attachments.Add = me.MyList.column(1)
loop

 

[Silvio]

OK I think I am getting close to what I need... however, the code below keeps
attaching the last file selected for the number of item selected. Meaning, if
I select 3 files, the last file selected is added 3 time.

Private Sub cmdeMailAttach_Click()

Dim MyItem As Variant
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(0)

With objOutlookMsg
..Subject = "test"
..HTMLBody = "Please see the attached documents."

For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1))
Next MyItem
End With

objOutlookMsg.Display

End Sub

 

[Douglas J. Steele]

For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyFiles))
Next MyItem

 

[Douglas J. Steele]

Oops. Sorry, that should have been

For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyItem))
Next MyItem

 

[Silvio]

Thanks Douglas, this works perfectly. To take this one step furted, how can I
also always include a specific report from Access as part of the attachments?
In few words, I need to e-mail the invoice as PDF (without save it anywere)
with all the supporting documentations (my attachments).

PS. I am using Access 2007

 

[Douglas J. Steele]

To be honest, I've never tried.

See whether Tony Toews has anything in his Access Email FAQ at
http://www.granite.ab.ca/access/email.htm

 

[Silvio]

I was thinking the same thing to temporarely crete the invoice and then
delete it.

I am not sure how to attach another file into the newely crerate e-mail with
multiple other attachment already there. Can you help? Lets assume that the
invoice will always be in the same location: C:\Invoice.pdf

With objOutlookMsg
..Subject = "Claim Documentation"
..To = Me.Adjuster.Column(2)
For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1, MyItem))
Next MyItem

End With

Thanks Douglas, this works perfectly. To take this one step furted, how can I
also always include a specific report from Access as part of the attachments?
In few words, I need to e-mail the invoice as PDF (without save it anywere)
with all the supporting documentations (my attachments).

PS. I am using Access 2007

Oops. Sorry, that should have been

[quoted text clipped - 61 lines]

.

Couldn't you output the file as PDF to a directory, attach it to your e-mail
(using olkMsg.Attachments.Add strPathToPDF or something like it) and then
once the e-mail is sent, ...

Kill strPathToPDF

to delete the file. I just wonder why you shouldn't be able to save the file
anywhere...

 

[Douglas J. Steele]

With objOutlookMsg
..Subject = "Claim Documentation"
..To = Me.Adjuster.Column(2)
For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyItem))
Next MyItem
Set objOutlookAttach = .Attachments.Add("C:\Invoice.pdf")
End With

Incidentally, you might want to reconsider where you're writing the pdf
file. I'm not sure you're able to write to the root of C: in Windows 7.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was thinking the same thing to temporarely crete the invoice and then
delete it.

I am not sure how to attach another file into the newely crerate e-mail
with
multiple other attachment already there. Can you help? Lets assume that
the
invoice will always be in the same location: C:\Invoice.pdf

With objOutlookMsg
.Subject = "Claim Documentation"
.To = Me.Adjuster.Column(2)
For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyItem))
Next MyItem

End With

Thanks Douglas, this works perfectly. To take this one step furted, how
can I
also always include a specific report from Access as part of the
attachments?
In few words, I need to e-mail the invoice as PDF (without save it
anywere)
with all the supporting documentations (my attachments).

PS. I am using Access 2007

Oops. Sorry, that should have been

[quoted text clipped - 61 lines]

.

Couldn't you output the file as PDF to a directory, attach it to your
e-mail
(using olkMsg.Attachments.Add strPathToPDF or something like it) and then
once the e-mail is sent, ...

Kill strPathToPDF

to delete the file. I just wonder why you shouldn't be able to save the
file
anywhere...

 

[Silvio]

Easy enough. Thank you very much.

With objOutlookMsg
..Subject = "Claim Documentation"
..To = Me.Adjuster.Column(2)
For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyItem))
Next MyItem
Set objOutlookAttach = .Attachments.Add("C:\Invoice.pdf")
End With

Incidentally, you might want to reconsider where you're writing the pdf
file. I'm not sure you're able to write to the root of C: in Windows 7.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was thinking the same thing to temporarely crete the invoice and then
delete it.

I am not sure how to attach another file into the newely crerate e-mail
with
multiple other attachment already there. Can you help? Lets assume that
the
invoice will always be in the same location: C:\Invoice.pdf

With objOutlookMsg
.Subject = "Claim Documentation"
.To = Me.Adjuster.Column(2)
For Each MyItem In Me.MyFiles.ItemsSelected
Set objOutlookAttach = .Attachments.Add(Me.MyFiles.Column(1,
MyItem))
Next MyItem

End With

Silvio wrote:
Thanks Douglas, this works perfectly. To take this one step furted, how
can I
also always include a specific report from Access as part of the
attachments?
In few words, I need to e-mail the invoice as PDF (without save it
anywere)
with all the supporting documentations (my attachments).

PS. I am using Access 2007

Oops. Sorry, that should have been

[quoted text clipped - 61 lines]

.

Couldn't you output the file as PDF to a directory, attach it to your
e-mail
(using olkMsg.Attachments.Add strPathToPDF or something like it) and then
once the e-mail is sent, ...

Kill strPathToPDF

to delete the file. I just wonder why you shouldn't be able to save the
file
anywhere...

.

 

[David W. Fenton]

you might want to reconsider where you're writing the pdf
file. I'm not sure you're able to write to the root of C: in
Windows 7.

Not without non-standard user permissions, or running your Access
app as administrator.

 

[Douglas J. Steele]

Not without non-standard user permissions, or running your Access
app as administrator.

That's what I thought. I just didn't have a Win7 machine handy to test at
the time.

Thanks for confirming.

 

[David W. Fenton]

That's what I thought. I just didn't have a Win7 machine handy to
test at the time.

Technically, the security restrictions apply before Vista/Win7, but
until Vista, running as an administrative logon got you
administrative permissions for everything. With the introduction of
UAC, by default, all applications run with a user-level security
token even when you're logged on as an administrator. Thus, by
default, your apps behave in Vista/Win7 as they would have in
Win2000 and WinXP running with user-level instead of administrative
permissions.

The fact that 99% of non-domain users would run as administrator hid
from all those users the fact that permissions had changed in
Win2000 to limit write access to the root of C:, the Windows folder
and the programs folder.

Anyone who is running into this problem now is someone who is
completely ignorant of proper usage of NTFS security as it has
existed for OVER TEN YEARS (i.e., since the release of Win2000). I
have been appalled at how many otherwise knowledgable people have
remained so completely ignorant of these issues, and am glad that
Vista/Win7 are finally forcing the issue, i.e., requiring that they
now learn to not assume administrative permissions and place files
requiring full access in locations that that were designed for that
purpose FOR OVER TEN YEARS.