Solved

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

Posted on 2014-02-05
24
334 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
  • 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 119

Expert Comment

by:Rey Obrero
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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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 119

Expert Comment

by:Rey Obrero
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

743 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

12 Experts available now in Live!

Get 1:1 Help Now