Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
I am trying to send an active sheet to a set of different email
address's and I keep geting error messages. Here is the code I am trying to use Sub mail() Dim olApp As Outlook.Application Dim olMail As MailItem Set olApp = New Outlook.Application Set olMail = olApp.CreateItem(olMailItem) ActiveSheet.Copy ActiveSheet.SaveAs ActiveSheet.Path & "\" & _ "Sheet2.xls" With olMail ..Recipients.Add " ..Recipients.Add " ..Recipients.Add " ..Subject = "Weekly Recap" ..Body = "Here is this weeks recap" ..Attachments.Add ActiveSheet.FullName ..Display End With ActiveSheet.Close False Kill Sheet.Path & "\" & "Sheet2.xls" Set olMail = Nothing Set olApp = Nothing End Sub I am really new to Macro writing so I am sure I am doing this all wrong. Am I at least in the ballpark? Another problem I may face is that the active sheet that I want to send gets data from other sheets so I need to find a way to send it witout error messages in the fields that data from other sheets goes. Is there hope? Thanks in advance for any help --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
Why not go to Ron de Bruin's web site ? Ron has lots of examples and code
which will help you do this without re-inventing the wheel. http://www.rondebruin.nl/sendmail.htm Regards Trevor "Kingtriotn " wrote in message ... I am trying to send an active sheet to a set of different email address's and I keep geting error messages. Here is the code I am trying to use Sub mail() Dim olApp As Outlook.Application Dim olMail As MailItem Set olApp = New Outlook.Application Set olMail = olApp.CreateItem(olMailItem) ActiveSheet.Copy ActiveSheet.SaveAs ActiveSheet.Path & "\" & _ "Sheet2.xls" With olMail Recipients.Add " Recipients.Add " Recipients.Add " Subject = "Weekly Recap" Body = "Here is this weeks recap" Attachments.Add ActiveSheet.FullName Display End With ActiveSheet.Close False Kill Sheet.Path & "\" & "Sheet2.xls" Set olMail = Nothing Set olApp = Nothing End Sub I am really new to Macro writing so I am sure I am doing this all wrong. Am I at least in the ballpark? Another problem I may face is that the active sheet that I want to send gets data from other sheets so I need to find a way to send it witout error messages in the fields that data from other sheets goes. Is there hope? Thanks in advance for any help --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
Thanks, I tried the mail one sheet example and it works fine from home.
I then emailed the workbook that I am developing to work and when tried to run it, I get an error that higlights the body text when debug. The error message says somthing to the tune of bad sendmai type. Any ideas? All of my other macros worked fine on the emaile version. Thanks in advance, Kingtrito -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
which example ?
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Kingtriotn " wrote in message ... Thanks, I tried the mail one sheet example and it works fine from home. I then emailed the workbook that I am developing to work and when I tried to run it, I get an error that higlights the body text when I debug. The error message says somthing to the tune of bad sendmail type. Any ideas? All of my other macros worked fine on the emailed version. Thanks in advance, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
this is the example that I used.
Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb ..SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" ..SendMail ", _ "This is the Subject line" ..ChangeFileAccess xlReadOnly Kill .FullName ..Close False End With Application.ScreenUpdating = True End Sub The part where the subject goes is what is getting highlighted in debuger. Thanks --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sending active sheet to email list
Look at the example on my website again you see that there is a dot before
SendMail and other lines to refer to WB. Post back if you got problems Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Kingtriotn " wrote in message ... this is the example that I used. Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" SendMail ", _ "This is the Subject line" ChangeFileAccess xlReadOnly Kill .FullName Close False End With Application.ScreenUpdating = True End Sub The part where the subject goes is what is getting highlighted in debuger. Thanks --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
maintaining text rotation sending sheet to email recipient | Excel Worksheet Functions | |||
sending workbook/sheet to recipient list | Excel Discussion (Misc queries) | |||
Sending a Spreadsheet as an Email Attachment vs. Imbedded in Email | Excel Discussion (Misc queries) | |||
Sending Difft Sheet as a seperate file thru email | Excel Worksheet Functions | |||
Sheet vs File size when sending email in Outlook | Excel Discussion (Misc queries) |