Send Expiration Notice from Outlook

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Send Expiration Notice from Outlook

Post by adam »

Hi anyone,

When using the following code I'm getting runtime error 13 type mismatch highlighting the line,

Code: Select all

 If cell.Offset(0, 6).Value = DateAdd("m", 1, Date) Then

Code: Select all

Sub SendReminderEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim senderEmail As String
    
    senderEmail = "your-email@example.com"
    
    ' Set the worksheet where the data is stored
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Set the range of cells containing the  data
    Set rng = ws.Range("A2:H" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Loop through each row in the range
    For Each cell In rng.Rows
        ' Check if the expiry date is 1 month from today
        If cell.Offset(0, 6).Value = DateAdd("m", 1, Date) Then
            ' Create Outlook application object
            Set OutlookApp = CreateObject("Outlook.Application")
            
            ' Create a new email
            Set OutlookMail = OutlookApp.CreateItem(0)
            
            ' Set email properties
            With OutlookMail
                .SentOnBehalfOfName = senderEmail
                .To = cell.Offset(0, 7).Value
                .Subject = "Certificate Expiry Reminder"
                .Body = "Dear " & cell.Offset(0, 3).Value & "," & vbCrLf & vbCrLf & _
                        "This is a reminder that your document (" & cell.Offset(0, 2).Value & ") is expiring on " & cell.Offset(0, 6).Value & ". Please renew." & vbCrLf & vbCrLf & _
                        "Best regards," & vbCrLf & _
                        "Your Name"
                .Display 
            End With
            
            ' Clean up
            Set OutlookMail = Nothing
            Set OutlookApp = Nothing
        End If
    Next cell
End Sub
I have date in column G. the format I write date is 9/12/2023. My computer date is 11/9/2023. I would appreciate if anyone could help me to run this code preventing the above error im getting.

for example I enter expiry date in column G in the format 9/12/2023 which then the code would send an email to the customer informing one month is left to expire.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Send Expiration Notice from Outlook

Post by HansV »

Select column G.
Set the horizontal alignment to General.
Are the dates left-aligned or right-aligned?
If they are left-aligned, they are text values instead of real dates. That might cause the problem.
You should be able to convert them to real dates using Data > Text to Columns.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Send Expiration Notice from Outlook

Post by adam »

My system date is as 11/9/2023. I want to write date in column G as 12/9/2023. But it does'nt work. I keep on getting the error.
Best Regards,
Adam

User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Send Expiration Notice from Outlook

Post by SpeakEasy »

> I write date is 9/12/2023
> write date in column G as 12/9/2023

Well ... which?

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Send Expiration Notice from Outlook

Post by adam »

Even 12/9/2023 is not working.
Best Regards,
Adam

User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Send Expiration Notice from Outlook

Post by SpeakEasy »

TBH, it isn't the date that is the problem. The problem is that cell isn't what you think it is ... it is a row from the region. And thus ius returning (in Value2) an array of values representing all of the cells in the row.. One quick fix is to change

Code: Select all

If cell.Offset(0, 6).Value = DateAdd("m", 1, Date) Then
to

Code: Select all

If cell.Offset(0, 6).Value2(1, 1) = DateAdd("m", 1, Date) Then

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Send Expiration Notice from Outlook

Post by adam »

Thank you for the help. Much appreciated.
Best Regards,
Adam