+ Reply to Thread
Results 1 to 8 of 8

Compile Error Argument not optional

  1. #1
    Registered User
    Join Date
    12-02-2017
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    27

    Compile Error Argument not optional

    Hello I am running a Macro to execute a predetermined email with a file attachment when the the excel file opens. I have this automated to open with the task scheduler. I'm a novice at best with this and have run into a issue after adding the Private Sub Workbook_Open() from my understanding this will allow the macro to run when the file opens? When I try and run this through testing I keep getting the error

    Argument not Optional.... Its pointing to the line Private Sub Workbook_Open()

    I have in "ThisWorkbook" in the project explorer

    Private Sub Workbook_Open()

    SendMail

    End Sub
    In the Module I have:

    Sub SendMail()

    Dim outApp As Object
    Dim i As Long

    'Check if Outlook is open
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If outApp Is Nothing Then
    MsgBox "Outlook is not open. Open Outlook and try again.", vbExclamation
    Exit Sub
    End If

    'Clear the ticks from column A
    Columns("A:A").ClearContents

    'The row in which the list of e-mails start
    i = 6

    While Cells(i, 2).Value <> ""
    updateMail Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5), _
    Cells(i, 6), Cells(i, 7), Cells(i, 8), Cells(i, 9), i
    i = i + 1
    Wend

    Set outApp = Nothing

    End Sub

    Sub updateMail(ToBox As String, CcBox As String, BccBox As String, _
    Subject As String, Message As String, AttachmentList As String, _
    AttachmentSeparator As String, Action As String, row As Long)

    Dim outApp As Object
    Dim outMailItem As Object
    Dim i As Integer
    Dim attachmentArray() As String

    Set outApp = GetObject(, "Outlook.Application")
    Set outMailItem = outApp.CreateItem(0)
    attachmentArray() = Split(AttachmentList, AttachmentSeparator)

    On Error GoTo ErrorFound

    'The To, CC and BCC values can send e-mails to multiple recpients
    'just ensure the e-mail addresses are separated with a semicolon (
    With outMailItem
    .To = ToBox
    .CC = CcBox
    .BCC = BccBox
    .Subject = Subject
    .Body = Message

    For i = LBound(attachmentArray) To UBound(attachmentArray)
    .Attachments.Add Trim(attachmentArray(i))
    Next

    Select Case Action

    Case "Display"
    .Display
    Case "Save"
    .Close False
    Case "Send"
    .Send

    End Select

    End With


    'Display tick or cross
    ErrorFound:
    If Err.Number = 0 Then
    Cells(row, 1) = ""
    Else
    Cells(row, 1) = ""
    End If

    CleanUp:
    Set outMailItem = Nothing
    Set outApp = Nothing

    End Sub


    Please help

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,950

    Re: Compile Error Argument not optional

    If you step through the code line by line using F8, which line does the error show up on?

    BSB

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compile Error Argument not optional

    SendMail is a method of the workbook object and requires arguments. Either change the name of your sub, or use Module1.SendMail.

    Please use CODE tags, not QUOTE tags, when posting code.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    12-02-2017
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    27

    Re: Compile Error Argument not optional

    Sorry about that next time I will use the right tags.

    When I press F8 it hight lights the SendMail in blue and Private Sub Wookbook_open in yellow.

    I changed send mail to Module1.SendMail and now the error is "Variable not defined"

    If it helps the object name in the modules folder is "modEmailTemplate" That is where the macro is

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compile Error Argument not optional

    If it helps the object name in the modules folder is "modEmailTemplate" That is where the macro is
    Then use that.

  6. #6
    Registered User
    Join Date
    12-02-2017
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    27

    Re: Compile Error Argument not optional

    I changed the code to say

    Please Login or Register  to view this content.
    The error I now get is " Expected variable or procedure, not module

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compile Error Argument not optional

    ModuleName.ProcedureName

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-02-2017
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    27

    Re: Compile Error Argument not optional

    That worked thank you!

+ 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. Compile error argument not optional
    By wayneatkins in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-07-2015, 08:08 AM
  2. i am getting Compile error: Argument not optional
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2014, 06:27 AM
  3. Compile error: Argument not optional
    By lulu319 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-23-2013, 02:57 PM
  4. [SOLVED] Compile error: Argument not optional
    By darkhangelsk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2013, 06:12 AM
  5. Compile error: Argument not optional
    By dabrows in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2011, 11:29 AM
  6. Getting compile error - Argument not optional
    By Mukesh Sajjan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2011, 10:46 AM
  7. [SOLVED] Compile Error: Argument not optional
    By Brett Smith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2006, 01:45 PM

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