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 "
.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

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"DannyJ" wrote in message
...
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

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

"Ken Slovak - [MVP - Outlook]" wrote:

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 "
.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

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"DannyJ" wrote in message
...
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

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Dan Guzman" Dan wrote in message
...
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

As I stated in the first post, the error is on the attachment line. Repeated
below:

.Attachments.Add "C:\Temp\Test.txt"

I've tried changing to late binding, and still get the same error mesage.
If I remove this one line, the code works fine (email sent or displayed) .
This has been very frustrating, since all online help seems to give the same
code as an example, and errors are related to binding and other things.


"Ken Slovak - [MVP - Outlook]" wrote:

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Dan Guzman" Dan wrote in message
...
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

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"

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Dan Guzman" wrote in message
...
As I stated in the first post, the error is on the attachment line.
Repeated
below:

.Attachments.Add "C:\Temp\Test.txt"

I've tried changing to late binding, and still get the same error mesage.
If I remove this one line, the code works fine (email sent or displayed) .
This has been very frustrating, since all online help seems to give the
same
code as an example, and errors are related to binding and other things.