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

[consultwithmoses]

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

 

[Tazguy37]

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]

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]

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.