Excel Macro to Copy Word Text into an Email

[TysonE]

Hello,

What I'm trying to do is set up a macro that will copy text from a
word doc into the body of my email (the reason a word doc is so I can
keep the formatting). Here is what I have so far:

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
'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 = body
.Attachments.Add "C:\temp\test.xls"
.Attachments.Add "C:\temp\test2.xls"
.display
End With
Next
End Sub


Right now the body is just being pulled from a cell in my excel file,
but the cell doesn't hold all the formatting I need. If anyone can
help me out here that would be great, or if you even have a better
idea on how to simply add the text that's cool too.

Thanks,

Tyson

(Sorry if this is a double post - I got an error page the last time I
hit Post)

 

[Dave Miller]

Tyson,

You could create your body in word, then save it as a webpage (.html,
or .htm). Then change your default format in Outlook to HTML. To add
the body from the saved file use this function below:

Change your line to:
..Body = Get_Body


Function Get_Body() as string
Dim ie as Object

With ie
.visible=True
.navigate "C:\Your File Name.html"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.Body.InnerHTML
.Quit
End With

Set ie = Nothing
End Function

 

[Dave Miller]

I forgot to set the ie object so use this function instead:


Function Get_Body() as string
Dim ie as Object

Set ie = CreateObject("InternetExplorer.Application")

With ie
.visible=True
.navigate "C:\Your File Name.html"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.Body.InnerHTML
.Quit
End With


Set ie = Nothing
End Function

 

[TysonE]

Thanks Dave, but I think I'm having a brain cramp here - if I read
what you are saying right, this is what the macro should look like:

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

Set ie = CreateObject("InternetExplorer.Application")

'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

Function Get_Body() As String
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "C:\temp\1.htm"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.body.InnerHTML
.Quit
End With
Set ie = Nothing
End Function

.Attachments.Add "C:\temp\test.xls"
.Attachments.Add "C:\temp\test2.xls"
.display
End With
Next
End Sub

But when I run this, I get "Compile error: Expected End Sub" - what am
I doing wrong with this code?

Thanks again,

Tyson

 

[Dave Miller]

Tyson,

You need paste the function beneath the End Sub statement.

 

[TysonE]

Ahh - that makes much more sense... but now I have one more 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-comfficeffice" /><o></o></SPAN></P>

Any idea on what is causing this?

Thanks,

Tyson

 

[TysonE]

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