Inserting table tields as part of text

[vandido626]

I'm using the code below to send notes email from access. I'm trying to create the body of the email in a mail merge type of way. Can I write the static text in the code and insert reference to table fields?

for example: "Dear Mr. [last name from table]"

    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Next line only works with 5.x and above. Replace password with your password
'    Session.Initialize ("password")
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    'Open the mail database in notes
    Setbject = Subject
    [B]MailDoc.Body = BodyText[/B]
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    'Set up th Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Sue embedded object and attachment and attach it
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.SEND 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Function

 

[pbaldy]

Sure; it would look like:

"Dear Mr. " & FormOrRecordsetReferenceHere & " the static text can continue here"

 

[vandido626]

Sure; it would look like:

"Dear Mr. " & FormOrRecordsetReferenceHere & " the static text can continue here"

yea, but I'm having a problem writing the reference.

lets say the DB is name.mbd
table is tbl_name
field is Last

how would I write the reference?

 

[pbaldy]

Do you have a form open with the information, and you're clicking on a button? Are you trying send something to everybody in the table? Maybe you should start here:

http://www.granite.ab.ca/access/email.htm

 

[vandido626]

Do you have a form open with the information, and you're clicking on a button? Are you trying send something to everybody in the table? Maybe you should start here:

http://www.granite.ab.ca/access/email.htm

Actually what I want to do is query the record I want to send out. I will only be sending out the information for one record and need to include the data from those fields in the body.

The link you mentioned has a reference to a form. would it work for a query? So would "Dear" & qry_name!lastnamefield & "rest of static txt" work when I'm referencing a query?

 

[pbaldy]

No, you'd need to open a recordset:

  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  Set rs = db.OpenRecordset("QueryName, dbOpenDynaset)

  YourVariable = "Dear " & rs!lastnamefield & " rest of static txt"

  set rs = nothing
  set db = nothing

 

[vandido626]

No, you'd need to open a recordset:

  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  Set rs = db.OpenRecordset("QueryName, dbOpenDynaset)

  YourVariable = "Dear " & rs!lastnamefield & " rest of static txt"

  set rs = nothing
  set db = nothing

got it!

thanks!