Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
I'm trying to send an open spreadsheet as an attachment from code in Outlook
2003. I tried using the excel command SendMail but that triggers a security warning. The code fails when I try to add the workbook as an attachment. Sub temp() Dim xlApp As New Excel.Application Dim appwbook As Excel.Workbook Dim appwsheet As Excel.Worksheet Set xlApp = New Excel.Application Set appwbook = xlApp.Workbooks.Add Set appwsheet = appwbook.Worksheets(1) xlApp.Visible = True Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) objMail.BodyFormat = olFormatHTML objMail.Recipients.Add ") objMail.Subject = "this is the subject" objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!! objMail.Display End Sub -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
Based on the following, which was copied from Outlook VBA help, looks like
you need the complete path for your Attachments.Add statement. Set myOlApp = CreateObject("Outlook.Application") Set myItem = myOlApp.CreateItem(olMailItem) Set myAttachments = myItem.Attachments myAttachments.Add "C:\My Documents\Q496.xls", _ olByValue, 1, "4th Quarter 1996 Results Chart" The last line is the one I was referring to. "BillyRogers" wrote: I'm trying to send an open spreadsheet as an attachment from code in Outlook 2003. I tried using the excel command SendMail but that triggers a security warning. The code fails when I try to add the workbook as an attachment. Sub temp() Dim xlApp As New Excel.Application Dim appwbook As Excel.Workbook Dim appwsheet As Excel.Worksheet Set xlApp = New Excel.Application Set appwbook = xlApp.Workbooks.Add Set appwsheet = appwbook.Worksheets(1) xlApp.Visible = True Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) objMail.BodyFormat = olFormatHTML objMail.Recipients.Add ") objMail.Subject = "this is the subject" objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!! objMail.Display End Sub -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
After reading your post a little closer, I'm not sure you can send an open
file as an attachment. That is probably why you got the security warning when you tried it from the menu. "JLGWhiz" wrote: Based on the following, which was copied from Outlook VBA help, looks like you need the complete path for your Attachments.Add statement. Set myOlApp = CreateObject("Outlook.Application") Set myItem = myOlApp.CreateItem(olMailItem) Set myAttachments = myItem.Attachments myAttachments.Add "C:\My Documents\Q496.xls", _ olByValue, 1, "4th Quarter 1996 Results Chart" The last line is the one I was referring to. "BillyRogers" wrote: I'm trying to send an open spreadsheet as an attachment from code in Outlook 2003. I tried using the excel command SendMail but that triggers a security warning. The code fails when I try to add the workbook as an attachment. Sub temp() Dim xlApp As New Excel.Application Dim appwbook As Excel.Workbook Dim appwsheet As Excel.Worksheet Set xlApp = New Excel.Application Set appwbook = xlApp.Workbooks.Add Set appwsheet = appwbook.Worksheets(1) xlApp.Visible = True Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) objMail.BodyFormat = olFormatHTML objMail.Recipients.Add ") objMail.Subject = "this is the subject" objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!! objMail.Display End Sub -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
Try:
Add a reference to Microsoft Outlook in VBE (Tools References). Add this function to a module Function msgA(mySubject, myBody, myTo, myCC, myAttachment) Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") With OutlookApp.CreateItem(olMailItem) .Subject = mySubject .Body = myBody .To = myTo .cc = myCC .Attachments.Add myAttachment .Send End With Set OutlookApp = Nothing End Function In your userform, add a button and in the click event add the following lines: Subject = "Test" Body = "Attached Excel file" A = " CC = " msgA Subject, Body, A, CC, ActiveWorkbook.FullName This is working for me with Excel 2000 pro. NB. The code snippet is not mine. But I do not remember where I picked from Internet. I hope this helps. Regards, Karim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
Try:
Add a reference to Microsoft Outlook in VBE (Tools References). Add this function to a module Function msgA(mySubject, myBody, myTo, myCC, myAttachment) Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") With OutlookApp.CreateItem(olMailItem) .Subject = mySubject .Body = myBody .To = myTo .cc = myCC .Attachments.Add myAttachment .Send End With Set OutlookApp = Nothing End Function In your userform, add a button and in the click event add the following lines: Subject = "Test" Body = "Attached Excel file" A = " CC = " msgA Subject, Body, A, CC, ActiveWorkbook.FullName This is working for me with Excel 2000 pro. NB. The code snippet is not mine. But I do not remember where I picked from Internet. I hope this helps. Regards, Karim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
Try:
Add a reference to Microsoft Outlook in VBE (Tools References). Add this function to a module Function msgA(mySubject, myBody, myTo, myCC, myAttachment) Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") With OutlookApp.CreateItem(olMailItem) .Subject = mySubject .Body = myBody .To = myTo .cc = myCC .Attachments.Add myAttachment .Send End With Set OutlookApp = Nothing End Function In your userform, add a button and in the click event add the following lines: Subject = "Test" Body = "Attached Excel file" A = " CC = " ActiveWorkbook.Save 'must save before sending msgA Subject, Body, A, CC, ActiveWorkbook.FullName This is working for me in Excel 2000 pro. NB. The code snippet is not mine. But I do not remember where I picked it from Internet. I hope this helps. Regards, Karim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
JLGWhiz,
It looks like I'm going to have to save each workbook. The sendmail command will send an open workbook that hasn't been saved but it triggers warnings which I want to avoid. Karim--thanks for the suggestion but this code is already in outlook and there is no userform and I've already tried the attachments.add method. It looks like it only accepts a path to a workbook. -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 "JLGWhiz" wrote: After reading your post a little closer, I'm not sure you can send an open file as an attachment. That is probably why you got the security warning when you tried it from the menu. "JLGWhiz" wrote: Based on the following, which was copied from Outlook VBA help, looks like you need the complete path for your Attachments.Add statement. Set myOlApp = CreateObject("Outlook.Application") Set myItem = myOlApp.CreateItem(olMailItem) Set myAttachments = myItem.Attachments myAttachments.Add "C:\My Documents\Q496.xls", _ olByValue, 1, "4th Quarter 1996 Results Chart" The last line is the one I was referring to. "BillyRogers" wrote: I'm trying to send an open spreadsheet as an attachment from code in Outlook 2003. I tried using the excel command SendMail but that triggers a security warning. The code fails when I try to add the workbook as an attachment. Sub temp() Dim xlApp As New Excel.Application Dim appwbook As Excel.Workbook Dim appwsheet As Excel.Worksheet Set xlApp = New Excel.Application Set appwbook = xlApp.Workbooks.Add Set appwsheet = appwbook.Worksheets(1) xlApp.Visible = True Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) objMail.BodyFormat = olFormatHTML objMail.Recipients.Add ") objMail.Subject = "this is the subject" objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!! objMail.Display End Sub -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
Try:
Add a reference to Microsoft Outlook in VBE (Tools References). Add this function to a module Function msgA(mySubject, myBody, myTo, myCC, myAttachment) Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") With OutlookApp.CreateItem(olMailItem) .Subject = mySubject .Body = myBody .To = myTo .cc = myCC .Attachments.Add myAttachment .Send End With Set OutlookApp = Nothing End Function In your userform, add a button and in the click event add the following lines: Subject = "Test" Body = "Attached Excel file" A = " CC = " ActiveWorkbook.Save msgA Subject, Body, A, CC, ActiveWorkbook.FullName This is working for me in Excel 2000 pro. NB. The code snippet is not mine. But I do not remember where I picked it from Internet. I hope this helps. Regards, Karim |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attach Open Excel Spreadshee to Email
JLGWhiz wrote: After reading your post a little closer, I'm not sure you can send an open file as an attachment. That is probably why you got the security warning when you tried it from the menu. "JLGWhiz" wrote: Based on the following, which was copied from Outlook VBA help, looks like you need the complete path for your Attachments.Add statement. Set myOlApp = CreateObject("Outlook.Application") Set myItem = myOlApp.CreateItem(olMailItem) Set myAttachments = myItem.Attachments myAttachments.Add "C:\My Documents\Q496.xls", _ olByValue, 1, "4th Quarter 1996 Results Chart" The last line is the one I was referring to. "BillyRogers" wrote: I'm trying to send an open spreadsheet as an attachment from code in Outlook 2003. I tried using the excel command SendMail but that triggers a security warning. The code fails when I try to add the workbook as an attachment. Sub temp() Dim xlApp As New Excel.Application Dim appwbook As Excel.Workbook Dim appwsheet As Excel.Worksheet Set xlApp = New Excel.Application Set appwbook = xlApp.Workbooks.Add Set appwsheet = appwbook.Worksheets(1) xlApp.Visible = True Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) objMail.BodyFormat = olFormatHTML objMail.Recipients.Add ") objMail.Subject = "this is the subject" objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!! objMail.Display End Sub -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attach all Open Workbooks to email as separate files? | Excel Programming | |||
Generate PDF from Excel & Attach to Email | Excel Programming | |||
How do I attach just one Excel worksheet in an email | Excel Programming | |||
How to get an object reference to an ALREADY open excel spreadshee | Excel Programming | |||
Open an email and attach my excel file | Excel Programming |