Passing A range from Excel to Outlook

[Hacktastic]

Greetings,

I have a macro set up to run a series of emails, and I would like to pass into that macro a specific range from excel and place it into the body of the outlook email.

I looked online and it says i have to pass it as an HTML using a worddocument editor.

I am quite confused.

Some sample code below

Sub putinEmail()


Dim objOutlook As Outlook.Application
Dim objOutlookExp As Object
Dim objDrafts As Object
Dim objEmail As Object
Dim strBody, strTitle, strTo As String
Dim MyData As Object


Dim Doc As Word.Document
Dim wdRn As Word.Range
Dim Xl As Excel.Application
Dim Ws As Excel.Worksheet
Dim xlRn As Excel.Range

Dim newtab As Table


'Set wdRn = Doc.Range

Set Xl = GetObject(, "Excel.Application")
Sheets("pName").Select
Set Ws = Xl.Workbooks("Media Spend TPS vb.xlsm").Worksheets(1)

Set xlRn = Ws.Range("A16", "f100")
xlRn.Copy

I want to pass xlRn into another macro as an html
so that its like

Sendemail1 (xlRn)

any way on how to do this?

 

[SkipVought]

Hi,

Try turning on your macro recorder in Outlook, and have at it.

Post back with your code if you still need help.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Hacktastic]

there is no macro recorder in Outlook 2007

I dont know how to do it

 

[WinblowsME]

Here's one way.

Sub Excel_To_Email()
   Dim html_file As String
   
   html_file = "C:\Documents and Settings\WinblowsME\Local Settings\Temp\Temp.htm"

   ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
                                     html_file, _
                                     ActiveSheet.Name, _
                                     Application.Selection.Address, _
                                     xlHtmlStatic, _
                                     "", _
                                     "").Publish (True)
   Call Put_In_Email(Get_HTML(html_file))
   
   On Error Resume Next
   Kill html_file
End Sub

Private Function Get_HTML(html_file) As String
   Dim line As String, html As String
   
   Open html_file For Input As #1
      Do While Not EOF(1)
         Line Input #1, line
         html = html & line & Chr(13)
      Loop
   Close #1
   
   Get_HTML = html
End Function

' Tools -> References -> Microsoft Outlook xx.0 Library

Private Sub Put_In_Email(html As String)
   Dim ol_app As New Outlook.Application, ol_mail_item As Outlook.MailItem

   Set ol_app = New Outlook.Application
   Set ol_mail_item = ol_app.CreateItem(olMailItem)
   
   ol_mail_item.HTMLBody = html
   ol_mail_item.Display
   
   Set ol_mail_item = Nothing
   Set ol_app = Nothing
End Sub