Send Multiple Attachments from Outlook

[lgray]

I've created a table that includes hyperlinks, file names and directory Path
to a group of documents that need to be attached and sent in one email. The
files that need to be attached are variable in both name as well as how many
files are to be attached. The files to be sent will be selected via a
"Yes/No, Checkmark" field type in a form. I know how to create a separate
table to include just the selected records to be sent, but is there a way to
attach these files with variable names?

 

[dch3]

I'm not following what you mean by the last statement "but is there a way to
attach these files with variable names?". Do mean using a variable in code
that is set to the file's name?

At any rate, when your working with the MailItem object, VBA doesn't care
where the name comes from - a table lookup, looping through a recordset,
using the common dialog, or even if its hard coded.

 

[lgray]

I've seen solutions in the Developer threads that address the issue of
attaching multiple attachments to Outlook, but the names of the files stay
the same, (i.e. "Financial Results.xls", "Monthly Budget.doc") within the
Code.

In my case, the file names can vary, depending on the Customer file they are
working on. The names of the files that have been created are usually
prefixed with a Customer ID, then the category of file (ie Legal
contracts,".doc", Service applications,".xls", scanned pdfs). Depending on
the details of the employees task in reviewing the customer information, it
could be any number of files with any naming convention. Basicly they've
requested me to package the documents and send as an email.

I hope this helps clarify

 

[dch3]

Currently, when your users send out the emails, are they manually selecting
the files to attach? Are the files preexisting files or do are they created
shortly before the email is sent out?

 

[lgray]

Prior to my automation work, all of the documents were manually created in
word and then combined with related scanned pdf's into a larger pdf file that
was then attached in Outlook.
My automation eliminated all of the manual Word docs that they were saving,
literally, in a file drawer, and replaced it with automated documents via
mailmerge etc.
But to preserve the ability to send out all of their documents in one email,
I've been asked to find a way for them to select the documents that are
created via automation, (prior to the email process) , preferably by using a
"checkbox" and then having the selected items sent to the email. I have a
list of all of the available attachments stored in a table. (I don't think
this part is relevant to the task at hand, but the table has a Hyperlink
field that goes directly to the document for view.) All of the documents are
stored in a central location on the network.

My thought was to collect the selected documents in a temporary folder and
then somehow, through vba, attach them into to an email. That is where my
question comes in.

I hope that all of this detail clarifies, rather than confuses my request
for help.

 

[dch3]

That tells me what I'm looking for...

As long as the information in the table is the full path (C:\documents and
settings\dch3\my documents\report.pdf), you can create the mail message in
vba using the Outlook as in

[sample code off the top of my head, google around and you'll be able to
find the correct syntax, this is just to get you started]

Set apOutlook = CreateObject(Outlook.Application)
Set newMail = apOutlook.CreateItem olMailItem (Correct syntax)

Then use the attachments collection of the newMail object to attach each
individual file.

Set newAttachment = newMail.Attachments.Add [fileName] (There's some addl
statements/parameters but I don't remember them off the top of my bald head)

Now to add multiple attachements, encapsule the above statement in a loop
that goes through the recordset with the file names rougly...

Set rs = Db.OpenRecordSet (tblName, dbForwardOnly)
while not rs.eof
Set newAttachment = newMail.Attachments.Add rs("fileNameField")
wend

Then set the other properties for the MailItem as in

With newMail
.To = "(e-mail address removed)"
.Subject = "Your Office 2007 Ribbon Sucks Like Dyvorak"
.Body = "Did you actually ask anyone what we thought about it?"
wend

Then either send it or display it as in

newMail.Send
or
newMail.Display

That's it in a nutshell. And again - the code is off the top of my head and
WILL need tweaking. For a full understanding of how to use code to send mail
via Outlook, see the OUTLOOK OBJECT MODEL in OUTLOOK VBA HELP and seach
MailItem.

 

[dch3]

Since the documents are being created via automation, I would put the code
that creates the MailItem in a separate sub - createMailItem - and then call
the sub from the wathever code/event runs the automation making it a single
click process. Also you can programmically request READ and DELIVERY
RECEIPTS, again look at the properties for MailItem under Outlook VBA help
for the specific property names.

 

[dch3]

Turns out did have an actual example closer than I thought...

Be certain to set a reference to the Outlook 11 library

sub createOutlookMailItem

Dim objOutlook As Outlook.Application
Dim newMail As MailItem

Set objOutlook = CreateObject("Outlook.application")
Set newMail = objOutlook.CreateItem(olMailItem)

newMail.To = "(e-mail address removed)"
newMail.Body = "The Office ribbon sucks like Dvorak"

Set newMailAttachments = newMail.Attachments

'If you're pulling records from a file, you'd start the loop here
changing the next
'line to file1 = rs("fieldName") or whichever way you prefer to pull the
value from
'the recordset you're working with, this code was done as a
proof-of-concept,
'so I used the file1 variable just to make the next line easier to read
it can
'be eliminated if needed
'I would definately check to see if the file exists first using the
FileExists property
'of the File System Object
'Set fs = CreateObject("Scripting.FileSystemObject")
'fs.FileExists(fileName)
'If FileExists fails, you could then present the CommonDialog to give
the users
'the option of searching for the file themselves (see mvps.org for that
aspect of
'it)

file1 = strTargetFolder & "\rptConfirmation.snp"
newMailAttachments.Add file1, olByValue, 1

'end the loop here

newMail.Subject = "Documents Confirmation"
newMail.OriginatorDeliveryReportRequested = True
newMail.ReadReceiptRequested = True
newMail.Display

Set newMailAttachments = Nothing
Set newMail = Nothing
Set objOutlook = Nothing

end sub

You may also want to check out the .SaveSentMessageFolder property as it
would allow you to have the sent messages saved in a specific folder - either
at the user level or in a public folder.

http://msdn.microsoft.com/en-us/library/aa171932(office.11).aspx

 

[lgray]

Thank you,
You guys are awesome lifesavers. I will work on implementing.

 

[dch3]

You can also print the MailItem to generate backup confirming the files that
were sent for the clients physical file.

 

[lgray]

dch3,
Thank you for your help with this code. I had to put the task aside for
awhile to work on different tasks, so just now, I'm able to focus on this
one. Below is my version of the code. I am getting a "run-time error 424"
object required. Obviously I'm still a novice with code, so can you take a
look to see if you know where the problem is. The table name is
Encroachment_Attachments, and the field name with the path is named:
FullPathToFile

Sub createOutlookMailItem()

Dim objOutlook As Outlook.Application
Dim newMail As MailItem
Dim rs As Recordset

Set objOutlook = CreateObject("Outlook.application")
Set newMail = objOutlook.CreateItem(olMailItem)


newMail.To = "(e-mail address removed)"
newMail.Subject = "Encroachment Documents"


Set newMailAttachments = newMail.Attachments

Set rs = Db.OpenRecordset(Encroachment_Attachments, dbForwardOnly)
file1 = ("FullPathToFile")
While Not rs.EOF
Set newAttachment = newMail.Attachments.Add("File1")
newMailAttachments.Add file1, olByValue, 1
Wend



newMail.Display

newMail.Subject = "Documents Confirmation"
newMail.OriginatorDeliveryReportRequested = True
newMail.ReadReceiptRequested = True
newMail.Display

Set newMailAttachments = Nothing
Set newMail = Nothing
Set objOutlook = Nothing


End Sub

 

[lgray]

fyi the line it is stopping at is:

Set rs = Db.OpenRecordset("Encroachment_Attachments", dbForwardOnly)

 

[lgray]

dch3,
I've gotten past the first error and am now encountering the following
error....

Run time error 3001..... Invalid Argument. Below is the code. I will keep
working on it and let you know if I work it out.

It errors on the followingline...

Set rs = db.OpenRecordset(Encroachment_Attachments, dbForwardOnly)


Sub createOutlookMailItem()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim newMail As MailItem


Set objOutlook = CreateObject("Outlook.application")
Set newMail = objOutlook.CreateItem(olMailItem)


newMail.To = "(e-mail address removed)"
newMail.Subject = "Encroachment Documents"


Set newMailAttachments = newMail.Attachments


'If you're pulling records from a file, you'd start the loop here

Set db = CurrentDb
Set rs = db.OpenRecordset(Encroachment_Attachments, dbForwardOnly)
File1 = rs("FullPathToFile")
While Not rs.EOF
Set newAttachment = newMail.Attachments.Add(File1)
Wend

'changing the next line to file1 = rs("fieldName") or whichever way you
prefer to pull the Value from
'the recordset you're working with, this code was done as a
proof-of-concept,
'so I used the file1 variable just to make the next line easier to read.
It can
'be eliminated if needed. I would definately check to see if the file
exists first using the FileExists Property
'of the File System Object Set fs =
CreateObject("Scripting.FileSystemObject")
'fs.FileExists(fileName) If FileExists fails, you could then present the
CommonDialog to give the Users
'the option of searching for the file themselves (see mvps.org for that
aspect of it)


newMail.Display


'end the loop here

newMail.Subject = "Documents Confirmation"
newMail.OriginatorDeliveryReportRequested = True
newMail.ReadReceiptRequested = True
newMail.Display

Set newMailAttachments = Nothing
Set newMail = Nothing
Set objOutlook = Nothing


End Sub

 

[dch3]

The table name needs to be encapsulated in QUOTES. Once you've done that try
taking out the Parenthesis.

Also, from here on out, you'l want to post any issues as separate posts
since we've got things rolling. I'm about to be tied up for 3 weeks and can't
guarantee that I'll be able to responde. If you have any issues with the
Outlook specific code - MailItem, CreateItem, etc, post the questions in the
OUTLOOK discussion group.

http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx#8

 

[lgray]

Thank you. Just in the last hour I managed to work through all of that and
am trying to get the Recordset bugs worked out. If I can't get it debugged,
I'll post elsewhere, and thank you again for your help.