VBA Email Macro Skipping code
 

First, my problem occurs on this part of the code:

nav = Sheets("facts").Range("B5").Value

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate = nav
'.navigate "C:\test attachment.htm"

For some reason when I have ".navigate = nav" in the code it skips
back to the Sub, wont close the IE sheet, and wont attach a file. But
when I use the ".navigate "C:\test attachment.htm"" code, it works
totally great.

Does anyone know what might be causing this and how to fix it?

Thanks,

Tyson



Here is the complete Macro if will help you.


Sub SendEmail()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim body As String
Dim cell As Range
Dim strto As String
Dim subject As String

On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Data Base") _
.Range("C5:C100").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
-2).Value) = "yes" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

subject = Sheets("facts").Range("B8").Value & "-- " &
Sheets("facts").Range("B6").Value
body = Sheets("facts").Range("B15").Value
attach = Sheets("facts").Range("B5").Value

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = strto
.subject = subject
.htmlbody = Get_Body
.Attachments.Add attach
.Display
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function Get_Body() As String
Dim ie As Object
Dim nav As String

nav = Sheets("facts").Range("B5").Value

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate = nav
'.navigate "C:\test attachment.htm"
Do Until .ReadyState = 4
Loop
Get_Body = .Document.body.InnerHTML
.Quit

End With
Set ie = Nothing
End Function

VBA Email Macro Skipping code
 

Solved my own problem

The answer for those who care.

Original problem text:

nav = Sheets("facts").Range("B5").Value


Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate = nav
'.navigate "C:\test attachment.htm"


I just needed to take out the "=" sign on this part:

.navigate nav


Tyson


On May 27, 12:47*pm, wrote:
First, my problem occurs on this part of the code:

* * * * * * nav = Sheets("facts").Range("B5").Value

* * * * * * Set ie = CreateObject("InternetExplorer.Application")
* * * * * * With ie
* * * * * * * * .Visible = True
* * * * * * * * .navigate = nav
* * * * * * * * '.navigate "C:\test attachment.htm"

For some reason when I have ".navigate = nav" in the code it skips
back to the Sub, wont close the IE sheet, and wont attach a file. *But
when I use the ".navigate "C:\test attachment.htm"" code, it works
totally great.

Does anyone know what might be causing this and how to fix it?

Thanks,

Tyson

Here is the complete Macro if will help you.

Sub SendEmail()
' Is working in Office 2000-2007
* * Dim OutApp As Object
* * Dim OutMail As Object
* * Dim body As String
* * Dim cell As Range
* * Dim strto As String
* * Dim subject As String

* * On Error Resume Next
* * For Each cell In ThisWorkbook.Sheets("Data Base") _
* * * * .Range("C5:C100").Cells.SpecialCells(xlCellTypeCon stants)
* * * * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
-2).Value) = "yes" Then
* * * * * * strto = strto & cell.Value & ";"
* * * * End If
* * Next cell
* * On Error GoTo 0
* * If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

* * Set OutApp = CreateObject("Outlook.Application")
* * OutApp.Session.Logon
* * Set OutMail = OutApp.CreateItem(0)

* * subject = Sheets("facts").Range("B8").Value & "-- *" &
Sheets("facts").Range("B6").Value
* * body = Sheets("facts").Range("B15").Value
* * attach = Sheets("facts").Range("B5").Value

* * On Error Resume Next
* * With OutMail
* * * * .To = "
* * * * .CC = ""
* * * * .BCC = strto
* * * * .subject = subject
* * * * .htmlbody = Get_Body
* * * * .Attachments.Add attach
* * * * .Display
* * End With
* * On Error GoTo 0

* * Set OutMail = Nothing
* * Set OutApp = Nothing
End Sub

* * * * * * Function Get_Body() As String
* * * * * * Dim ie As Object
* * * * * * Dim nav As String

* * * * * * nav = Sheets("facts").Range("B5").Value

* * * * * * Set ie = CreateObject("InternetExplorer.Application")
* * * * * * With ie
* * * * * * * * .Visible = True
* * * * * * * * .navigate = nav
* * * * * * * * '.navigate "C:\test attachment.htm"
* * * * * * * * Do Until .ReadyState = 4
* * * * * * * * Loop
* * * * * * * * Get_Body = .Document.body.InnerHTML
* * * * * * * * .Quit

* * * * * * End With
* * * * * * Set ie = Nothing
* * * * * * End Function