VBA/Macros to send outlook email notification from excel

[KatieB1981]

Hi All,
I have created a spreadsheet in excel to record our staff tickets and qualification along with their expiry date. I have applied conditional formatting (colour code system) so that each cell changes colour when it is within 60 days of expiry. See attached as an example of the conditional formatting.
The question has now been asked whether VBA/Macros can be added to send out email notifications when qualifications are due to expire.
The message can be simple: "(NAME), (QUALIFICATION), is due to expire within 60 days. Please renew accordingly"
Can this be done? I am new to VBA - so any assistance and coding would be appreciated.

 

[severynm]

What you are asking for is very possible and relatively simple. Add this to a new module. Also, follow the instructions at the bottom of the page here to add the needed Outlook references to the VBA editor.

Option Explicit

Public Sub CreateEmail()
    Dim idx As Integer
    Dim count As Integer
    Dim dateRange As Range
    Dim emailRange As Range
    
    Set dateRange = Sheet1.Range("A1:A10") 'Customize with your sheet name and range
    Set emailRange = Sheet1.Range("B1:B10") 'Customize with your sheet name and range
    count = dateRange.count 'Number of cells in the range
    
    'Create email object & populate necessary fields
    Dim OutApp As New Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    For idx = 1 To count 'Loop through all rows in the range
        If dateRange.Rows(idx).Value < Now() - 60 Then 'If the date is older than (today - 60d)
            On Error Resume Next
            With OutMail
                .To = emailRange.Rows(idx).Value
                .Subject = "..." 'customize as necessary
                .Body = "..." 'customize as necessary
                .Display '.Send 'Swap out as needed
            End With
            On Error GoTo 0
        End If

    Next idx
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

You will need to run it manually yourself, but if you like, you can link this to a button to make running the code easier. If you want the VBA to run without any user intervention at all, you can always call the function from the workbook Open, BeforeSave, or BeforeClose, depending on your intent.

 

[upthan]

What you are asking for is very possible and relatively simple. Add this to a new module. Also, follow the instructions at the bottom of the page here to add the needed Outlook references to the VBA editor.

Option Explicit

Public Sub CreateEmail()
    Dim idx As Integer
    Dim count As Integer
    Dim dateRange As Range
    Dim emailRange As Range
   
    Set dateRange = Sheet1.Range("A1:A10") 'Customize with your sheet name and range
    Set emailRange = Sheet1.Range("B1:B10") 'Customize with your sheet name and range
    count = dateRange.count 'Number of cells in the range
   
    'Create email object & populate necessary fields
    Dim OutApp As New Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutMail = OutApp.CreateItem(olMailItem)
   
    For idx = 1 To count 'Loop through all rows in the range
        If dateRange.Rows(idx).Value < Now() - 60 Then 'If the date is older than (today - 60d)
            On Error Resume Next
            With OutMail
                .To = emailRange.Rows(idx).Value
                .Subject = "..." 'customize as necessary
                .Body = "..." 'customize as necessary
                .Display '.Send 'Swap out as needed
            End With
            On Error GoTo 0
        End If

    Next idx
   
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

You will need to run it manually yourself, but if you like, you can link this to a button to make running the code easier. If you want the VBA to run without any user intervention at all, you can always call the function from the workbook Open, BeforeSave, or BeforeClose, depending on your intent.

may i see a youtube video regarding on this

 

[severynm]

may i see a youtube video regarding on this

No. I'm not making a video. Specifically what do you need help with?