Send to Mail Recipient using current sheet as message body

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Hi all.

As the title - I'm trying to create a macro which will allow me to send the current work sheet to a mail recipient using the current sheet as the message body.

By searching in this forum and using some links I have managed to get to a point of sending an e-mail to a specific user with a specific subject but with the file as an attachment which is no good.

Could anyone help me complete this task?

Thanks

Graham
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Yes but when I copied the code and run it I get a Compile error on the first line indicating "User-defined type not defined".

I'm sure it is probably my lack of knowledge of VBA causing the problem.
 
Upvote 0
OK

The extracted info from the above link is this:

Sub Mail_ActiveSheet_Body()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = SheetToHTML(ActiveSheet)
.Send 'or use .Display
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Can anyone advise why I get the error message of "User-defined type not defined".
 
Upvote 0
grd

Did you see this bit in the link?
You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number
 
Upvote 0
Thanks

Yes I did but I ignored it as my original attempt opened Outlook and I assumed it was working!!!

Howver, having followed the instructions and ticked the box it now hangs on the line

.HTMLBody = SheetToHTML(ActiveSheet)

with SheetToHTML highlighted and an error message of

Compile Error - Sub or Function not defined.

Any further help gratefully accepted.

Graham
 
Upvote 0
You need to add the code below your sub.


Public Function SheetToHTML(sh As Worksheet)

Dim TempFile As String
Dim Nwb As Workbook
Dim myshape As Shape
Dim fso As Object
Dim ts As Object
sh.Copy
Set Nwb = ActiveWorkbook
For Each myshape In Nwb.Sheets(1).Shapes
myshape.Delete
Next
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
Nwb.SaveAs TempFile, xlHtml
Nwb.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function
 
Upvote 0
Still had a problem but in searching around I found this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;816644

Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B5").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "My subject"
.Item.Send
End With
End Sub

which, with a bit of modification, gives me exactly what I want with relative ease.

It would now be nice to be able to bypass the security message which comes up and I think I found something to do that previously - so back to searching.

Thanks for your input.

Graham
 
Upvote 0
Doh!!

Whilst this works fine with Excel 2003 and Outlook 2003 I find I now have a problem with Excel XP (2002) and Outlook 2003.

The lines
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope

both reply with errors.

For what it's worth, I have discovered that you are not able to send an Outlook 2003 e-mail from using Excel XP.

Does anyone know a VBA way around this?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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