How do I send an email from Access?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form which has a tick box. If the user ticks the box I want it to
open Outlook and populate the subject with the value in a text control
called docnumtxt. How do I do this?
TIA
Tony Williams
 
Thanks Scott. However I dont want to send an object just open an email with the subject and some text loaded. can I just leave out the object? Also when an email is sent I want to automatically update a control called emaildatetxt with the date the email was sent. Can you help with the code which presumably I would put in the Before Update property of the emaildatetxt control?
many thanks for your help

----- Scott McDaniel wrote: -----

Look into Docmd.SendObject via online help.
 
You need to do a bit of VBA coding.

In the After_Update event of the check box type in code like this...

Sub chkEMail_OnClick()

Dim oA As New Outlook.Application
Dim oM As Outlook.MailItem

Set oM = oA.CreateItem(oMailItem) ' can't remember the actual constant but
it'll drop down for you to choose
oM.Subject = Me.docnumtxt

'then you can send it
oM.Send

' or display it for editing
oM.Display

End Sub

I hope that helps

Algis Kuliukas
 
Tony
This is the complete Email code. You can add any
additional coding at the end. I have seen quite a bit of
code for sending email. I have tried many of them. If
you are just sending an Access report you can use the
SendObject command. If attachments are files of any type
try the code below. The one below( I have it stored as a
function in a new module) has been the most reliable for me
with MS Outlook and Access 2000 and can send up to 5
attachments or none. Also I am able to pull from an open
form email address of the recipient, CC, BCC, and the
subject and text and file name of the attachment(s). Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String,strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for body here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
strAttachment2 = "attachment3"
strAttachment3 = "attachment4"
strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
If Len(strAttachment2) <> 0 Then
olMail.Attachments.Add (strAttachment2)
If Len(strAttachment3) <> 0 Then
olMail.Attachments.Add (strAttachment3)
If Len(strAttachment4) <> 0 Then
olMail.Attachments.Add (strAttachment4)
End If
End if
End if
End if
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function
 
Thanks to Jim and Algis for those extra points I will try out both and come back to you if I may if I get stuck. Would Jim?Algis look at my reply to Scott for any help they could give me on stage 2
TI
Tony
 
If you want to just open an email using a macro
Create a form with all your email parameters
Create macro useing SendObject
The sendto, CC, BCC, Subject, Message text can be blank or
you can pull Information from the form by
=[Forms]![NameOfYourForm]![NameOfControl]
In the Edit message put yes.

The next action is RunQuery. This would be an update query
that updates the table with the email date....

Oh by the way I use Access 2000
Jim
-----Original Message-----
Thanks Scott. However I dont want to send an object just
Click to expand...
open an email with the subject and some text loaded. can I
just leave out the object? Also when an email is sent I
want to automatically update a control called emaildatetxt
with the date the email was sent. Can you help with the
code which presumably I would put in the Before Update
property of the emaildatetxt control?
 
Hi Algis! I've tried your code Here is my version
Private Sub Loadtxt_Click()
Dim oA As Outlook.Application
Dim oM As Outlook.MailItem
Set oM = oA.CreateItem(olMailItem)
oM.Subject = Me.Docnumtxt
oM.Body = "Please upload this document to Online"
'then you can send it
'oM.Send

' or display it for editing
oM.Display

End Sub

However I get an error that says Object variable orWith block variable not set and it points to line
Set oM = oA.CreateItem(olMailItem)
What am I missing ?
I have added the references to Outlook but still can't see where I'm going wrong.
TIA
Tony
----- Algis Kuliukas wrote: -----

You need to do a bit of VBA coding.

In the After_Update event of the check box type in code like this...

Sub chkEMail_OnClick()

Dim oA As New Outlook.Application
Dim oM As Outlook.MailItem

Set oM = oA.CreateItem(oMailItem) ' can't remember the actual constant but
it'll drop down for you to choose
oM.Subject = Me.docnumtxt

'then you can send it
oM.Send

' or display it for editing
oM.Display

End Sub

I hope that helps

Algis Kuliukas
 
This assumes the user has Outlook set up on their machine
Similar methods apply to other mail applications, but the details will
be different. You will need to check your mail programs technical
documentation
 
Back
Top