Automate Macro When Opening Excel Spreadsheet

I would like to automate this macro that searches the expired date field and emails me a reminder that the permit is expiring in 7 days. The macro works by itself but can't figure out how to have the macro run automatically when the spreadsheet opens. I have tried some of the examples to input this into "ThisWorkbook" between the code:

Private Sub Workbook_Open()

'Place code here

End Sub

This does not work! I also get an error saying the "member already exists in an object module from which this object module derives"

Here is the code that works without automation:


Sub GetExpirations()

Dim uRange
Dim lRange
Dim BCell As Range
Dim EmailString As String
Dim IntervalType As String
Dim FirstDate As Date

Set uRange = Sheet1.Range("I2")
Set lRange = Sheet1.Range("I" & Rows.Count).End(xlUp)
EmailString = Empty
IntervalType = "d"
FirstDate = Date

For Each BCell In Range(uRange, lRange)

If Date = DateAdd(IntervalType, -29, BCell) Then 'Change -29 to days to email in advance

EmailString = EmailString & BCell.Offset(0, -8) & ": " & BCell.Offset(0, -5) & "'s permit is expiring in 7 days. " & vbCrLf

End If

Next BCell


Sub SendMail(iBody As String)

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

'If ActiveWorkbook.Saved = True Then

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = iBody

On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Permits expiring in 7 days"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

'End If
End Sub

I tried this an got a Compile Error: Sub or Function not defined.

Excel Macro.jpg

I am still getting the same Compile Error: Sub or Function not defined.

Excel Macro.jpg

Thanks, I appreciate the help. I got it work! Now just need to automate the spreadsheet to open with Windows Task Scheduler so this VBA script will run and email me the info without physically opening the spreadsheet everyday.

I was able to get this code to work so it would email me the correct information but the code needs to be edited so it doesn't email me every time I open the spreadsheet. I just want it to email me when the parameters are met (TRUE). Can someone help out please! I believe it has to do with the code line: SendMail EmailString.

Excel Macro.jpg