Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
Code to send a email via Macro in excel | Excel Discussion (Misc queries) | |||
Code skipping when screenupdating is turned off | Excel Programming | |||
Macro skipping | Excel Programming | |||
Skipping sections of code | Excel Programming |