Stuck trying to send email from Excel 2007

[SerenityNetworks]

I've referenced Ron DeBruin's articles. I've tried CDO and it seems SMTP is blocked. I've tried sending via Outlook 2007, but can't get past the Outlook dialog asking if I want to allow Excel to send the mail. I've tried using SendKeys at various points. I've examined the gmail example found on this forum here, but something has changed since the posting and the login (changed to my gmail account) does not work.

I would love to send email via CDO or some method that does not require another application as an intermediary. If that is not possible, my second choice would be to use our company's Exchange WebMail (https://mail.ourdomain.com/exchange). My last choices would be to use gmail or Outlook, but I'll take what I can get working.

I have been successful in using the script (below) to go to a website, record the status of it being available, then logging into the web site, and recording if the login was successful. What I need to do now is send an email if the site is not available or if the login is unsuccessful. I was thinking I could modify the working login script (below) to log into our company's Exchange Webmail, but I can't get past the "Connect to mail.ourdomain.com" dialog box that pops up.

I admit I'm not a coder, so I may be missing something simple. But I have spend a considerable amount of time on this and I'm nowhere. Any help would be greatly appreciated.

Thanks in advance,
Andrew

Sub myavailability()
    Dim IE As InternetExplorer
    Dim ieDoc As Object
    Dim itm As Variant
    Dim varFound As Variant
    Dim LastRow As Variant
    Dim keyHome As Variant
    Dim keyMaint As Variant
    Dim keyLogged As Variant
    Dim keyUser As Variant
    Dim keyPswd As Variant
        
'VARIABLES
    Sheets("Variable").Select
    varFound = ""
    keyHome = Range("B2").Value
    keyMaint = Range("B3").Value
    keyUser = Range("B4").Value
    keyPswd = Range("B5").Value
    keyLogged = Range("B6").Value
    Set IE = New InternetExplorer
    IE.Visible = True
    IE.Navigate "http://www.site-to-check.com"  'NOTE TO SELF: USE THE VARIABLE

    'LastRow Variable
    Sheets("STATUS").Select  'NOTE TO SELF: USE THE VARIABLE
    Range("A1").Select
    LastRow = Range("A1048576").End(xlUp).Row 'defines the last populated row

'PAUSES MACRO FROM ACTION UNTIL WEB PAGE IS LOADED
     'Loop until ie page is fully loaded
    Do Until IE.ReadyState = READYSTATE_COMPLETE
    Loop

'TESTS TO SEE IF HOME PAGE OR MAINTENANCE PAGE IS AVAILABLE
    Set ieDoc = IE.Document.all
        For Each itm In IE.Document.all
            If InStr(itm.innerText, "Look for This") > 0 Then  'NOTE TO SELF: USE THE VARIABLE
                'MsgBox "Home Page Available"
                varFound = "Available"
                Exit For
            ElseIf InStr(itm.innerText, keyMaint) Then
                varFound = "Maintenance"
                Exit For
            Else
                varFound = "Unavailable"
            End If
        Next itm
        
        'The following populates the results on the check of the home page.
        If varFound = "Available" Then
            'MsgBox "No match found."
            Range("A" & LastRow + 1) = Now()
            Range("B" & LastRow + 1) = "Home"
            Range("C" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = varFound
            Range("D1").Select
        ElseIf varFound = "Maintenance" Then
            Range("A" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = Now()
            Range("B" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = "Home"
            Range("C" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = varFound
            Range("D1").Select
        Else
            Range("A" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = Now()
            Range("B" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = "Home"
            Range("C" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = varFound
            Range("D1").Select
            varFound = "No"

            Exit Sub
        End If
        
        varFound = ""

   Set ieDoc = IE.Document
   With ieDoc.forms(0)
        .Username.Value = "myusername"   'NOTE TO SELF: USE THE VARIABLE
        .Password.Value = "mypassword"    'NOTE TO SELF: USE THE VARIABLE
        'following two lines allow the javascript function to be passed and third line submits the form
        IE.Document.all.Item
        Call IE.Document.parentWindow.execScript("doSubmit()", "JavaScript")
        .submit
   End With
    
    Do While IE.Busy: DoEvents: Loop
    Do Until IE.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    
    LastRow = Range("A1048576").End(xlUp).Row 're-defines the last populated row
    
'TESTS TO SEE IF LOGGED IN HOME PAGE IS AVAILABLE
    Set ieDoc = IE.Document.all
        For Each itm In IE.Document.all
            If InStr(itm.innerText, "15003553") > 0 Then
                varFound = "Available"
                Exit For
            Else
                varFound = "Unavailable"
            End If
        Next itm
                
'MsgBox "varFound = " & varFound
                
        If varFound = "Available" Then
            Range("A" & LastRow + 1) = Now()
            Range("B" & LastRow + 1) = "Logged In Home"
            Range("C" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = varFound
            Range("D1").Select
            'NEED TO PUT IN CODE TO LOG OUT
            'MsgBox ("In the last if.")
        Else
            Range("A" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = Now()
            Range("B" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = "Logged In Home"
            Range("C" & LastRow + 1).Select
            ActiveCell.FormulaR1C1 = varFound
            Range("D1").Select
            
            Exit Sub
        End If

    IE.Quit
    Set IE = Nothing
    Set ieDoc = Nothing
    Set itm = Nothing
    Application.StatusBar = ""
    ActiveWorkbook.Save
End Sub

 

[SerenityNetworks]

I re-examined my implementation of Ron's Outlook examples and found a typo'. Whoops! It now works past the Outlook security alert as expected. So I have a solution.

That said, I would still prefer using our Exchange WebMail. If anyone has any thoughts on this then I'd still be interested.

Thanks,
Andrew