Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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
0
lehi52
Asked:
lehi52
  • 12
  • 10
  • 2
1 Solution
 
GozrehCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
lehi52Author Commented:
Im not seeing how to get the data from one record to merge into the table.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
GozrehCommented:
did you test to send email from the Introduction template ? it will give you the date and phone from current record.
0
 
lehi52Author Commented:
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
 
GozrehCommented:
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
 
lehi52Author Commented:
Where do I add a function?  I understand the other code part.
0
 
GozrehCommented:
on same page.

did you download my sample, it should work there.
3.accdb
0
 
lehi52Author Commented:
I downloaded the sample and it shows the {Eff Date} in brackets still not filled in.  Do I need to do anything else?
0
 
lehi52Author Commented:
SHould I download the sample and add the function to it. Or is the function already in their?
0
 
GozrehCommented:
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
 
lehi52Author Commented:
2007
0
 
GozrehCommented:
I test it now with 2007, and it worked.
did it work by you ?
CRM-Version-2.1-Sample.png
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
lehi52Author Commented:
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
 
lehi52Author Commented:
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
 
GozrehCommented:
what do you mean
it pulls a different text
Which text are you getting ?
0
 
lehi52Author Commented:
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
 
GozrehCommented:
test the Email button next to "Action and Opportunities" - on tab "Reporting"

dos it work there ?
0
 
lehi52Author Commented:
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
 
GozrehCommented:
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
 
lehi52Author Commented:
It is close.  I just need to get the code onto the button on the General Tab.
0
 
GozrehCommented:
so here is it
6.accdb
0
 
lehi52Author Commented:
No doubt that Gorzeh is one of the best on here.  He has helped me a lot.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 12
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now