+ Reply to Thread
Results 1 to 9 of 9

Automate Macro When Opening Excel Spreadsheet

  1. #1
    Registered User
    Join Date
    01-26-2018
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    9

    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 = "Nathanial.Wigington@parks.ca.gov"
    .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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,047

    Re: Automate Macro When Opening Excel Spreadsheet

    Put this in the ThisWorkbook code module.

    Keep your other code that works in a standard code module e.g.; Module1.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    01-26-2018
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    9

    Re: Automate Macro When Opening Excel Spreadsheet

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

    Excel Macro.jpg

  4. #4
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    510

    Re: Automate Macro When Opening Excel Spreadsheet

    try putting your code inside the workbook open sub

    Please Login or Register  to view this content.
    Last edited by k1dr0ck; 01-26-2018 at 03:09 AM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,047

    Re: Automate Macro When Opening Excel Spreadsheet

    Select from the VBA menu; Insert\Module.

    Cut all the code from the Sheet1 code module and paste it in the standard module; Module1.


    The Workbook_Open procedure is in the correct place.

  6. #6
    Registered User
    Join Date
    01-26-2018
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    9

    Re: Automate Macro When Opening Excel Spreadsheet

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

    Excel Macro.jpg

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,047

    Re: Automate Macro When Opening Excel Spreadsheet

    You put it in the Class Module. It must go into a standard code module. They are named by default Module1, Module2 ...etc.

    Select from the VBA menu; Insert\Module to create a Standard code module.

  8. #8
    Registered User
    Join Date
    01-26-2018
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    9

    Re: Automate Macro When Opening Excel Spreadsheet

    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.

  9. #9
    Registered User
    Join Date
    01-26-2018
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    9

    Re: Automate Macro When Opening Excel Spreadsheet

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 12-26-2015, 12:38 PM
  2. Macro to automate monthly spreadsheet pulling dates and info from Sheet1 to Sheet 2
    By macaulaymetals in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-16-2015, 05:55 PM
  3. macro to automate the opening , saving and closing the work in another program
    By emymeeky in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2014, 09:40 AM
  4. Replies: 3
    Last Post: 10-18-2012, 12:29 PM
  5. Need VB Macro to Automate Spreadsheet
    By haleakala17 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2012, 09:06 PM
  6. Run a macro when opening spreadsheet
    By Craig2097 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2009, 11:52 AM
  7. Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1