Solved

Merge fields in database fields, onto one section to send email

Posted on 2014-02-05
24
342 Views
Last Modified: 2014-02-19
I attached a sample database.  You will see the send email button on the contact form.  When I click it it opens a box that allows me to choose which email to send.  It will then automatically fill in the name, subject and text into my default email program.  The one thing I need is the ability to send an email with different fields automatically going into that email.  I would need things like EPLI Total, Connam Name along with a big long text merged into a single email.
1.accdb
0
Comment
Question by:lehi52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 2
24 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39836765
I have a function that you can enter your field names in the table with {} symbols, then its convert it to the data from this form.

i added 2 fields in the Introduction record {Eff Date} and {Connam Phone}.
2.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39836778
try this codes


Private Sub Command231_Click()
On Error GoTo Command231_Click_Err

   Dim StrBody As String
   StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf '&  "Here you will find the attached report......"
   StrBody = StrBody & "Total " & Me.EPLI_Total & vbCrLf & vbCrLf
   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
   
Command231_Click_Exit:
   Exit Sub

Command231_Click_Err:
   MsgBox Error$
   Resume Command231_Click_Exit
End Sub
0
 

Author Comment

by:lehi52
ID: 39836781
Im not seeing how to get the data from one record to merge into the table.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 10

Expert Comment

by:Gozreh
ID: 39836797
did you test to send email from the Introduction template ? it will give you the date and phone from current record.
0
 

Author Comment

by:lehi52
ID: 39836821
I did the test send, but it did not merge into the email.  It just shows  {Eff Date} and {Connam Phone} without it merged.  It is going to Lotus notes. Could that be why?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39836833
did you add my functions ?
Function ConvertBody(BodyText As String) As String
   Dim strFullBody As String, strBody As String
   Dim strPos As Long, endPos As Long
   Dim FieldName As String
   
   strFullBody = BodyText
   strPos = InStr(BodyText, "{")
   Do
      endPos = InStr(strFullBody, "}")
      strBody = Left(strFullBody, strPos - 1)
      FieldName = Mid(strFullBody, strPos + 1, endPos - strPos - 1)
      ConvertBody = ConvertBody & strBody & Me.Controls(FieldName)
      
      strFullBody = Right(strFullBody, Len(strFullBody) - endPos)
      strPos = InStr(strFullBody, "{")
   Loop Until strPos = 0
   ConvertBody = ConvertBody & strFullBody
End Function

Open in new window

and then change the code before sendopbject
   DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
   strBodyText = DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)
   DoCmd.Close acForm, "frmSelectTemplate"
   strBody = strBody & ConvertBody(strBodyText)

Open in new window

0
 

Author Comment

by:lehi52
ID: 39837040
Where do I add a function?  I understand the other code part.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837047
on same page.

did you download my sample, it should work there.
3.accdb
0
 

Author Comment

by:lehi52
ID: 39837067
I downloaded the sample and it shows the {Eff Date} in brackets still not filled in.  Do I need to do anything else?
0
 

Author Comment

by:lehi52
ID: 39837102
SHould I download the sample and add the function to it. Or is the function already in their?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837127
it should be there. you can check for it.
i now added on tab "page232" a test button, you can test it there.

Which version MS Access are you using ?
4.accdb
0
 

Author Comment

by:lehi52
ID: 39837156
2007
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837180
I test it now with 2007, and it worked.
did it work by you ?
CRM-Version-2.1-Sample.png
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39837225
<I would need things like EPLI Total, Connam Name along with a big long text merged into a single email. >

how do you want the merging done? where do you want the  EPLI Total, Connam Name, etc to appear in the body of the mail?

post a sample result.
0
 

Author Comment

by:lehi52
ID: 39837342
Gozreh,  The test seems to work but when I click the button to send to my email it pulls a different text.  Here is the database.
5.accdb
0
 

Author Comment

by:lehi52
ID: 39837351
Rey,   There is a table in my database. See the sample. I want to be able to put the merged data into a place where I need it.  We have not written the emails yet.   But we want need the ability to say we want EPLI total in this place or that place in the body text of the tbl where the email text is.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837352
what do you mean
it pulls a different text
Which text are you getting ?
0
 

Author Comment

by:lehi52
ID: 39837365
The test pulls in text and when I send it to email its different.  Here is the test converter:

I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. TooI want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  

I want to send an email to you. 3/20/2014 Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.  I want to send an email to you. Too 123

Here is the sample email

Here you will find the attached report......3/10/2014 Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum. This is a test.

 d k fdjf dj jdfjdjkfjdkfjkdjfkjdfjkdj fd fdj fdjf djfdjfjdkjfdkj fd fjd jfdjfkdjfkdjf d fdjfjdkjfdkjfkdjkfj dkj fdjkfjdkjfdjkfj dkjf kdj fjdkjfkdjfdj jfdjfdjfkdjfkdj kjd fjdkjfdjfjd jfjdkf jdkjfkd jdkfj

 jdkfj dkjfk dfkjkdjkfdjfjd jdjjfdjfj
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837374
test the Email button next to "Action and Opportunities" - on tab "Reporting"

dos it work there ?
0
 

Author Comment

by:lehi52
ID: 39837390
Weird.  That does work.  Haha.  So now I need to get that code to the General Tab and make it work on the Send email button.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39837406
so its my mistake, in the first place i only added the function to the "Action and Opportunities",
i didn't recognize that you added a button Send Email on the General tab.

so now its working as you needed ?
0
 

Author Comment

by:lehi52
ID: 39837411
It is close.  I just need to get the code onto the button on the General Tab.
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39837415
so here is it
6.accdb
0
 

Author Closing Comment

by:lehi52
ID: 39837432
No doubt that Gorzeh is one of the best on here.  He has helped me a lot.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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