Sending Emails using Macro/Form

[jadeverell]

Hi,

I am not sure if i am doing this correctly or not, and was wondering if you could help me...

I have a form that contains staff details including email addresses. I want to create a button that sends an email to the email address that is in the current record.
I have created a macro and in the 'To' box i have put the following:

[Forms]![frm_contract_maintenance]![End_User Email]

I have linked the button to the macro, but everytime i click on it, it crashes and i have to reboot Access.

Am i doing something wrong?

Please help

Thanks

James

 

[GaryPanic]

try this
access 2000/outlook 2000
my code on this (Pinched)

Private Sub Command13_Click()

Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = [Forms]![claimsreporter]![text8] this is where you reference to email address is
EmailSend.Subject = "Claims Report for" & " " & [Forms]![claimsreporter]![Text3]
'EmailSend.Message = "h:\email.txt" (THIS FAILED)
EmailSend.Attachments.Add "C:\xxx\xxxx.xls"
EmailSend.Display

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

'Kill "C:\temp\test2.xls"

End Sub

 

[burrcm]

The vagaries of Access just keep on keeping on. Curiously, if you do it with a Macro, it will fail, as you can only enter a specific Email address in the To field, but if you convert the macro to a module and then grab the business line and copy it to the on click event of the button on your form, you will find that you can substitute a reference to the address field on the form and it will work just fine. It will look like this -
DoCmd.SendObject acForm (or whatever), "ItemName", "MicrosoftExcelBiff8(*.xls)", Forms!FormName!EmailAddressField, "", "", "Subject", "MsgTxt", False, ""

Chris B

 

[jadeverell]

I am quite new 'modules', and have been trying with the code you provided.
It appears my syntax is wrong and i am really struggling to work it.

Please could you break it down and explain it to me as i were stupid !

My email address is stored in the form/field - [Forms]![frm_contract_maintenance]![End_User Email]

Thanks

James

 

[GaryPanic]

ensure in your reference libary tat yu have Microsoft DAO3.6 ticked
microsoft visual basics for applications extensibility 5.3
and then see what happens

 

[GaryPanic]

my (pinched code)
in essense say open outlook
2 nd line (not sure)
3rd line create new email


then the to line onwards should be easy to follow
then close

 

[burrcm]

Here is the db I tested with. For the exercise, I am emailing the content of the form you see on open as an excel sheet. Should point you in the right direction.

Chris B

 

[jadeverell]

Its working!!!!

Thank you all ever so much!

James

 

[jadeverell]

Sorry, me again.

I have one final question....

My code now looks like -

Private Sub Command100_Click()
DoCmd.SendObject acSendNoObject, , , [Forms]![frm_Maintenance Contracts Data]![End_User Email], "emailaddress@email.com", "", [Forms]![frm_Maintenance Contracts Data]![Contract Title], "hello", False, ""
End Sub

I have managed to insert the persons' email address into the 'To' field and a contract title into the 'subject' field.

My Question is, if i want to include some free text and a unbound field (frorm the form) into the subject or the email text, how would this be done?

e.g. Email

"Hi,

This is to notify you that contract - ************** has expired. Please call me on 01234 567890"

- where ********** is a field within the form, Can this be done?

Thanks

 

[burrcm]

Sure you can. I have updated the demo db so it sends the content of a text box on the form as the subject text. You can elaborate on this as required by building the text string including other data sources, e.g. "This is to notify you that contract - " & [Another Text box] & " has expired. Please call me on 01234 567890". If this isn't clear, let me know and I will expand it some more.

Chris B