Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=567616 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach all Open Workbooks to email as separate files?
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 = " .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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach all Open Workbooks to email as separate files?
I wish I could look this up on your site, but how do I add a referenc to the Microsoft Outlook Library? I know I saw it somewhere, I don' remember though. Thanks for the help, ~ -- nbaj2 ----------------------------------------------------------------------- nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648 View this thread: http://www.excelforum.com/showthread.php?threadid=56761 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach all Open Workbooks to email as separate files?
1) Go to the VBA editor, Alt -F11
2) ToolsReferences 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Separate Excel Files in Separate Windows | Excel Discussion (Misc queries) | |||
How can I open from Web Excel Files in a separate Window? | Excel Discussion (Misc queries) | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
How can I get existing Excel workbooks to open in separate windows | Excel Worksheet Functions | |||
Open an email and attach my excel file | Excel Programming |