| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email distribution list
Hello
How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Look here
http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Thanks Ron I had overlooked that when browsing your site. However I am
getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Hi
Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
"Worksheets("Forecast").Copy
You copy only 1 sheet to a new workbook" What do you mean? "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Changed as suggested
MyArr = Workbooks("mysheetname.xls").Sheets("distribution" ).Range("a5:a6") .SendMail MyArr, "Forecast" Still giving compile error invalid or unqulified.... thanks "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
You copy the sheet Forecast into a new workbook with this line
Worksheets("Forecast").Copy This is the Activeworbook now with 1 sheet Sheets("distribution") is not in that workbook -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... "Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook" What do you mean? "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Now also getting an error here (Method "SaveAs of object'_Workbook' failed)
ActiveWorkbook.SaveAs "\\c:\rforecast\lfcst" & Range("B56") & ".xls" "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Try this example
Sub Mail_Sheet() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False Workbooks("mysheetname.xls").Sheets("Forecast").Co py MyArr = Workbooks("mysheetname.xls").Sheets("distribution" ).Range("a5:a6") Set wb = ActiveWorkbook With wb .SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail MyArr, "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Changed as suggested MyArr = Workbooks("mysheetname.xls").Sheets("distribution" ).Range("a5:a6") .SendMail MyArr, "Forecast" Still giving compile error invalid or unqulified.... thanks "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email distribution list
Hi Steve
I think you use Outlook instead of Outlook Express Look here for a example for Outlook http://www.rondebruin.nl/sendmail.htm#Tips See the Outlook section -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this example Sub Mail_Sheet() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False Workbooks("mysheetname.xls").Sheets("Forecast").Co py MyArr = Workbooks("mysheetname.xls").Sheets("distribution" ).Range("a5:a6") Set wb = ActiveWorkbook With wb .SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail MyArr, "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Changed as suggested MyArr = Workbooks("mysheetname.xls").Sheets("distribution" ).Range("a5:a6") .SendMail MyArr, "Forecast" Still giving compile error invalid or unqulified.... thanks "Ron de Bruin" wrote in message ... Hi Worksheets("Forecast").Copy You copy only 1 sheet to a new workbook MyArr = Sheets("distribution").Range("a5:a6") It can't find the sheet in the activeworkbook because the workbook have only a sheet named Forecast Use this MyArr = Workbooks("test.xls").Sheets("distribution").Range ("a5:a6") Change the workbook name -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Thanks Ron I had overlooked that when browsing your site. However I am getting a compile error. Here is my code Sub Export() Dim MyArr As Variant ScreenUpdating = False Worksheets("Forecast").Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".xls" ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls" 'Dim MyArr As Variant MyArr = Sheets("distribution").Range("a5:a6") .SendMail MyArr, "Forecast" ActiveSheet.Copy ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" & Range("B56") & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ScreenUpdating = True End Sub "Ron de Bruin" wrote in message ... Look here http://www.rondebruin.nl/sendmail.htm#Tips -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" <sailor4life61@hotmaildotcom wrote in message ... Hello How can I reference a distribution list Sheet1!A5:A15 using ActiveWorkbook.SendMail Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Email distribution of Access Record | Excel Discussion (Misc queries) | |||
| import email addresses into a outlook distribution list | Excel Discussion (Misc queries) | |||
| How to embed email link in excel document to distribution list | Excel Worksheet Functions | |||
| Email distribution of certain cells, not an entire sheet/workbook | Excel Discussion (Misc queries) | |||
| Excel Distribution list | Excel Discussion (Misc queries) | |||
Linear Mode
