Solved

How to click a button and send an email

Posted on 2014-01-31
41
356 Views
Last Modified: 2014-02-19
I have a database, CRM specifically that I am building.  I have a lot of it done.  But there is one thing I want to add to it. But it is way above me and I am hoping someone can help. I would like to have a series of buttons, each doing a different thing, that when you click the button it opens the default email program and fills in the subject,  the body of the email, and automatically populates the name of the person on whose record you are.  

Seems like this is something complex,  how do you do it?
1st.accdb
0
Comment
Question by:lehi52
  • 26
  • 10
  • 2
  • +1
41 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 39824509
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.
0
 

Author Comment

by:lehi52
ID: 39824561
How do you pass recipient, subject and body variables to it
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 39824580
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
0
 

Author Comment

by:lehi52
ID: 39824621
That gets the email open with a subject, but it doesnt fill in the body of the email.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39824705
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

Open in new window

this example is for the "actions and opps"
0
 

Author Comment

by:lehi52
ID: 39825274
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.
0
 

Author Comment

by:lehi52
ID: 39825423
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.
0
 
LVL 84
ID: 39825594
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:

http://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.
0
 

Author Comment

by:lehi52
ID: 39830832
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.
0
 

Author Comment

by:lehi52
ID: 39830841
It gives this code for going to outlook, could there be one for Lotus notes

Set SendItems = New clsOutlookCreateItem
0
 
LVL 84
ID: 39832094
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.
0
 

Author Comment

by:lehi52
ID: 39832723
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
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39833197
this is your first sample, that i did it for the "actions and opps".

Did you still need something more ?
2nd.accdb
0
 

Author Comment

by:lehi52
ID: 39833206
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.
0
 

Author Comment

by:lehi52
ID: 39833227
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.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39833315
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

Open in new window

3rd.accdb
0
 

Author Comment

by:lehi52
ID: 39833358
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.
0
 

Author Comment

by:lehi52
ID: 39833407
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?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39833486
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.
0
 

Author Comment

by:lehi52
ID: 39833493
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Expert Comment

by:Gozreh
ID: 39833537
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

Open in new window

4th.accdb
0
 

Author Comment

by:lehi52
ID: 39833594
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
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39833683
New Sample
6th.accdb
0
 

Author Comment

by:lehi52
ID: 39833865
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.
0
 

Author Comment

by:lehi52
ID: 39833867
Whoops here is the database.
7th.accdb
0
 

Author Comment

by:lehi52
ID: 39834022
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?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39834049
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 .
0
 

Author Comment

by:lehi52
ID: 39834053
I guess that means not using the table then?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39834064
yes

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

Author Comment

by:lehi52
ID: 39834084
I would need the ability to merge from different fields and put that into an email?  VBA or table?
0
 

Author Comment

by:lehi52
ID: 39834085
I think if I can get the merging thing down that would be huge.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39834095
you should do it like i did with with - "Dear " & Me.Connam_Name
0
 

Author Comment

by:lehi52
ID: 39834099
Gotcha.  Ill give it a try.  Too bad.  Because having one button for all my emails would have been nice.
0
 

Author Comment

by:lehi52
ID: 39834207
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

Open in new window

0
 

Author Comment

by:lehi52
ID: 39834211
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

Open in new window

0
 

Author Comment

by:lehi52
ID: 39834299
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.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39834430
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.)
0
 

Author Comment

by:lehi52
ID: 39834441
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.
0
 

Author Comment

by:lehi52
ID: 39836367
Do you have any ideas on my previous two points.  I think we almost got this part done.  Which will be huge.
0
 

Author Closing Comment

by:lehi52
ID: 39836602
He is very helpful.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
compress/resize an image to fit a control in ms access 3 24
Access Date Query 28 29
Loop within Select Case 3 25
Access MDB/PDF 21 32
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now