Excel Macro - Htm file to Body of Email Error

  • Thread starter Thread starter TysonE
  • Start date Start date
T

TysonE

I posted this over on programming but I didn't get a response to it
over there, so sorry for the semi double post:

Here is the code I'm working with:

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email As String
Dim cc As String
Dim subject As String
Dim body As String
Dim attach As String
Dim I As Long
Dim ie As Object

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In
Range("b2:b100").Cells.SpecialCells(xlCellTypeConstants)

email = cell.Value
subject = cell.Offset(0, 2).Value
body = cell.Offset(0, 3).Value
cc = cell.Offset(0, 1).Value
attach = cell.Offset(0, 4).Value

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email
.cc = cc
.subject = subject
.body = Get_Body
.Attachments.Add "C:\temp\hpapp.dat"
.Attachments.Add "C:\temp\hpapp2.dat"
.display
End With
Next
End Sub


Function Get_Body() As String
Dim ie As Object


Set ie = CreateObject("InternetExplorer.Application")


With ie
.Visible = True
.navigate "C:\temp\2.htm"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.body.InnerHTML
.Quit
End With


Set ie = Nothing
End Function


One problem, I have my default email set to HTML but here is how the
body comes in at:

<DIV class=Section1>
<P class=MsoNormal><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY:
Arial">Good afternoon,<?xml:namespace prefix = o ns = "urn:schemas-
microsoft-com:office:office" /><o:p></o:p></SPAN></P>

Any idea on what is causing this? I just want the actual format (red
and bold) to appear - not the literal code.

Thanks,

Tyson
 
I posted this over on programming but I didn't get a response to it
over there, so sorry for the semi double post:

Here is the code I'm working with:

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email As String
Dim cc As String
Dim subject As String
Dim body As String
Dim attach As String
Dim I As Long
Dim ie As Object

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In
Range("b2:b100").Cells.SpecialCells(xlCellTypeConstants)

email = cell.Value
subject = cell.Offset(0, 2).Value
body = cell.Offset(0, 3).Value
cc = cell.Offset(0, 1).Value
attach = cell.Offset(0, 4).Value

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email
.cc = cc
.subject = subject
.body = Get_Body
.Attachments.Add "C:\temp\hpapp.dat"
.Attachments.Add "C:\temp\hpapp2.dat"
.display
End With
Next
End Sub

Function Get_Body() As String
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "C:\temp\2.htm"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.body.InnerHTML
.Quit
End With

Set ie = Nothing
End Function

One problem, I have my default email set to HTML but here is how the
body comes in at:

<DIV class=Section1>
<P class=MsoNormal><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY:
Arial">Good afternoon,<?xml:namespace prefix = o ns = "urn:schemas-
microsoft-com:office:office" /><o:p></o:p></SPAN></P>

Any idea on what is causing this? I just want the actual format (red
and bold) to appear - not the literal code.

Thanks,

Tyson
Click to expand...

Solved my own problem - the answer for those of you that care:

.htmlbody = Get_Body

That was it - just needed to ass "html" infront of body

Tyson
 
Back
Top