How to click a button and send an email

use an HTML mailto link to do it for you....

http://www.labnol.org/internet/email/learn-mailto-syntax/6748/

Mailto automatically uses the default email program on the client, and you can pass recipient, subject and body variables to it.

How do you pass recipient, subject and body variables to it

1.Open a form, a report, or a data access page in Design view.
2.On the Insert menu, click Hyperlink.
3.In the Insert Hyperlink dialog box, click E-mail address under Link to.
4.In the Text to display box, type the text that you want displayed in the field or text box. If you leave the box blank, Access will use the e-mail address as the display text.
5.Click ScreenTip, and type the text that you want to appear when the user rests the pointer on the hyperlink. If you leave the ScreenTip text box blank, Access will display the e-mail address as the ScreenTip.
6.In the E-mail address box, type an e-mail address. You can also select an address from the Recently used e-mail addresses list.
7.In the Subject box, type the information that you want to appear in the Subject field of the e-mail message.

 Note   Some Web browsers and e-mail programs might not recognize the subject line.
8.Click OK.

On a data access page, Access adds the hyperlink in a hyperlink control. In a form or report, it adds the hyperlink in a label. To test the link on a page, switch to Page view and click the hyperlink. To test the link in a form or report, right-click the label, point to Hyperlink on the shortcut menu, and then click Open. Access opens your mail program and creates a new message, with the address and subject filled in.

source: http://office.microsoft.com/en-ca/access-help/create-a-hyperlink-HP005188821.aspx

That gets the email open with a subject, but it doesnt fill in the body of the email.

Use VBA like this

Private Sub Command93_Click()
On Error GoTo Command93_Click_Err

   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities"

Command93_Click_Exit:
   Exit Sub

Command93_Click_Err:
   MsgBox Error$
   Resume Command93_Click_Exit

End Sub

Select allOpen in new window

this example is for the "actions and opps"

OK great.  That is what I am looking for.  There is one other thing.   How do I make it so that I can put into the code what the body text will be in the email.  And then choose a different PDF attachment that is not a report.   I would like these to automatically fill in along with the subject etc.

This is great.  Thank you.

I got the message body in the email.  I just can't figure out how to format that message body so that it looks like an email and then how to attach a PDF file i have stored in the database to that email.

You can't send an HTML email, or include an attachment, using SendObject. You'll have to automate Outlook (assuming you're using Outlook, of course).

Patrick Matthews has a great article on doing just that:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

I use vbMAPI (www.everythingaccess.com) for all my Outlook email stuff. It's easy to use, integrates completely in the database, and the support is very good. I have no interest in the product except for being a satisfied user.

Scott, on that article is there a slight tweak I can make that will send the email via lotus notes.  Or just go to the default mail system.

It gives this code for going to outlook, could there be one for Lotus notes

Set SendItems = New clsOutlookCreateItem

I have never worked with Lotus Notes, so can't really help with that.

You can try setting a reference to Lotus Notes (I believe it's the "Lotus Notes Automation Classes" reference) and see what you get from there. Some code culled from other internet postings seems to indicate you do something like this:

Dim Session As Object
Set Session = CreateObject("Lotus.NotesSession")

There are many other Objects available, like a "MailDatabase" and a "MailDocument". Not sure how they're used and I don't have Lotus Notes so cannot help. I'd suggest you ask the Moderators to add the Lotus zone to this question.

I found a database online that does almost what I want.   See the new database I attached.

On one form you type in your subject,  message,  and it retrieves who it will send to.  You click a button to open up the default email program in which is put the subject, message and email addresses of the people you are sending to.  

I need something similar to this but with two changes.  

The first thing is I will have a bunch of different forms.  One form per topic.  Each form will have the subject and message filled in.  On a different form I will have a button that references the specific form for that topic.  

The second difference is to send the email to the person on whose record I am on rather than pull from a list on a different table.  

Once the button is clicked it will open the email program.  

How do I do that.   I think it should be just a few code changes.
EmailDBbyPK.mdb

this is your first sample, that i did it for the "actions and opps".

Did you still need something more ?
2nd.accdb

Scott or someone else.  Would you have any idea on my second part of my question.  I found a round a bout way to do what I want.  I just don't know the code to do it.  I assume it's taking the existing code and modifying it.

Gozreh,

It did work and it was great.  The issue is that it is limited in the number of characters in an email,  and it cannot separate out the paragraphs.  It just looks like it is just one big paragraph.   And it cannot add a Dear So and so.

So the other database I posted I thought would be a good solution, I just dont know the code.

Any ideas.

i dont see in the EmailDBbyPK where you have an option to to add paragraphs

i added this code

   Dim StrBody As String
   StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf & "Here you will find the attached report......"
   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody

Select allOpen in new window

3rd.accdb

I dont see it in the attached database.  Is that code attached to a button in the sample? and are there any character limits for the email.

Believe it or not.  I just found it and it worked thanks.  That resolves the issue with paragraphs.  Now one more issue is with character limitations.   Lets say I want an sample email with two long paragraphs.  Can I do that?

it should not be any limit,

i dont know if you want to write the body-text in VBA, or if you want you can create a table or field for body-template's, and then retrieve it from there to your email.

That is a good idea to put it the body into a table or another form and retrieve it.  SO then the question is if I put the body into a separate table what would the code be to retrieve it?

You have been a lot of help

You can add a table Called tblBodyTemplate with Fields TemplateID,TemplateName,BodyText
you can also change the BodyText to a Memo field and format it to "Rich Text".

then before the sendObject open a form with combobox to select the templateID to insert the BodyText.

   Dim StrBody As String
   StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf '&  "Here you will find the attached report......"
   
   DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
   StrBody = StrBody & DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)
   DoCmd.Close acForm, "frmSelectTemplate"
   
   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody

Select allOpen in new window

4th.accdb

I added the table and some sample data.  but it is not pulling the information.  I attached the code to a button on the sales script page.
5th.accdb

This is great.   It works just like I need it to.  Is there a way to attach a PDF file that I imported into the database somewhere.  See the attached database in the table tblBodyTemplate.  I added a column for attachments.  Is there a way automatically add that to the email too.

Whoops here is the database.
7th.accdb

Dang,  there is one more thing and I think that is it beyond the previous item I asked about.  How do you merge data from a field in another part of the database into the text of the email that is located in the tblbodytemplate table?

its not so simple, you will have to create a function to loop the text to find if you entered a {field name}.

my bast option would be to write the body text in VBA .

I guess that means not using the table then?

yes

depends what you need, if you want to use a static body email for each report, then you should write it in VBA.

I would need the ability to merge from different fields and put that into an email?  VBA or table?

I think if I can get the merging thing down that would be huge.

you should do it like i did with with - "Dear " & Me.Connam_Name

Gotcha.  Ill give it a try.  Too bad.  Because having one button for all my emails would have been nice.

I cant get the code right for the merging.  so if I am pulling a field called eff_date into the email what would it be.  This is what I tried.

Private Sub Command233_Click()
On Error GoTo Command93_Click_Err

   Dim StrBody As String
   StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf & "Here you will find the attached report......" & Me.Eff_Date "Here you will find the attached report......"
   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody

Command93_Click_Exit:
   Exit Sub

Command93_Click_Err:
   MsgBox Error$
   Resume Command93_Click_Exit
End Sub

Select allOpen in new window

I just got it.   I missed the second & sign.

Private Sub Command233_Click()
On Error GoTo Command93_Click_Err

   Dim StrBody As String
   StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf & "Here you will find the attached report......" & Me.Eff_Date "Here you will find the attached report......"
   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody

Command93_Click_Exit:
   Exit Sub

Command93_Click_Err:
   MsgBox Error$
   Resume Command93_Click_Exit
End Sub

Select allOpen in new window

SO I keep coming up against little barriers.  Maybe you can address these.

1)  I am trying to VB option and I am finding that it limits the amount of text for the email per one line.   Whenever I carry the text over to the next line down it gives me an error. Is that right?

2)  I loved the first option where I can click a button and run all the emails from that one option.  Going back to the VB option it will require me to have 5-10 different buttons.   Is there anyway you can think of that will give me the ability to merge fields into text using the single button method you came up with?  I really like that way.

what do mean "run all the emails from that one option" ?
i see on the reporting tab that you have there 9 reports,
so do you want to run this all from one button, or you want to send all reports together in one email, or combine all reports in one single pdf  file ?

2) do you want to have an option to select from list of body-text templates to choose every time you send an email ?

3) do you want to attach more pdf files (not from your database objects) to the email you send ?

each option will need a different method, and so should be a different question.

So please describe exactly what you need, and will try to help you to get your solution.

(i think that your first question how to send a report as an email to email-address from the database info included the name in the body should be solved.)

You have been most helpful.  And how to send a report from the database is indeed answered.  Thank you.  

1)  This part does not deal with reports.  In contacting customers we will have a series of 5-10 emails.  Each with different text.  Each one may have some fields from a table that need to be merged into main text of an email.  Then there will be PDF files in a table as you see in the sample I posted.  The PDF file is attached to the table you had me create.  Sometimes there will be a PDF and sometimes there will not be a PDF.   This answers #3 too.

2)  Yes.  I would like to have a list of body text templates to choose from each time I send an email. Like the drop down option you helped me with.

Do you have any ideas on my previous two points.  I think we almost got this part done.  Which will be huge.

He is very helpful.