Need help with Emailing Code Please

F

fre

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 = "(e-mail address removed)"
.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
----------------------------------------------------
 
D

David McRitchie

I would guess that simply commenting out or removing the
following line would do the trick:

.Send ' send the worksheet
 
F

fre

When I take out that line, nothing happens. Email does not even open
now.

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

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 = "(e-mail address removed)"
.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
----------------------------------------------------
Click to expand...
Click to expand...
 
D

David McRitchie

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

When I take out that line, nothing happens. Email does not even open
now.

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

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 = "(e-mail address removed)"
.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
----------------------------------------------------
Click to expand...
Click to expand...
Click to expand...
 
F

fre

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!

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

When I take out that line, nothing happens. Email does not even open
now.

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


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 = "(e-mail address removed)"
.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
Click to expand...
Click to expand...
Click to expand...
 
F

fre

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!


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

When I take out that line, nothing happens. Email does not even open
now.

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


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 = "(e-mail address removed)"
.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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print A For (docm), Send Email Attachment And Close For Without Saving 2
Command Button - MsgBox 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Excel VBA coding - Outlook and Shut down issue 6
Need help modifying code 0
VBA - exporting serial letters to single documents 0
Need help with Email Code (PLEASE) 4
Need help with email code (PLEASE) 9

Top