Send Emails via Access Form with Form data

[dougwilson1]

Hi all,

I've found some sample code of what I am [largely] trying to do. First I'll explain what I am after and then I'll paste the code I have found at the base of this post.

I have a qry called QryInvestigate which compiles data from several tables and returns the records I am interested in. From this qry i have created a form (called Investigations) which displays the records in a nicer way for me to view.

I want a command button to be displayed on the form, that when pressed creates an email (I use Outlook) based on the data displayed on that form record. I do not want it to automatically send the email (which the below code does), i want to check each mail before i physically click send. I just want the cmd to prepare the email for me via the info on the record.

For example, in my form I have the following fields:
Account
Owner
Email
Amount
Currency
Reference

When i click the email button i want the macro to do the following;

Open a new mail message
To: [email address supplied in database]
Subject: Urgent! Please look

Msg body:

" Hi [Owner],

You have the following issue on your account to address urgently:

Account: [Account]
Amount: [Amount]
Currency: [Currency]
Reference: [Reference]

Please log into your relevant system to amend.

Thank you. "

Can somebody help me out? The code i have found is below:

'******begin code******
Dim email, ref, origin, destination, notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form. this sets the string variable to your fields
email = Me!email
ref = Me!ref
origin = Me!origin
destination = Me!destination
notes = Me!notes

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = email
.Subject = ref & " " & origin & " " & destination
.Body = notes
.Send
End With

'**closes outlook
Set objEmail = Nothing
objOutlook.Quit

Exit Sub
'****end code****

[jonh]

I don't have Outlook so don't know. But I guess it's pretty obvious you need to remove

.send

and

Set objEmail = Nothing
objOutlook.Quit

And if it isn't visible already you might also need to add something like

objOutlook.visible = true