VBA automatic email help

[isometric]

Hi all,

I'm fairly new to VBA and have been using Ron de Bruin's website to help me make my code. Basically, this code works but only if the emails on column E is a hyperlink, but the thing is, column E has an Index array that gets the email based on a condition and it gets the email as a text but not as a link.

Is there a different code to use for getting emails that are on text format? or do i have to change my formula on the cells in which the emails go to?

The formula is

=IFERROR(INDEX(MATRIX!$D$8:$D$47,MATCH($A5,MATRIX!$A$8:$A$47,0)),"")

My VBA code is

Sub all_sheets()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Select
        Call mail_reminder
    Next
    Application.ScreenUpdating = True
End Sub

Sub mail_reminder()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "I").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "B").Value _
                      & vbNewLine & vbNewLine & _
                       "You have 6 weeks remaining before your " & _
                        Range("B1").Value & " certificate expires." _
                       & vbNewLine _
                       & "Please contact the office for more info." & _
                .display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Thanks to anyone willing to help.

 

[Logit]

I have yet to use VBA to send emails when the address is a hyperlink ... so hopefully the following resource is accurate for your needs.
The primary difference is your macro does not specify the address is a 'hyperlink'. The following resource discusses this difference and
how to layout a successful vba macro ...

How to Send Emails from Excel with the Hyperlink Formula

 

[Anthony47]

In addition to what suggested by Logit, above...
Try replacing the line For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants) with

Dim LastE As Long
LastE = Cells(Rows.Count, "E").End(xlUp).Row
For Each cell In Range("E1").Resize(LastE, 1)

If that doesn't work, can you show how column E looks? A screenshot should be enough.

 

[isometric]

In addition to what suggested by Logit, above...
Try replacing the line For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants) with

Dim LastE As Long
LastE = Cells(Rows.Count, "E").End(xlUp).Row
For Each cell In Range("E1").Resize(LastE, 1)

If that doesn't work, can you show how column E looks? A screenshot should be enough.

This worked perfectly! Thank you very much.

 

[Anthony47]

Thank you for the feedback