VBA automatic email help

isometric

New Member
Joined
May 9, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
Excel Formula:
=IFERROR(INDEX(MATRIX!$D$8:$D$47,MATCH($A5,MATRIX!$A$8:$A$47,0)),"")

My VBA code is
VBA Code:
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
In addition to what suggested by Logit, above...
Try replacing the line For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants) with
VBA Code:
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.
 
Upvote 0
Solution
In addition to what suggested by Logit, above...
Try replacing the line For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants) with
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top