By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

composing an email from form data

P: n/a
Hi all,

I'll explain my database first.

Users input customer details that are required to complete a company
document. The document merge etc is all working - the database saves
the completed document to a variable directory based on form data.

What I need to code into it now is a button that creates an email.
The email has the same basic text each time but just needs to draw a
few variables from the form.

Ideally - the database would also collect as an attachement the
document the database just produced (given that I could hard code the
directory line & document name based on the variables in the form
data)

I don't know if the attachement part is possible and if not I can live
with this.

However the thing I really need is to be at least able to code
multiple lines for the email.

I have tried using "DoCmd.SendObject acSendNoObject" however I don't
know how to get the body text into a multi line format.

Send object works for a basic string of text and a subject line...

Can anyone help me code this?? or has maybe come up with a solution
already??

Cheers!
Nov 3 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
DeZZar wrote:
Hi all,

I'll explain my database first.

Users input customer details that are required to complete a company
document. The document merge etc is all working - the database saves
the completed document to a variable directory based on form data.

What I need to code into it now is a button that creates an email.
The email has the same basic text each time but just needs to draw a
few variables from the form.

Ideally - the database would also collect as an attachement the
document the database just produced (given that I could hard code the
directory line & document name based on the variables in the form
data)

I don't know if the attachement part is possible and if not I can live
with this.

However the thing I really need is to be at least able to code
multiple lines for the email.

I have tried using "DoCmd.SendObject acSendNoObject" however I don't
know how to get the body text into a multi line format.

Send object works for a basic string of text and a subject line...

Can anyone help me code this?? or has maybe come up with a solution
already??

Cheers!
You can't attach a document that isn't an Access document via an email
with SendObject.

I recommend you start at http://www.granite.ab.ca/access/email.htm for
some help on emails.

Then go to http://groups.google.com/advanced_search?q=&pli=1 and search on
Sending Emails
and for the group
comp.databases.ms-access
for further code examples.
Nov 3 '08 #2

P: n/a
is there no way to simply instruct the code to use multiple lines:

This is my current code that would work fine if there was just some
way to expand it to use multiple lines.

Private Sub Treasury_email_Click()

DoCmd.SendObject acSendNoObject, email, acFormatTXT, "AFS - Asset &
Liability Management", (Forms!frm_Leaselocks!BDM), , _
"RATE ACCEPTED - " & Forms!frm_Leaselocks!CustomerName & " - C/N: " &
Forms!frm_Leaselocks!CustomerNumber, _
"<body text portion of the email - just need multiple lines>"
Nov 4 '08 #3

P: n/a
DeZZar wrote:
is there no way to simply instruct the code to use multiple lines:

This is my current code that would work fine if there was just some
way to expand it to use multiple lines.

Private Sub Treasury_email_Click()

DoCmd.SendObject acSendNoObject, email, acFormatTXT, "AFS - Asset &
Liability Management", (Forms!frm_Leaselocks!BDM), , _
"RATE ACCEPTED - " & Forms!frm_Leaselocks!CustomerName & " - C/N: " &
Forms!frm_Leaselocks!CustomerNumber, _
"<body text portion of the email - just need multiple lines>"

I might do something like
Dim strBody As String
strBody = "Hello Joe" & vbNewLine & "How are you doing today?" & _
vbNewLine & vbNewLine & "Have you voted yet?" & vbNewLine & _
vbNewLine & "Say hi to the wife."

Then at the end use strBody instead of your
"<body text portion of the email - just need multiple lines>"
Nov 4 '08 #4

P: n/a
thanks salad - this worked. But presented a new problem.

Here is the working code:
----------------
Dim strBody As String
strBody = "Please regard this as confirmation of leaselock acceptance
with reference to the following details" & _
vbNewLine & " " & _
vbNewLine & "Customer Name:" + " " + Forms!frm_Leaselocks!
CustomerName & _
vbNewLine & "Customer Number:" + " " + Forms!frm_Leaselocks!
CustomerNumber & _
vbNewLine & " " & _
vbNewLine & "If you have any queries please don't hesitate to call"
----------------

What happens now is that when a refer to a field on the form that is a
number type field, (or anything other than text) it errors with a
message "Runtime error 13' Type mismatch"

When I only poin to a text field - it works......

Any ideas??
Nov 5 '08 #5

P: n/a
sorry...the entire code might help:

---------------------
Private Sub Treasury_email_Click()
Dim strBody As String
strBody = "Please regard this as confirmation of leaselock acceptance
with reference to the following details" & _
vbNewLine & " " & _
vbNewLine & "Customer Name:" + " " + Forms!frm_Leaselocks!
CustomerName & _
vbNewLine & "Customer Number:" + " " + Forms!frm_Leaselocks!
CustomerNumber & _
vbNewLine & " " & _
vbNewLine & "If you have any queries please don't hesitate to call"

DoCmd.SendObject acSendNoObject, email, acFormatTXT, "AFS - Asset &
Liability Management", (Forms!frm_Leaselocks!BDM), , _
"RATE ACCEPTED - " & Forms!frm_Leaselocks!CustomerName & " - C/N: " &
Forms!frm_Leaselocks!CustomerNumber, strBody

--------------------------
Nov 5 '08 #6

P: n/a
DeZZar wrote:
thanks salad - this worked. But presented a new problem.

Here is the working code:
----------------
Dim strBody As String
strBody = "Please regard this as confirmation of leaselock acceptance
with reference to the following details" & _
vbNewLine & " " & _
vbNewLine & "Customer Name:" + " " + Forms!frm_Leaselocks!
CustomerName & _
vbNewLine & "Customer Number:" + " " + Forms!frm_Leaselocks!
CustomerNumber & _
vbNewLine & " " & _
vbNewLine & "If you have any queries please don't hesitate to call"
----------------

What happens now is that when a refer to a field on the form that is a
number type field, (or anything other than text) it errors with a
message "Runtime error 13' Type mismatch"

When I only poin to a text field - it works......

Any ideas??
Usually I use an & instead of a + when concatenating a string.

Nov 5 '08 #7

P: n/a
wonderful!! thats all it was!!

Thanks again Salad, you've been a great help!

*************************
For anyone else thats interested this is the final solution that
worked for me to export an email from form data:

<XXXXXX CODE STARTS HERE XXXXXX>
Private Sub email_banker_Click()

Dim strBody As String
'set up the body text of your email
'combine pre-determined text and data from your form. replace
variables as required
strBody = "<YOUR FIRST LINE>" & Forms!frm_MyForm!MyField & _
'below for a blank line
vbNewLine & " " & _
vbNewLine & "<YOUR NEW LINE>" & _
vbNewLine & "<YOUR NEXT LINE>" & _
'repeat as required
vbNewLine & "Kind regards," & _
vbNewLine & Forms!MyForm!MyDBUser

'setup the SendObject command
DoCmd.SendObject acSendNoObject, email, acFormatTXT, <TO>,
<CC>,<BCC, _
"<SUBJECT LINE - Can combine both text and form fields>", strBody

End Sub

<XXXXXX CODE ENDS HERE XXXXXX>

Thanks to SALAD for all the help!

Cheers
DeZZar
Nov 5 '08 #8

P: n/a
DeZZar wrote:
wonderful!! thats all it was!!

Thanks again Salad, you've been a great help!

*************************
For anyone else thats interested this is the final solution that
worked for me to export an email from form data:

<XXXXXX CODE STARTS HERE XXXXXX>
Private Sub email_banker_Click()

Dim strBody As String
'set up the body text of your email
'combine pre-determined text and data from your form. replace
variables as required
strBody = "<YOUR FIRST LINE>" & Forms!frm_MyForm!MyField & _
'below for a blank line
vbNewLine & " " & _
vbNewLine & "<YOUR NEW LINE>" & _
vbNewLine & "<YOUR NEXT LINE>" & _
'repeat as required
vbNewLine & "Kind regards," & _
vbNewLine & Forms!MyForm!MyDBUser

'setup the SendObject command
DoCmd.SendObject acSendNoObject, email, acFormatTXT, <TO>,
<CC>,<BCC, _
"<SUBJECT LINE - Can combine both text and form fields>", strBody

End Sub

<XXXXXX CODE ENDS HERE XXXXXX>

Thanks to SALAD for all the help!

Cheers
DeZZar
Grin. Thanks for the compliment...but if it wasn't me it'd be somebody
else. This is a good group and most likely the reason the regulars
remain regular posters.

Nov 5 '08 #9

P: n/a
no worries Salad! All your help deserves a mention. Your right -
this is a good group and has been a life saver a few times over
already!!

Heres another one. I've been looking at the code supplied for Lotus
notes mailing. This code sends the email directly as setup without
making it appear in your default mail client. It works well...but for
some reason I can't get the attachement part of it work.

If the file I want to attach to the email was:
X:\Leaselocks\VIC\Document.rtf

what would I make this code look like to make it work?
(This is just the attachement portion of the code)

If Attachment <"" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment,
"Attachement")
MailDoc.CREATERICHTEXTITEM ("Attachment")
End If

Nov 5 '08 #10

P: n/a
DeZZar wrote:
no worries Salad! All your help deserves a mention. Your right -
this is a good group and has been a life saver a few times over
already!!

Heres another one. I've been looking at the code supplied for Lotus
notes mailing. This code sends the email directly as setup without
making it appear in your default mail client. It works well...but for
some reason I can't get the attachement part of it work.

If the file I want to attach to the email was:
X:\Leaselocks\VIC\Document.rtf

what would I make this code look like to make it work?
(This is just the attachement portion of the code)

If Attachment <"" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment,
"Attachement")
MailDoc.CREATERICHTEXTITEM ("Attachment")
End If
I've never worked with Lotus Notes. But I went to Tony Toews site.
http://www.granite.ab.ca/_vti_bin/shtml.dll/search.htm
and I searched on the words
Lotus Notes
Check out what he has to say over there. Hopefully there'll be a
solution in one of the links.

This is a great day, America!

Nov 5 '08 #11

P: n/a
solved the problem in the meantime (trial and error!!)

all you need from the code i posted above is this bit:

Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachemt")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", "X:\Leaselocks\" &
Forms!frm_Leaselocks!GetDirectory)
MailDoc.CREATERICHTEXTITEM ("Attachement")

Done!!
Nov 5 '08 #12

P: n/a
FWIW, I've updated/modified a function from Roger's Access Library
(http://www.rogersaccesslibrary.com/f...sts.asp?TID=52) to
work with Access/Word/Outlook 2007. Interested?

Public Function SendReportInMailBody(ByVal ReportName As String, ByVal
MailAddresses As String, Optional ByVal MailSubject As String, Optional
ByVal MailBody As String)

*** Sent via Developersdex http://www.developersdex.com ***
Nov 6 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.