Automatically adding attachments and sending them

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks,

I am trying to create a macro that at the push of a button creates an email
with a subject of "Cims data" picks up and attaches all the files from a
predefined, folder, and sends it to a predefined email address.

i.e. no need to manually add the files, the subject or the recipient.

Any ideas? I know a little VBA in Excel but nothing about Outlook, though I
assume there are similarities.

Many thanks,

A
 
The only tricky part of what you want to do is to know how many files to
attach are in a specific folder and how to get those files. For that you
will need to get the folder, get the files count in the folder and add each
item. For simplicity I'll show some code that adds two pre-determined files
as attachments.

Sub Foobar()
Dim oApp As Outlook.Application
Dim oNS As Outlook.NameSpace
Dim oMail As Outlook.MailItem

Set oApp = CreateObject("Outlook.Application")
Set oNS = oApp.GetNameSpace("MAPI")
oNS.Logon

Set oMail = oApp.CreateItem(olMailItem)
With oMail
.Subject = "Cims data"
.Body = "Some body text"
.Attachments.Add "c:\myFolder\File1.xls"
.Attachments.Add "c:\myFolder\File2.xls"
.Recipients.Add "(e-mail address removed)"
.Recipients.ResolveAll
.Send
End With
End Sub

Accessing Recipients and sending may fire the Outlook object model guard if
running the code from Excel. For your options with that see
http://www.outlookcode.com/d/sec.htm
 
I've tried your exact code, in a Microsoft Access module, with the Outlook
reference added, and get the following error message on the attachment line:

'Operation is not supported for this type of Object'

What am I missing?

Dan
 
What line do you get the error on?

I'm not sure about that error, attaching a file is certainly supported for
an email item, so I don't know why you would get that.
 
I'm sure it's frustrating. I've never had any problems adding attachments
and I use code like that all the time.

The only things I can think of offhand are to see if the same code when run
as an Outlook macro from within Outlook fires the same error, try the code
on another machine to see if the problem is machine specific and to test an
alteration of the code to change that line to instantiate an Attachment
object:

Dim oAttach As Outlook.Attachment
Set oAttach = .Attachments.Add "C:\Temp\Test.txt"
 
Back
Top