VBA Code For Sending Database Fields as outlook message field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi There,

I have created an access database and associated queries and forms to track
change requests that our department submits to a corporate mail box for
review / approval etc.

Instead of typing the fields separately, what I would like to do is once i
have filled in all the fields on the form that are mandatory, i would like to
be able to send this record via e-mail to the corporate mailbox. For
example If the table name is CR_Tracker I would like to send the
CR_Request_Title feild contents as the subject line of the e-mail, and the
Request_Desc and Request_ROI fields as part of the body in plain or rich
text. I noticed that I can sort of do this with the macro sendobject but
only as an attachment so it does not do what I would like. I have done
something similar many many moons ago but can not remember how to do it....
can you help?
 
It's quite simple. Something like this air code should do it, in the
Click event procedure of a button on the form.


Private Sub cmdEmail_Click()
Dim strBody As String
Dim strSubject As String

On Error GoTo ErrHandler:

'Me.Dirty = False 'may need to save the form to ensure that
'the current values are used

'Assemble the text of the message using the Me!ControlName
'syntax to refer to contents of controls on the form.
strBody = "Item ID: " & Me!ItemID & vbCrLf _
& "Item Name: " & Me!ItemName & vbCrLf _
& Me!Description

strSubject = "This is the subject of the message"

'Launch the message in the email client ready to have the address
'added and the text edited if necessary.
'Or you can pass the address as one of the arguments to SendObject
'and have the message be sent without manual intervention.
DoCmd.SendObject acSendNoObject, , "Text (*.txt)", , , _
, strSubject, strBody, True


NormalExit:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Err.Clear
MsgBox "User cancelled the message", vbInformation + vbOKOnly
Case Else
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
Err.HelpContext
End Select
Resume NormalExit

End Sub
 
Hello"MAM"

I have read the response you have received from John Nurick, however i have
a simular request and was wondering if you could assist me with the solution
you applied. I find it hard to follow the solution criteria and would
immensly appreciate if you could explain in easier terms the steps i have to
follow as i am a novice access user. I have already created a command button
and would like to include the info in the comments box as the body, and info
in a text box as the Subject. Please help!!

Kindest regards
 
Back
Top