Problem taking data from Access Form into automated email.

southwestgooner

southwestgooner

Registered User.
Local time
Today, 08:25
Joined
May 14, 2009
Messages
28
Hi All,
I hope someone out there can help me resolve a 'Event Procedure' problem I have.
I want to send a automated email via Lotus Notes to inform a database user that a record has been updated.
I have got the automated email working to send a email to myself and save it into the sent items folder with no problem.

I now need to add the data from two source controls from the form into the email subject and body and also take the email address from another form control into the send to field of the message.
This is where I seem to have hit a brick wall !!

Can anyone reply with some code I can edit to get the result I want ?

Many thanks in anticipation.

Regards,
Steve.
 
It would have helped if you'd posted your working code, so we knew what method you used. Generally, I'd use a variable:

strBody = "Dear " & Me.FirstName

which concatenates literal text with a value from the form.
 
pbaldy said:
It would have helped if you'd posted your working code, so we knew what method you used. Generally, I'd use a variable:

strBody = "Dear " & Me.FirstName

which concatenates literal text with a value from the form.
Click to expand...

This is the code that works to send the email:-

Private Sub Command131_Click()

' Set up the objects required for Automation into Lotus Notes.

Dim Maildb As Object 'The mail database.
Dim UserName As String 'The current users Lotus Notes name.
Dim MailDoc As Object 'The mail document itself.
Dim Subject As Object 'The message subject text.
Dim Body As Object 'The message body text.
Dim Session As Object 'The Lotus Notes session.

' Start a session to notes.
Set Session = CreateObject("Notes.NotesSession")

' Open the mail database in notes.
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

' Open the mail database in notes.
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
' Already open for mail.
Else
Maildb.OPENMAIL
End If

' Create the mail document.
Set MailDoc = Maildb.CREATEDOCUMENT
Call MailDoc.ReplaceItemValue("Form", "Memo")

' Set the recipient.
Call MailDoc.ReplaceItemValue("SendTo", "<Email Address>")

' Set subject.
Call MailDoc.ReplaceItemValue("Subject", "<Subject Text>")

' Create and set the Body content.
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT("<Body Text>")

' Example to create an attachment (optional)
' Call Body.ADDNEWLINE(2)
' Call Body.EMBEDOBJECT(1454, "", "C:\filename", "Attachment")

' Example to save the message (optional).
MailDoc.SAVEMESSAGEONSEND = True

' Send the document.
Call MailDoc.ReplaceItemValue("PostedDate", Now())

' Gets the mail to appear in the Sent items folder.
Call MailDoc.SEND(False)

' Clean Up.
Set Maildb = Nothing
Set MailDoc = Nothing
Set Body = Nothing
Set Session = Nothing

' Confirmation messages.
MsgBox "Message has been transferred to Notes and sent."
MsgBox "Check Lotus Notes inbox to ensure no delivery failure."
End Sub


Help required to add database form variables into the message "SendTo", "Subject" and "Body" fields.

Hope the code above gives you an idea of my method.

Best Regards,
Steve.
 
I haven't worked with Lotus Notes. Where does "<Body Text>" come from?
 
pbaldy said:
I haven't worked with Lotus Notes. Where does "<Body Text>" come from?
Click to expand...

Call MailDoc.ReplaceItemValue("SendTo", "<Email Address>")

The message SendTo address "<Email Address>" will be taken from one of the database Form Source Controls named "Email"


' Set subject.
Call MailDoc.ReplaceItemValue("Subject", "<Subject Text>")

The "<Subject Text>" will go into the Subject field of the email. This will include some standard text but also will include data taken from one of the database Form Source Controls named "ECNNo"


'Create and set the Body content.
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT("<Body Text>")

The "<Body Text>" will go into the main Body field of the email. This will also include some standard text but also will include data taken from one of the database Form Source Controls named "ECNNo".

Hope this is clearer .

Regards,
Steve.
 
If I want to achieve an email that will look like this:

To: "Address"
(Where "Address" is in the recipients email address taken from the Email form control of the access database Form)

Email Subject: Your ECN No ???? has had it's status updated.
(Where the ???? is taken from the ECNNo form control of the access database Form)

Email Body: Hi User, Your ECN No ???? has had it's status updated. Please view the ECN to check it's latest status.
Regards, ECN Dtabase Admin.
(Where User is taken from the FullName form control and the ???? is taken from the ECNNo form control of the access database Form)

Hopefully that explains what I want to achieve.

Regards,
Steve.
 
This is a shot in the dark, but does this test work to pull in the name?

Call Body.APPENDTEXT("Hi " & Me.FullName & ", Your ECN")
 
pbaldy said:
This is a shot in the dark, but does this test work to pull in the name?

Call Body.APPENDTEXT("Hi " & Me.FullName & ", Your ECN")
Click to expand...

Hi,
Many thanks for that shot in the dark. It worked to send form control data into the message body.
The last problem I have now is setting the email recipient. I want to take the EmailAddress from the form into the email recipient "SendTo" line of code.

This code works to send to a fixed email account:
' Set the recipient.
Call MailDoc.ReplaceItemValue("SendTo", "zakogen@hotmail.com")

I tried the code below:
' Set the recipient.
Call MailDoc.ReplaceItemValue("SendTo", & Me.EmailAddress &)

but it falls over.
Can anyone let me know how I can take the EmailAddress data control text into the email recipient "SendTo" address ??

Many thanks in anticipation of a satisfactory resolution.

Regards,
Steve.
 
No need for the concatenation with one value. Try

Call MailDoc.ReplaceItemValue("SendTo", Me.EmailAddress)
 
You must log in or register to reply here.

Similar threads

N
Bulk Emailer using different body texts
Replies
2
Views
174
Gasman
Gasman
N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
293
nector
N
M
Solved Format Email Body
Replies
4
Views
302
xavier.batlle
X
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
368
oxicottin
oxicottin
N
Solved How to remove this error "the execution this software stopped due to run time" error in MS Access VBA
Replies
11
Views
502
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom