Attach all Open Workbooks to email as separate files?

I figured out how to attach the active workbook to an email, but is there away to attach all open workbooks to a single email as separate files?

I have no idea how to do it. What I have below is how I'm attaching the active sheet.

Anyway to select all open workbooks so I can set the macro to open run and attach all of them? I know how to do all of it except attaching all of them.

Anyone have any ideas or done this before?

Thanks,

~J

Application.ScreenUpdating = True
Dim myattachment
Dim olNs As Object
Dim olMailItem
Dim olMail As Object
Dim olApp As Object
Dim ATTACH1 As String


ATTACH1 = ActiveWorkbook.FullName
Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
olMail.Display

olMail.To = "RECIPIENTS"
olMail.CC = "RECIPIENTS"
'olMail.BCC =
olMail.Subject = "Daily Report for " & Format(Date - 1, "mmm-d-yy")
olMail.Body = "MESSAGE"

Set myattachment = olMail.Attachments
myattachment.Add ATTACH1
'Stop
''olMail.Send
'olNs.Logoff
'Set olNs = Nothing
'Set olMail = Nothing
'Set olApp = Nothing

End Sub

Try this

Change display to send if it is working correct


Sub Mail_workbook_Outlook()
'This example send the last saved version of each open workbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
For Each wb In Application.Workbooks
If wb.Windows(1).Visible And wb.Path <> "" Then
.Attachments.Add wb.FullName
End If
Next
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2bxhy7_1154548825.5326@excelforum-nospam.com> wrote in message
news:nbaj2k.2bxhy7_1154548825.5326@excelforum-nospam.com...
>
> I figured out how to attach the active workbook to an email, but is
> there away to attach all open workbooks to a single email as separate
> files?
>
> I have no idea how to do it. What I have below is how I'm attaching
> the active sheet.
>
> Anyway to select all open workbooks so I can set the macro to open run
> and attach all of them? I know how to do all of it except attaching
> all of them.
>
> Anyone have any ideas or done this before?
>
> Thanks,
>
> ~J
>
> Application.ScreenUpdating = True
> Dim myattachment
> Dim olNs As Object
> Dim olMailItem
> Dim olMail As Object
> Dim olApp As Object
> Dim ATTACH1 As String
>
>
> ATTACH1 = ACTIVEWORKBOOK.FULLNAME
> Set olApp = CreateObject("Outlook.Application")
> Set olNs = olApp.GetNamespace("MAPI")
> olNs.Logon
> Set olMail = olApp.CreateItem(olMailItem)
> olMail.Display
>
> olMail.To = "RECIPIENTS"
> olMail.CC = "RECIPIENTS"
> 'olMail.BCC =
> olMail.Subject = "Daily Report for " & Format(Date - 1, "mmm-d-yy")
> olMail.Body = "MESSAGE"
>
> Set myattachment = olMail.Attachments
> myattachment.Add ATTACH1
> 'Stop
> ''olMail.Send
> 'olNs.Logoff
> 'Set olNs = Nothing
> 'Set olMail = Nothing
> 'Set olApp = Nothing
>
> End Sub
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=567616
>

I wish I could look this up on your site, but how do I add a reference to the Microsoft Outlook Library? I know I saw it somewhere, I don't remember though.

Thanks for the help,

~J

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2byqmg_1154606707.9664@excelforum-nospam.com> wrote in message
news:nbaj2k.2byqmg_1154606707.9664@excelforum-nospam.com...
>
> I wish I could look this up on your site, but how do I add a reference
> to the Microsoft Outlook Library? I know I saw it somewhere, I don't
> remember though.
>
> Thanks for the help,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=567616
>