Using Email with Microsoft Access

[sha7jpm]

I am investigating whether access can email a user with the text of the email taken from fields within the database.

I really want it to be as text within the message rather than as an attachment and be able to specify the recipients from within access.

am dubious as to whether this can be achieved.

sounds quite complicated if it is possible.

any pointers or tips would be great.

cheers

John

 

[charityg]

The answer is Yes, absolutely, and it's not as difficult as you may think.

Say you have a button on your form called cmdSendEmail and some textboxes on your form containing the information you would like to include in the email like txtName, txtSpouse, txtPartyType, txtDate.

(I'm using an example of sending an email inviting someone to a party on a particular date)

In the OnClick event of cmdSendEmail
Dim strTextMessage As String
strTextMessage = "Dear " & Me!txtName & "," & Chr(13)
strTextMessage = strTextMessage & "You are invited to a " & Me!PartyType & " Party on " & Me!txtDate & Chr(13)
strTextMessage = strTextMessage & "Please feel free to bring "
strTextMessage = strTextMessage & IIf(IsNull(Me!txtSpouse), "a guest", Me!txtSpouse) & Chr(13)
strTextMessage = strTextMessage & "We look forward to seeing you!"

DoCmd.SendObject acSendNoObject, , , Me!txtEmailAddress, , , "Party Invitation", strTextMessage, True

The last True parameter allows you to modify the email before it is sent. If you just want the email to send automatically change it to False.

Let me know if you need a physical sample.

 

[sha7jpm]

Apologies!!!

Option Explicit

Sorry, am probably being stupid. have tried the code you gave me, see below! but am getting an error occuring about using an expression which evaluated the wrong data type. any pointers?

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim strTextMessage As String
strTextMessage = "Project Title" & Me!Proj & "," & Chr(13)
strTextMessage = strTextMessage & "Instrument Quantity " & Me!Inst & "Project Description" & Me!Projdescrip & Chr(13)
strTextMessage = strTextMessage & "SNAP Filename"
strTextMessage = strTextMessage & "Email to Statictics"

DoCmd.SendObject acSendNoObject, , , Me!txtemailaddress, , , , strTextMessage, False

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

 

[charityg]

Do you have a field on your form named txtEmailAddress that contains an email address for the recipient?

 

[sha7jpm]

email query (cont)

yes I do,

started off as a text box, with the name as txtemailaddress
and then when I ran the form, the caption changed from the email address I had typed to #NAME

so changed the text box to a label but with no success.

 

[sha7jpm]

Success!

many apologies!

I had not typed in the email address in the correct place!!

thanks for all your help,

am very happy now that is works.

cheers

John

 

[sha7jpm]

the saga continues!

er, me again,

sorry!

my boss has now chucked in a nice spanner into the works.

the fields I am emailing could include 1 or many projects. is there a way of say having, 50 combo boxes, the user selects the project for combo box1, and the instrument for combo box2
and so on for each row up to 50.

then the query runs, displays the data and the email syntax picks off the data for all the fields. i.e. 50 rows of about 4 fields each.

would I need to specify each of the 50 rows within the syntax, bearing in mind that some of the fields will be blank. as not all 50 rows will be used.

blooming nightmare!

any tips as per usual would be gratefully received.
kind regards

john

 

[Len Boorman]

I have been following this discussion with interest. Very helpful and informative.

Could I ask if this procedure works with Notes as the email server.

I have previously understood that generally Access will only interface with Outlook which unfortunately we do not use.

I can forsee the situation when I will be asked for an email interface but with Notes.

All information appreciated.

Len B