Link to home
Start Free TrialLog in
Avatar of lehi52
lehi52

asked on

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

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
Avatar of Gozreh
Gozreh
Flag of United States of America image

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
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
Avatar of lehi52
lehi52

ASKER

Im not seeing how to get the data from one record to merge into the table.
did you test to send email from the Introduction template ? it will give you the date and phone from current record.
Avatar of lehi52

ASKER

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?
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

Avatar of lehi52

ASKER

Where do I add a function?  I understand the other code part.
on same page.

did you download my sample, it should work there.
3.accdb
Avatar of lehi52

ASKER

I downloaded the sample and it shows the {Eff Date} in brackets still not filled in.  Do I need to do anything else?
Avatar of lehi52

ASKER

SHould I download the sample and add the function to it. Or is the function already in their?
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
Avatar of lehi52

ASKER

2007
I test it now with 2007, and it worked.
did it work by you ?
CRM-Version-2.1-Sample.png
<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.
Avatar of lehi52

ASKER

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
Avatar of lehi52

ASKER

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.
what do you mean
it pulls a different text
Which text are you getting ?
Avatar of lehi52

ASKER

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
test the Email button next to "Action and Opportunities" - on tab "Reporting"

dos it work there ?
Avatar of lehi52

ASKER

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.
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 ?
Avatar of lehi52

ASKER

It is close.  I just need to get the code onto the button on the General Tab.
ASKER CERTIFIED SOLUTION
Avatar of Gozreh
Gozreh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lehi52

ASKER

No doubt that Gorzeh is one of the best on here.  He has helped me a lot.