Constant Expression Required Error Message

Topic

New Reply

Hello,

Below is code that works nicely (I didn’t include the whole macro):Const stPath As String = “K:GroupsADP2012Schedules”
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:GroupsADP” [/COLOR]& Sheet4.Range(“G2”) & ”Schedules”

[/COLOR]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

Reply | Quote

Replies

Remove the word Const from the beginning of the line.

Reply | Quote

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:GroupsADPPhantom StockReports2012Reportsemail 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

Reply | Quote

Add this before:

Dim stPath As String

Then, the line should be like this:

stPath = “K:GroupsADPPhantom StockReports2012Reportsemail statements” & Sheet4.Range(“C2”)

Reply | Quote