Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with Emailing Code Please
The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with Emailing Code Please
I would guess that simply commenting out or removing the
following line would do the trick: .Send ' send the worksheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with Emailing Code Please
When I take out that line, nothing happens. Email does not even open now. On Sun, 28 May 2006 19:39:23 -0400, "David McRitchie" wrote: I would guess that simply commenting out or removing the following line would do the trick: .Send ' send the worksheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with Emailing Code Please
Try taking out the Display alerts false, and the application quit.
Instead of the send put in a MSGBOX "fix letter" I sent out emails once and I did not send automatically so that I could edit each email before sending. Take a look at Ron de Bruin's pages http://www.rondebruin.nl/ http://www.rondebruin.nl/sendmail.htm Send Mail page --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... When I take out that line, nothing happens. Email does not even open now. On Sun, 28 May 2006 19:39:23 -0400, "David McRitchie" wrote: I would guess that simply commenting out or removing the following line would do the trick: .Send ' send the worksheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
FIregured it out
I was able to figure it out. Just replace .send with .display This will open the email but not send it. Thanks for the quick responce! On Sun, 28 May 2006 20:11:39 -0400, "David McRitchie" wrote: Try taking out the Display alerts false, and the application quit. Instead of the send put in a MSGBOX "fix letter" I sent out emails once and I did not send automatically so that I could edit each email before sending. Take a look at Ron de Bruin's pages http://www.rondebruin.nl/ http://www.rondebruin.nl/sendmail.htm Send Mail page --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... When I take out that line, nothing happens. Email does not even open now. On Sun, 28 May 2006 19:39:23 -0400, "David McRitchie" wrote: I would guess that simply commenting out or removing the following line would do the trick: .Send ' send the worksheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with Emailing Code Please - DONE!
I was able to figure it out. Just replace .send with .display This will open the email but not send it. Thanks for the quick responce! On Sun, 28 May 2006 20:11:39 -0400, "David McRitchie" wrote: Try taking out the Display alerts false, and the application quit. Instead of the send put in a MSGBOX "fix letter" I sent out emails once and I did not send automatically so that I could edit each email before sending. Take a look at Ron de Bruin's pages http://www.rondebruin.nl/ http://www.rondebruin.nl/sendmail.htm Send Mail page --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... When I take out that line, nothing happens. Email does not even open now. On Sun, 28 May 2006 19:39:23 -0400, "David McRitchie" wrote: I would guess that simply commenting out or removing the following line would do the trick: .Send ' send the worksheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ... The folowing code is called to by a button. It creates and sends email with the Current work as an attachment and works just fine. The problem I am having it that I want to modify the code to create the email, but NOT send it. This way they can manually add the persons to the email. Could someone please help me do this? ---------------------------------------------------- Sub Button6_Click() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching Dim StyleName As String ' Email Subject Dim TempName As String ' Cell Contents Application.EnableEvents = False Set OL = CreateObject("Outlook.Application") ' New Outlook application Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem StyleName = "Email sUBJECT" ' Email Subject FileName = "ATTACHMENT.xls" ' Attachment File Name Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\" & FileName ' file attachment location 'On Error Resume Next With EmailItem ' with the created e-mail .Subject = StyleName .Body = Cells(25, 6).Value .To = " .Importance = olImportanceHigh ' Set Importance .Attachments.Add "C:\" & FileName .Send ' send the worksheet End With ActiveWorkbook.Close False ' Close workbook without saving Application.ScreenUpdating = True ' Return to normal Application.EnableEvents = True Application.DisplayAlerts = True Application.Quit End Sub ---------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
close form code | Excel Discussion (Misc queries) |