ExcelBanter - Need help with Emailing Code Please

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Need help with Emailing Code Please (https://www.excelbanter.com/new-users-excel/91023-need-help-emailing-code-please.html)

[email protected]

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
----------------------------------------------------

David McRitchie

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
----------------------------------------------------

[email protected]

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
----------------------------------------------------

David McRitchie

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
----------------------------------------------------

[email protected]

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
----------------------------------------------------

[email protected]

Need help with Emailing Code Please - DONE!

All times are GMT +1. The time now is 09:11 AM.