Excel E-Mal VBA Code - Send one page and a message

consultwithmoses

New Member
Joined
Jan 25, 2005
Messages
2
Hello, I am working on a project that requires me to make a VBA that will e-mail one sheet. I have read through the message boards and cannot find one that will open the e-mail so that the user can add their own text.

This code (found on this site) works, but does not open the e-mail message so the user can add their own message.

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 _
& ".xls"
.SendMail "consultwithmoses@aol.com", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

(It also will not delete the temp file)

---------------


This code (found on the internet) works, but does not copy the information from one worksheet.

Sub Mail_ActiveSheetAGAIN()

Dim myOutlook As Object
Dim myMailItem As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

With otlNewMail
.To = "consultwithmoses@aol.com"
.body = "This is a test" & Chr(13) & "Regards," & Chr(13) & "Consultwithmoses" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display

End With

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing

End Sub


Thank you for your time,
(help...)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to the Board!

I copied your second example, and the only thing I altered was the email address. Worked perfectly for me, but the active workbook has to be saved first. You didn't specify whether yours was or not, so you may want to try that. (XL 2000, Windows 2000)

Hope that helps!
 

consultwithmoses

New Member
Joined
Jan 25, 2005
Messages
2
You are correct, the second script works fine, but it inserts the entire workbook. I would like to have the VBA send only sheet 1.

For example, my workbook contains 12 worksheets and I only would like VBA to send the worksheet named "table".

Thanks for looking at my problem.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Have a look at this link then. I don't know if using an add-in is feasible for you, but it's something to try.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,756
Messages
5,626,678
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top