Excel Macro to Copy Word Text into an Email
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(xlCellTypeCons tants)
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
|