Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Constant Expression Required Error Message

    Hello,

    Below is code that works nicely (I didn't include the whole macro):Const stPath As String = "K:\Groups\ADP\2012\Schedules"
    Dim stFileName As String

    Dim FileName As String
    FileName = ActiveSheet.Name
    stAttachment = stPath & "\" & FileName & ".pdf"

    When I change the "Const stPath As String" line to:
    Const stPath As String = "K:\Groups\ADP\" & Sheet4.Range("G2") & "\Schedules"

    I keep getting an error message that says "constant expression required"... which makes sense now that I think about it because I'm trying to change it when the cell value changes, which means it's not "constant", but rather a "variable", however I clearly don't know how to change the code to do that.

    Any help on how I could change the code to not be a constant so it can change the path whenever the value in Sheet4.Range("G2") changes would be greatly appreciated.

    Thanks!
    Lana

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Remove the word Const from the beginning of the line.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    LJM (2012-08-17)

  4. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That didn't work... here is all the code, in case I should have showed more than what I originally did...

    Sub SendEmail()

    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object

    Dim stSubject As Variant, stAttachment As String

    Dim vaRecipient As Variant, vaMsg As Variant

    Const EMBED_ATTACHMENT As Long = 1454


    '************************************************* *******

    stPath As String = "K:\Groups\ADP\Phantom Stock\Reports\2012\Reports\email statements\" & Sheet4.Range("C2")
    Dim stFileName As String
    Dim FileName As String
    FileName = ActiveSheet.Name

    stAttachment = stPath & "\" & FileName & ".pdf"



    'Initiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

    'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")


    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)


    vaRecipient = Range("A3")

    'Add values to the created e-mail main properties.

    With noDocument
    .Form = "Memo"
    .SendTo = vaRecipient
    .Subject = Sheet4.Range("A2") & " Phantom Stock Statement"
    .Body = "Attached is your " & Sheet4.Range("A2") & " Phantom Stock Statement."
    .SaveMessageOnSend = True
    End With

    'Send the e-mail.
    With noDocument
    .PostedDate = Now()
    .Send 0, vaRecipient
    End With

    'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing

    'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    End Sub

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Add this before:

    Dim stPath As String

    Then, the line should be like this:

    stPath = "K:\Groups\ADP\Phantom Stock\Reports\2012\Reports\email statements\" & Sheet4.Range("C2")

  6. The Following User Says Thank You to ruirib For This Useful Post:

    LJM (2012-08-17)

Posting Permissions

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