Some help capturing form data in email

[fralippolippi]

So, I'm very new to Access and not all that skilled in VBA. I have, through trolling the internet found some code that does allow me to send an email on the click of a button on a form through Lotus Notes.

However, I cannot get any of the data on the form to show up in the email.

How do I even do something simple like, have the subject line come from a text box on the form or have the body come from a text box (or 2)?

Here is the code I have so far - also, the "attachment" part does not work - but one problem at a time...

----
Public Sub Command15_Click()
'-------------------------------------------------------------------------------
' Date: 02-06-2003
'
' Sends an email via Lotus Notes.
'-------------------------------------------------------------------------------
'Set up the objects required for Automation into lotus notes
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 MailSession As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)




'Start a session to notes
Set MailSession = CreateObject("Notes.NotesSession")
'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
UserName = MailSession.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

'Open the mail database in notes
Set Maildb = MailSession.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 = "my email address"
MailDoc.Subject = "TEST"
'I have tried to use me.text15 and other things to no avail.
MailDoc.Body = "TEST"
MailDoc.SAVEMESSAGEONSEND = SaveIt

'Set up the 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 MailSession = Nothing
Set EmbedObj = Nothing
End Sub
---------------------------

Again, I literally just have this as an event on "On Click" for the button. Nothing too fancy. It sends an email....with whatever text I manually put in to this code....which is not ideal....

 

[pbaldy]

Along the lines of:

MailDoc.Subject = Me.TextboxName

 

[fralippolippi]

I guess I kind of buried that I have tried that. It is in the code, I have the comment there, that I've done that.

What happens is the email is sent, no error, but the subject line is blank. I've even tried "text" & Me.Textboxname & "text"

And the "text" parts show up but the content of the textbox (Me.Textboxname) do not....

 

[pbaldy]

Saw that but figured you might have used quotes, like "Me.Textbox". You're sure the textbox contains something (stupid question, I know, but have to cover all the bases). I've used that method to populate emails many times. Do you know how to set a breakpoint and check it as it runs?

http://www.baldyweb.com/Debugging.htm

 

[fralippolippi]

I'll check that out when I return to the office. I'm in Baltimore, USA.

There is data in the fields, I tried multiple text boxes, even labels.

I don't know....maybe a reboot or a new record would help.

Thanks for the quick replies

 

[pbaldy]

No problem, post back tomorrow if you're still stuck. Perhaps a sample db would help.

 

[fralippolippi]

I've updated the db.

So, I tried again - rebooted my desktop and such - even made a new record. I can get the email...it just shows up with out any subject?!? So, the script doesn't error - it just doesn't grab the text.

I'm sure I'm doing something rather silly - and so I apologize upfront. Any help would be greatly appreciated.

 

[fralippolippi]

This seems to be working:

MailDoc.Subject = "TEXT" & " " & [Forms]![PRSubmits]![JustificationTextBox]

 

[fralippolippi]

So, the weird part is as long as I have some sort of manually entered text prior to the Form data - it works like a charm.

The good news is that "" works just fine.

So, in the end this is what will work:

MailDoc.Subject = "" & [Forms]![PRSubmits]![JustificationTextBox]

Maybe somebody will have the same issue one day - maybe it will work differently for them - but this seems to work great for me.

Now...how do I get that attachment working?!?

 

[pbaldy]

That is odd, because I've used just the form reference many times. In any case, glad you got it working. I've never used Lotus Notes so not sure how to fix the attachment part. I'd recommend starting a new thread on that topic.