PDA

View Full Version : Solved: Display Send To Recipient



Marcster
02-02-2006, 02:24 AM
Hello people,

In Excel 2000 when you click File > Send To > Mail Recipient,
you can send the active worksheet as HTML in the body of an e-mail without opening Outlook.
How do I via VBA display the e-mail part in Excel when you do the above?.
Also to put an e-mail address into the To field?.
And to put text into the Subject field?.

I don't want to send the message automatically though
as this macro would run after a large macro has run and
the user can check the sheet before sending.
Is this possible?.

Using the macro recorder it displays nothing.

Thanks,


Marcster.

Bob Phillips
02-02-2006, 02:31 AM
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add("bob.phillips@somewhere.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
.Attachments.Add ("filename")
.Display 'use .Send when all testing done
End With


The file is takedn from the HDD, not memory, so you will need to save it beforehand.

Marcster
02-02-2006, 02:49 AM
Hi xld,

I don't want it as an attachment.
I want the worksheet to be displayed in the body of the e-mail.
Is this possible?,

Thanks,

Marcster.

Bob Phillips
02-02-2006, 03:04 AM
Option Explicit

Sub SendData()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add("bob.phillips@somewhere.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(ActiveSheet.Cells)
.Display 'use .Send when all testing done
End With
End Sub

Public Function RangetoHTML(rng As Range)
' You can't use this function in Excel 97
Dim oFSO As Object
Dim oTS As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = oTS.ReadAll
oTS.Close
Set oTS = Nothing
Set oFSO = Nothing
Kill TempFile
End Function

Marcster
02-02-2006, 03:05 AM
Thanks xld, i'll try it.

Marcster
02-02-2006, 03:19 AM
How do I set the Subject line to the contents of cell B2?.

Thanks,

Marcster.

Marcster
02-02-2006, 03:33 AM
Now I remember:

Range("B2").Value

Thanks,

Marcster.