VBA - Lotus Notes Constant error?

[MagicalHippo]

Hello, I am trying to pass a variable "J" into my Lotus Notes application. J designates an employee name, retried from cell (1,1).text.

For some reason, I cannot add the variable into the body of the Lotus message, it keeps throwing a "constant expression required" error. Fairly new to this and coding it on the go.

Questions are bolded and Highlighted in my code below:

Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "c:\Attachments"
Const stSubject As String = "Weekly report"

[B][U][FONT=arial black]Const vaMsg As Variant = "Hi" & j & ", Below is your updated BSA allocation to projects from now until the end of the year." & vbCrLf & _
"Please communicate with me or your respective pm(s) if this allocation does not align to your understanding." & vbCrLf & _
"Thanks..."[/FONT][/U]
[/B]
Const vaCopyTo As Variant = "[EMAIL="name@mail.com"]name@mail.com[/EMAIL]"
 
  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
  Dim stAttachment As String
 
  'Copy the active sheet to a new temporarily workbook.
  With ws
    .Copy
    stFileName = .Range("A4").Value
  End With
 
  stAttachment = stPath & "" & stFileName & ".xls"
 
  'Save and close the temporarily workbook.
  With ActiveWorkbook
  .Worksheets(1).Protect Password:=Whatever
    .SaveAs stAttachment
    .Close
  End With
[U][FONT=arial black][B] 
  'Create the list of recipients.
  vaRecipients = VBA.Array(j)           '''How do I get all the Employee Names from Column A into this Array? [/B][/FONT][/U]
   
  'Instantiate 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 noAttachment = noDocument.CreateRichTextItem("stAttachment")
  Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
  'Add values to the created e-mail main properties.
  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipients
    .CopyTo = vaCopyTo
    .Subject = stSubject
    .Body = vaMsg
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 
  'Delete the temporarily workbook.
  Kill stAttachment
 
  'Release objects from memory.
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  MsgBox "The e-mail has successfully been created and distributed", vbInformation

 

[Norie]

You can't use a variable when setting the value of a constant.

Why don't you just declare vaMsg as a variable?

As for the array question, what kind of array is needed? Is it definitely an array that's required?

 

[MagicalHippo]

You can't use a variable when setting the value of a constant.

Why don't you just declare vaMsg as a variable?

As for the array question, what kind of array is needed? Is it definitely an array that's required?

Thank you for you reply! It took me about 2 days to get this code working right.
I did as you suggested and converting to a string worked amazing! For learning purposes do you know what the code was set to "Constant"?

Additionally, I found a way to loop through each row in Column A, and fire off emails to each Name, so everything works as it should